SQL server数据库的数据表、字段查询

1.查找数据库中的外键关系（含有字段NAME的表）

--下面结果中从左到右分别是： 外键约束名，子表名，外键列名，父表名

select fk.name fkname , ftable.name ftablename, cn.name fkcol, rtable.name 
ftablename from sysforeignkeys
join sysobjects fk on sysforeignkeys.constid = fk.id
join sysobjects ftable on sysforeignkeys.fkeyid = ftable.id
join sysobjects rtable on sysforeignkeys.rkeyid = rtable.id
join syscolumns cn on sysforeignkeys.fkeyid = cn.id 
and sysforeignkeys.fkey = cn.colid
--where cn.name = 'PART_ID'   //依字段名查找关系表
--where rtable.name = 'BASIC_PART'   //依据表名查找外键表

--查询所有含有字段NAME的表

SELECT name FROM sysobjects WHERE id IN (SELECT id FROM syscolumns WHERE name = '字段名' )

说明：sysobjects，syscolumns 都是系统表，分别存储的是表信息和字段信息。

            SQL1仅查询表中建立了外键联系的结果

            SQL2查询的是和字段名的字符匹配的所有表，包括视图。

2.查询SQLSERVER中的所有数据库(及存储过程)

exec sp_helpdb

查询结果包括数据库名称，所有者，创建时间等

3.查询数据库中所有表

方法一：利用sysobjects系统表 在这个表中，在数据库中创建的每个对象（例如约束、默认值、日志、规则以及存储过程）都有对应一行，我们在该表中筛选出xtype等于U的

所有记录，就为数据库中的表了。 示例语句如下：

select* from sysobjects where xtype='U'

详细查询可参考下列语句：

SELECT Name FROM SysObjects Where XType='U' ORDER BY Name

注意：在SQL SERVER2005中，出现了sys.objects目录视图来代替sysobjects系统表，我们在SQL SERVER2005及以后的版本中，可以使用sysobjects系统表与sys.objects目录视图的任意对象来查询所有表。

说明：不同的对象用xtype来标识。

C = CHECK 约束
D = 默认值或 DEFAULT 约束
F = FOREIGN KEY 约束
L = 日志
FN = 标量函数
IF = 内嵌表函数
P = 存储过程
PK = PRIMARY KEY 约束（类型是 K）
RF = 复制筛选存储过程
S = 系统表
TF = 表函数
TR = 触发器
U = 用户表
UQ = UNIQUE 约束（类型是 K）
V = 视图
X = 扩展存储过程
这些信息可以通过联机丛书->索引->sysobjects->xtype来查看。

方法二：利用sys.tables目录视图 sys.tables目录视图，为每个表对象返回一行. 示例语句如下：

select * from sys.tables

注意：sys.tables目录视图也只有在SQL SERVER2005及以上的版本中才能使用。

方法三：利用存储过程sp_tables sp_tables存储过程,可返回可在当前环境中查询的对象列表。这代表可在FROM子句中出现的任何对象。 我们可以执行如下语句：

exec sp_tables

然后在结果集中筛选出所有TABLE_TYPE等于TABLE的记录就是表信息了。

4.查询一个已知表的主键

--查询数据库中所有表的主键
SELECT TABLE_NAME,COLUMN_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE CONSTRAINT_NAME LIKE 'PK%'

--查询一个已知表的主键：
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE CONSTRAINT_NAME LIKE 'PK%' 
and TABLE_NAME = '表名'

参考：http://bbs.csdn.net/topics/320109008

5.查询某一个表的所有字段名，并且有一个字段的统计数

select  name  from  syscolumns  where  id=object_id( '表名' )

 

select  count (1) 统计数  from  syscolumns  where  id=object_id( '表名' )

http://bbs.csdn.net/topics/340055066

6.查询表结构

        包括表的字段及其类型，字段序号，长度，标识，是否为空，默认值，主键等

--查询表的结构(简化版)
SELECT (case when a.colorder=1 then d.name else '' end) 表名,  
a.colorder 序号,
a.name 字段名,
b.name 类型,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  
(case when a.isnullable=1 then '空'else '非空' end) 空, 
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '自增'else '' end) 标识,  
isnull(e.text,'') 默认值
FROM  syscolumns a 
left join systypes b on a.xtype=b.xusertype  
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id  
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件,WHERE改为AND
order by d.name,a.id,a.colorder


--查询表的结构(完全版)
--注释掉dbo.sysproperties表及相关内容
SELECT (case when a.colorder=1 then d.name else null end) 表名,  
a.colorder 字段序号,a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, 
(case when (SELECT count(*) FROM sysobjects  
WHERE (name in (SELECT name FROM sysindexes  
WHERE (id = a.id) AND (indid in  
(SELECT indid FROM sysindexkeys  
WHERE (id = a.id) AND (colid in  
(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  
AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,  
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,  
isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]
FROM  syscolumns a 
left join systypes b on a.xtype=b.xusertype  
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 
left join syscomments e on a.cdefault=e.id  
left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.class and f.minor_id=0
where b.name is not null
--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件,WHERE改为AND
order by a.id,a.colorder

网上其他写法：

SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
FROM dbo.syscolumns a LEFT OUTER JOIN
dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
dbo.sysobjects d ON a.id = d.id AND d.xtype = 'U' AND
d.status >= 0 LEFT OUTER JOIN
dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, a.colorder

该结果包含了表的字段，类型，字段序号，长度，标识，是否为空，创建时间，修改时间等

7.操作数据库的外键

*注意，若更改了数据库外键关系的名称，若该关系中包含了除字母数字下划线以外的其他字符，则该关系不能被下列语句识别，执行出错。

declare @sql varchar(max),
 @tab_name varchar(128),
 @fk_name varchar(128);
declare c cursor for
select o.name, fk.name from sys.foreign_keys fk
JOIN  sys.all_objects  o  ON (fk.parent_object_id=o.object_id)
--或者将该上述两行SQL语句替换成
--select OBJECT_NAME(parent_object_id), name from sys.objects where type='F'

open c
fetch next from c into @tab_name, @fk_name 
while @@FETCH_STATUS=0
begin 
 set @sql='';
 set @sql='alter table ' + @tab_name + ' drop constraint ' + @fk_name
 print @sql
 exec(@sql)
 
 fetch next from c into @tab_name, @fk_name
end
close c
deallocate c

select OBJECT_NAME(parent_object_id) 'table', * from sys.objects where type='F'

参考网址 http://blog.csdn.net/songguozhi/article/details/5878648

SQL Server 批量 停用/启用 外键约束

http://www.cnblogs.com/xiohao/archive/2013/06/28/3160112.html

8.查询一个数据库中所有表的数据，并按表分组

declare @sql nvarchar(100) 
declare @tableName nvarchar(100)
declare cur cursor for
select name from sysobjects where xtype='U'  --搜索所有表名
open cur 
while @@fetch_status=0
 begin 
   set @sql='select * from  [' + @tableName + '] '      --循环查询表数据
   exec sp_executesql @sql
   fetch next from cur into @tableName
 end 
close cur 
deallocate cur 
set nocount off