sql查询备注和属性的语句
标签：
sql查询备注和属性的语句
‘字段‘ =a. name,
‘类型‘ =b. name,
‘长度‘ = COLUMNPROPERTY (a. id,a .name, ‘PRECISION‘),
‘是否非空‘ = case when a.isnullable =1 then ‘√ ‘else ‘ב end,
‘是否自增‘ = case when COLUMNPROPERTY( a.id ,a. name,‘IsIdentity‘ )=1 then ‘√‘ else ‘ ב end,
‘是否主键‘ = case when exists(SELECT 1 FROM sysobjects where xtype=‘PK‘ and parent_obj =a. id 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,
‘字段说明‘ = isnull (g. [value],‘‘ )
FROM
syscolumns a
left join
systypes b
on
a.xusertype =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
sys.extended_properties g
on
a.id =g. major_id and a. colid=g .minor_id
left join
sys.extended_properties f
on
d.id =f. major_id and f. minor_id=0
where
d.name =‘TABLE‘ --如果只查询指定表,加上此条件
order by
a.id ,a. colorder