整理资料时看到有这样一个查询数据库中的表和字段信息的语句,很强! 就是忘了当初谁写的了,印像中该是邹建所创。也贴出来以后备用吧。　

1SELECT　

2表名= CASE a.colorder WHEN 1 THEN c.name ELSE '' END,

3序= a.colorder,

4字段名 = a.name,

5标识= CASE COLUMNPROPERTY(a.id,a.name,'IsIdentity') WHEN 1 THEN '√' ELSE '' END,

6主键= CASE

7WHEN EXISTS (

8SELECT *

9FROM sysobjects

10WHERE xtype='PK' AND name IN (

11SELECT name

12FROM sysindexes

13WHERE id=a.id AND indid IN (

14SELECT indid

15FROM sysindexkeys

16WHERE id=a.id AND colid IN (

17SELECT colid

18FROM syscolumns

19WHERE id=a.id AND name=a.name

20)

21)

22)

23)

24THEN '√'

25ELSE ''

26END,

27类型= b.name,

28字节数 = a.length,

29长度= COLUMNPROPERTY(a.id,a.name,'Precision'),

30小数= CASE ISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)

31WHEN 0 THEN ''

32ELSE CAST(COLUMNPROPERTY(a.id,a.name,'Scale') AS VARCHAR)

33END,

34允许空 = CASE a.isnullable WHEN 1 THEN '√' ELSE '' END,

35默认值 = ISNULL(d.[text],''),

36说明= ISNULL(e.[value],'')

37FROM syscolumns a

38LEFTJOIN systypesb ON a.xtype=b.xusertype

39INNER JOIN sysobjectsc ON a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties'

40LEFTJOIN syscommentsd ON a.cdefault=d.id　

41LEFTJOIN sysproperties e ON a.id=e.id AND a.colid=e.smallid

42ORDER BY c.name, a.colorder

修改一下,变个精简版本的:

1　

2select a.name, b.xtype,b.name

3from syscolumns a

4innerJOIN systypesb

5ON a.xtype=b.xusertype

6inner join sysobjects c ON

7a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties' where c.name = 表名