最近在倒腾一个框架是要将数据库所有表及字段导入到数据库表中,然后就自动生成代码,所以研究了一下查询当前库所有表及字段说明语句:
具体SQL语句如下:
--查询数据库所有表
select
o.name '表名',
f.value '说明'
from
sys.sysobjects o
left join sys.extended_properties f on
f.major_id=o.id and f.minor_id=0 and f.class=1
where o.type in ('U')
order by o.name asc
--查询数据库所有表字段
select
o.name '表名',
c.name '列名',
case when c.system_type_id in (165, 167,231) and c.max_length = -1 then t.name + '(Max)' else t.name end '字段类型',
case (c.max_length) when -1 then 0 else case when c.system_type_id in (99, 231, 239) then (c.max_length/2) else(c.max_length) end end as '长度',
case(c.is_nullable) when 1 then 'NULL' else'NOT NULL' end '是否为空',
(select convert(varchar(8000), value) from sys.extended_properties as ex where ex.major_id = c.object_id and ex.minor_id = c.column_id) '说明'
from
sys.columns c
join sys.objects o on(o.object_id = c.object_id)
join sys.types t on (t.user_type_id = c.system_type_id)
where
o.type in ('U')
order by o.name asc
效果图: