SQL 2005 字段备注获取

　　实际开发中经常为数据字段备注而烦恼，为此写了如下存储过程，一方便查看数据库备注信息.

 
create proc [dbo].[GenerateDataDictionaryByTableName]
@tableName  nvarchar(255)
as
begin
--获取数据表名
declare  @tableid int
declare mycursor Cursor
for select object_id from sys.objects where type='U' and  name<>'dtproperties' and name LIKE '%'+ @tableName+'%'
--获取字段名称、标识、字段序号、占用字节数、小数位数、允许空等
open mycursor
fetch next from mycursor into @tableid
while(@@fetch_status=0) 
begin 
    SELECT object_name(@tableid) AS 表名,VALUE AS 表说明 FROM sys.extended_properties 
    WHERE NAME='MS_Description' AND MAJOR_ID=@tableid AND MINOR_ID=0;
    select 
        col.colorder 字段序号,
        col.name 字段名,
        t.name 类型,
        col.length 占用字节数,
        COLUMNPROPERTY(col.id,col.name,'PRECISION') as 长度,
        isnull(COLUMNPROPERTY(col.id,col.name,'Scale'),0) as 小数位数,
        (case when 
            (SELECT count(*)
                FROM sysobjects
                WHERE (name in
                    (SELECT name
                        FROM sysindexes
                        WHERE (id = col.id) 
                            AND (indid in
                                    (SELECT indid
                                     FROM sysindexkeys
                                     WHERE (id = col.id) AND (colid in
                                            ( SELECT colid
                                              FROM syscolumns
                                              WHERE (id = col.id)
                                                    AND (name = col.name)
                                              ))
                                     )
                                  )
                   )) AND
                   (xtype = 'PK')
              )>0 
         then '√' else '' end) 主键,
        (case when COLUMNPROPERTY(col.id,col.name,'IsIdentity')=1 then '√'else '' end) 标识,
        (case when col.isnullable=1 then '√'else '' end) 允许空,
        isnull(expro.[value],'') AS 字段说明
    from sys.syscolumns as col
    left join sys.systypes as t on col.xtype = t.xusertype
    left join sys.extended_properties as expro on col.id=expro.major_id AND col.colid = expro.minor_id
    where id = @tableid 
    fetch next from mycursor into @tableid
end
close mycursor
deallocate mycursor 
end

　　在需要查看指定表描述时，输入如下语句：

exec [GenerateDataDictionaryByTableName] N'T_INTE_'

该存储过程会搜索当前数据库中表名包含传入的参数字符串，并返回符合条件的所有表的备注信息。如下返回形如：