今天整理资料时看到有这样一个查询数据库中的表和字段信息的语句，很强! 就是忘了当初谁写的了，印像中该是邹建所创。也贴出来以后备用吧。
1
SELECT
2表名;;;=CASEa.colorder;WHEN1THENc.name;ELSE''END,
3序;;;;;=a.colorder,
4字段名;=a.name,
5标识;;;=CASECOLUMNPROPERTY(a.id,a.name,'IsIdentity');WHEN1THEN'√'ELSE''END,
6主键;;;=CASE
7WHENEXISTS(
8SELECT*
9FROMsysobjects
10WHERExtype='PK'ANDname;IN(
11SELECTname
12FROMsysindexes
13WHEREid=a.id;ANDindid;IN(
14SELECTindid
15FROMsysindexkeys
16WHEREid=a.id;ANDcolid;IN(
17SELECTcolid
18FROMsyscolumns
19WHEREid=a.id;ANDname=a.name
20)
21)
22)
23)
24THEN'√'
25ELSE''
26END,
27类型;;;=b.name,
28字节数;=a.length,
29长度;;;=COLUMNPROPERTY(a.id,a.name,'Precision'),
30小数;;;=CASEISNULL(COLUMNPROPERTY(a.id,a.name,'Scale'),0)
31WHEN0THEN''
32ELSECAST(COLUMNPROPERTY(a.id,a.name,'Scale');ASVARCHAR)
33END,
34允许空;=CASEa.isnullable;WHEN1THEN'√'ELSE''END,
35默认值;=ISNULL(d.[text],''),
36说明;;;=ISNULL(e.[value],'')
37FROMsyscolumns;a
38LEFTJOINsystypes;;;;;;b;ONa.xtype=b.xusertype
39INNERJOINsysobjects;;;;c;ONa.id=c.id;ANDc.xtype='U'ANDc.name<>'dtproperties'
40LEFTJOINsyscomments;;;d;ONa.cdefault=d.id
41LEFTJOINsysproperties;e;ONa.id=e.id;ANDa.colid=e.smallid
42ORDERBYc.name,;a.colorder
我修改一下，变个精简版本的：
1
2select a.name, b.xtype,b.name
3from syscolumns a
4inner; JOIN systypes; b
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 = 表名
