sql2005获取表字段说明

--sql server2000读取备注信息 
SELECT o.name as tableName,c.name as columnName,p.value as Description FROM sysproperties p join sysobjects o on o.id=p.id 
join syscolumns c on p.id=c.id and p.smallid=c.colid where p.name='MS_Description' and o.name = 'yourtable'order by 1

--sql server2005读取备注信息 
select o.name as tableName,c.name as columnName,t.name as columnType,p.value as columnDescription   from  sysobjects o left join syscolumns c  on o.id=c.id
 left join sys.extended_properties p on p.major_id=c.id and p.minor_id=c.colid and p.name='MS_Description' left join systypes t on c.xusertype=t.xusertype
where o.type='u' 
and o.name='YourTableName'

 

另：关联information_schema.columns用

select table_name,column_name,B.value 
from information_schema.columns A left join sys.extended_properties B
on B.major_id=object_id(A.table_name)
and A.ordinal_position=B.minor_id

--sql server2000读取备注信息

SELECT o.name as tableName,c.name as columnName,p.value as Description FROM sysproperties p join sysobjects o on o.id=p.id

join syscolumns c on p.id=c.id and p.smallid=c.colid where p.name='MS_Description' and o.name = 'yourtable'order by 1

--sql server2005读取备注信息

select o.name as tableName,c.name as columnName,t.name as columnType,p.value as columnDescription from sysobjects o left join syscolumns c on o.id=c.id

left join sys.extended_properties p on p.major_id=c.id and p.minor_id=c.colid and p.name='MS_Description' left join systypes t on c.xusertype=t.xusertype

where o.type='u'

and o.name='YourTableName'

另：关联information_schema.columns用

select table_name,column_name,B.value

from information_schema.columns A left join sys.extended_properties B

on B.major_id=object_id(A.table_name)

and A.ordinal_position=B.minor_id

转载请注明：中国翼通 » sql2005获取表字段说明

分享到：

与本文相关的文章