[MSSQL] - (命令)列出所有表.字段名.主键.类型.长度.小数位数等信息

注：来源于网络

-- ===========================================================
-- 列出 SQL SERVER 所有表,字段名,主键,类型,长度,小数位数等信息
-- 在查询分析器里运行即可
-- ===========================================================
select
( case
when a.colorder = 1 then d.name
else ''
end ) as ' 表名 ' ,
a.colorder as ' 字段序号 ' ,
a.name as ' 字段名 ' ,
( case
when columnproperty (a.id,a.name, ' IsIdentity ' ) = 1 then ' √ '
else ''
end ) as ' 标识 ' ,
( case
when ( select count ( * ) from sysobjects
where (name in ( select name from sysindexes where id = a.id
and (indid in ( select indid from sysindexkeys where id = a.id
and (colid in ( select colid from syscolumns where id = a.id and name = a.name))))))
and xtype = ' PK ' ) > 0 then ' √ '
else ''
end ) as ' 主键 ' ,
b.name as ' 类型 ' ,
a.length as ' 占用字节数 ' ,
columnproperty (a.id,a.name, ' PRECISION ' ) as ' 长度 ' ,
isnull ( columnproperty (a.id,a.name, ' Scale ' ), 0 ) as ' 小数位数 ' ,
( case
when a.isnullable = 1 then ' √ '
else ''
end ) as ' 允许空 ' ,
isnull (e. text , '' ) as ' 默认值 ' ,
isnull (g.value, '' ) as ' 字段说明 '
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
order by a.id,a.colorder

 

转载于:https://www.cnblogs.com/hcbin/archive/2010/02/24/1669600.html