贝网博客

我的分类
流水记事
源码下载
Asp.net
其它
数据库
Javascript
.Net技术
我的相册
友情链接
博客园
CSDN博客
Start0
最新回复
fasdfasdf
[:..
lz这个东西好厉害,我..
哈哈,好照片
不错,以前一直用黄色..
终于找到支持ff的修正..
终于找到支持ff的修正..
新鲜性
看看,试试,好不好使。
好东西一起学习[:img0..
分类 =》数据库
Sql Server 2005获取表结构、文件数据相关信息
发表于:2011-12-26 11:44:31
更新于:2011-12-26 14:52:13

1、获取所有表的所有外键及关联表、字段信息:
SELECT tbl.name AS 表名, cstr.name AS 外键名, fk.constraint_column_id AS [ID], cfk.name AS 外键列,
rtbl.[name] AS 关联表, crk.name AS 关联列
FROM sys.tables AS tbl
INNER JOIN sys.foreign_keys AS cstr ON cstr.parent_object_id=tbl.object_id
INNER JOIN sys.foreign_key_columns AS fk ON fk.constraint_object_id=cstr.object_id
INNER JOIN sys.columns AS cfk ON fk.parent_column_id = cfk.column_id and fk.parent_object_id = cfk.object_id
INNER JOIN sys.columns AS crk ON fk.referenced_column_id = crk.column_id and fk.referenced_object_id = crk.object_id
INNER JOIN sys.tables AS rtbl ON rtbl.object_id = cstr.referenced_object_id
ORDER BY tbl.name, cstr.[name]

2、获取所有表的所有主键以及主键对应的字段信息:
SELECT a.name, b.[name],
STUFF((SELECT ',' + CAST(d.name AS NVARCHAR) FROM sysindexkeys c, syscolumns d, sysindexes e
        WHERE d.colid = c.colid AND d.id = c.id AND d.id = b.id and d.id = e.id and e.name = a.name and e.indid = c.indid
        FOR XML PATH('')),1,1,'') cols
FROM sysobjects a
INNER JOIN sysobjects b ON a.parent_obj = b.id
 WHERE a.xtype = 'PK'

3、删除当前数据库所有外键(有时效率低,通过程序维护关系,不需要外键处理):
DECLARE @tbname NVARCHAR(100), @fkname NVARCHAR(100);
DECLARE @sql NVARCHAR(MAX);
DECLARE CUR CURSOR LOCAL FORWARD_ONLY FOR SELECT b.[name] tbname, a.name fkname
FROM sysobjects a
INNER JOIN sysobjects b ON a.parent_obj = b.id
 WHERE a.xtype = 'F'
ORDER BY b.[name];
OPEN CUR
FETCH NEXT FROM CUR INTO @tbname, @fkname
WHILE @@FETCH_STATUS = 0
BEGIN 
 SET @sql = N'ALTER TABLE dbo.['+@tbname+N'] DROP CONSTRAINT ['+@fkname+N']';
    EXEC sp_executesql @sql
    FETCH NEXT FROM CUR INTO @tbname, @fkname
END
CLOSE CUR
DEALLOCATE CUR
 

发表评论
名称(*):
邮箱:
正文:

©2008 Beinet.cn 版权所有