如何得到數據庫中所有表字段及字段中文描述
以下資料,通過csdn的一位師兄從SQL版主那得到:
sql中
SELECT?
?(case when a.colorder=1 then d.name else '' end) N'表名',
?a.colorder N'字段序號',
?a.name N'字段名',
?(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'標識',
?(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) N'主鍵',
?b.name N'類型',
?a.length N'占用字節數',
?COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'長度',
?isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小數位數',
?(case when a.isnullable=1 then '√'else '' end) N'允許空',
?isnull(e.text,'') N'默認值',
?isnull(g.[value],'') AS N'字段說明'
--into ##tx
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 object_name(a.id),a.colorder
一個字,爽,還可以省去做DD時的好多麻煩.
如果不是很明白,可以將以上代碼放到SQL查詢分析器上運行一下就知道了(當然要打開一個數據庫啦)
)
