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,
??? 標(biāo)識?????? = 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,
??? 類型?????? = b.name,
??? 占用字節(jié)數(shù) = a.length,
??? 長度?????? = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
??? 小數(shù)位數(shù)?? = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
??? 允許空???? = case when a.isnullable=1 then '√'else '' end,
??? 默認(rèn)值???? = isnull(e.text,''),
??? 字段說明?? = 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.id=object_id('cay.t_table')??? --如果只查詢指定表,加上此條件
order by
??? a.id,a.colorder

總結(jié)

以上是生活随笔為你收集整理的Sql获取表信息(包括结构及字段说明)的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。