SQL Server数据表信息

SQL Server数据表信息2007-06-01 本站收集整理
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,"PRECISION"）,

小数位数=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 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 sysproperties g on a.id=g.id and a.colid=g.smallid

left join sysproperties f on d.id=f.id and f.smallid=0

--where d.name="要查询的表" --如果只查询指定表,加上此条件

order by a.id,a.colorder