SQL 2005 字段备注获取

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

 1  
 2 create proc [dbo].[GenerateDataDictionaryByTableName]
 3 @tableName  nvarchar(255)
 4 as
 5 begin
 6 --获取数据表名
 7 declare  @tableid int
 8 declare mycursor Cursor
 9 for select object_id from sys.objects where type='U' and  name<>'dtproperties' and name LIKE '%'+ @tableName+'%'
10 --获取字段名称、标识、字段序号、占用字节数、小数位数、允许空等
11 open mycursor
12 fetch next from mycursor into @tableid
13 while(@@fetch_status=0) 
14 begin 
15     SELECT object_name(@tableid) AS 表名,VALUE AS 表说明 FROM sys.extended_properties 
16     WHERE NAME='MS_Description' AND MAJOR_ID=@tableid AND MINOR_ID=0;
17     select 
18         col.colorder 字段序号,
19         col.name 字段名,
20         t.name 类型,
21         col.length 占用字节数,
22         COLUMNPROPERTY(col.id,col.name,'PRECISION') as 长度,
23         isnull(COLUMNPROPERTY(col.id,col.name,'Scale'),0) as 小数位数,
24         (case when 
25             (SELECT count(*)
26                 FROM sysobjects
27                 WHERE (name in
28                     (SELECT name
29                         FROM sysindexes
30                         WHERE (id = col.id) 
31                             AND (indid in
32                                     (SELECT indid
33                                      FROM sysindexkeys
34                                      WHERE (id = col.id) AND (colid in
35                                             ( SELECT colid
36                                               FROM syscolumns
37                                               WHERE (id = col.id)
38                                                     AND (name = col.name)
39                                               ))
40                                      )
41                                   )
42                    )) AND
43                    (xtype = 'PK')
44               )>0 
45          then '√' else '' end) 主键,
46         (case when COLUMNPROPERTY(col.id,col.name,'IsIdentity')=1 then '√'else '' end) 标识,
47         (case when col.isnullable=1 then '√'else '' end) 允许空,
48         isnull(expro.[value],'') AS 字段说明
49     from sys.syscolumns as col
50     left join sys.systypes as t on col.xtype = t.xusertype
51     left join sys.extended_properties as expro on col.id=expro.major_id AND col.colid = expro.minor_id
52     where id = @tableid 
53     fetch next from mycursor into @tableid
54 end
55 close mycursor
56 deallocate mycursor 
57 end

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

exec [GenerateDataDictionaryByTableName] N'T_INTE_'

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

　　

转载于:https://www.cnblogs.com/Silicon-Fado/archive/2009/08/25/1553530.html