查询数据库表和字段sql语句

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

1SELECT2表名;;;=CASEa.colorder;WHEN1THENc.name;ELSE''END,3序;;;;;=a.colorder,4字段名;=a.name,5标识;;;=CASECOLUMNPROPERTY(a.id,a.name,'IsIdentity');WHEN1THEN'√'ELSE''END,6主键;;;=CASE7WHENEXISTS(8SELECT*9FROMsysobjects10WHERExtype='PK'ANDname;IN(11SELECTname12FROMsysindexes13WHEREid=a.id;ANDindid;IN(14SELECTindid15FROMsysindexkeys16WHEREid=a.id;ANDcolid;IN(17SELECTcolid18FROMsyscolumns19WHEREid=a.id;ANDname=a.name20)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;a38LEFTJOINsystypes;;;;;;b;ONa.xtype=b.xusertype39INNERJOINsysobjects;;;;c;ONa.id=c.id;ANDc.xtype='U'ANDc.name<>'dtproperties'40LEFTJOINsyscomments;;;d;ONa.cdefault=d.id41LEFTJOINsysproperties;e;ONa.id=e.id;ANDa.colid=e.smallid42ORDERBYc.name,;a.colorder我修改一下，变个精简版本的：

12select a.name, b.xtype,b.name3from syscolumns a4inner; JOIN systypes; b5ON a.xtype=b.xusertype6inner join sysobjects c ON7a.id=c.id AND c.xtype='U' AND c.name<>'dtproperties' where c.name = 表名

http://ring1981.cnblogs.com/archive/2006/06/22/432857.html