从数据库中获得表的字段和描述信息

方法1:从函数里面获得

IF NOT EXISTS
(
SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE',N'T', N'COLUMN',N'ID')
)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'标志ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'T', @level2type=N'COLUMN',@level2name=N'ID'
GO

方法2:
直接从表里面获得
SELECT [table name] = i_s.table_name,
       [column name] = i_s.column_name,
       [description] = s.VALUE ,
       [DATA_TYPE]=i_s.DATA_TYPE
FROM   information_schema.columns i_s
       LEFT OUTER JOIN sys.extended_properties s
         ON s.major_id = Object_id(i_s.table_schema + '.' + i_s.table_name)
            AND s.minor_id = i_s.ordinal_position
            AND s.name = 'ms_description'
WHERE  Objectproperty(Object_id(i_s.table_schema + '.' + i_s.table_name),
       'ismsshipped'
       ) = 0
--and i_s.table_name = 'meter' 
ORDER  BY i_s.table_name,
          i_s.ordinal_position 

转载于:https://www.cnblogs.com/mingyongcheng/archive/2011/06/07/2074102.html