(轉)mssql2005生成表字典

出處不詳

?

SELECT?TOP?100?PERCENT?--a.id,?
CASE?WHEN?a.colorder?=?1?THEN?d.name?ELSE?''?END?AS?表名,?
CASE?WHEN?a.colorder?=?1?THEN?isnull(f.value,?'')?ELSE?''?END?AS?表說明,?
a.colorder?AS?字段序號,?a.name?AS?字段名,?CASE?WHEN?COLUMNPROPERTY(a.id,?
a.name,?'IsIdentity')?=?1?THEN?'√'?ELSE?''?END?AS?標識,?
CASE?WHEN?EXISTS?
(SELECT?1?
FROM?dbo.sysindexes?si?INNER?JOIN?
dbo.sysindexkeys?sik?ON?si.id?=?sik.id?AND?si.indid?=?sik.indid?INNER?JOIN?
dbo.syscolumns?sc?ON?sc.id?=?sik.id?AND?sc.colid?=?sik.colid?INNER?JOIN?
dbo.sysobjects?so?ON?so.name?=?so.name?AND?so.xtype?=?'PK'?
WHERE?sc.id?=?a.id?AND?sc.colid?=?a.colid)?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?字段說明,?d.crdate?AS?創建時間,?
CASE?WHEN?a.colorder?=?1?THEN?d.refdate?ELSE?NULL?END?AS?更改時間?
FROM?dbo.syscolumns?a?LEFT?OUTER?JOIN?
dbo.systypes?b?ON?a.xtype?=?b.xusertype?INNER?JOIN?
dbo.sysobjects?d?ON?a.id?=?d.id?AND?d.xtype?=?'U'?AND?
d.status?>=?0?LEFT?OUTER?JOIN?
dbo.syscomments?e?ON?a.cdefault?=?e.id?LEFT?OUTER?JOIN?
sys.extended_properties?g?ON?a.id?=?g.major_id?AND?a.colid?=?g.minor_id?LEFT?OUTER?JOIN?
sys.extended_properties?f?ON?d.id?=?f.major_id?AND?f.minor_id?=?0?
ORDER?BY?d.name,?a.colorder

?