[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
个性签名:做要做好,做到不三不四不如不做。