查看SQLServer数据表的详细信息

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