一条语句查询数据库中所有表的信息

SELECT 表名=case when a.colorder=1 then d.name else '' end,

表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,

字段序号=a.colorder,

字段名=a.name,

标识=case when COLUMNPROPERTY( a.id,a.name,''IsIdentity'')=1 then ''√''else '' end,

主键=case when exists(SELECT 1 FROM sysobjects where xtype=''PK'' and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then ''√'' else '' end,

类型=b.name,

占用字节数=a.length,

长度=COLUMNPROPERTY(a.id,a.name,''PRECISI>小数位数=isnull(COLUMNPROPERTY(a.id,a.name,''Scale''),0),

允许空=case when a.isnullable=1 then ''√''else '' end,

默认值=isnull(e.text,''),

字段说明=isnull(g.[value],'')

FROM syscolumns a

left join systypes b >inner join sysobjects d >left join syscomments e >left join sysproperties g >left join sysproperties f >--where d.name=''此处可添加要查询的表名''

Order by a.id,a.colorder

可以在where语句中填写要查询指定表的名称,将显示指定的结构信息和说明。

欢迎您访问www.5ucms.org