SQL2008架构：如何显示列的描述属性

SQL2008架构：如何显示列的描述属性

一、可以添加在企业管理器图形用户界面的描述，或者你可以使用以下代码：

EXEC sp_addextendedproperty 
    'MS_Description', 
    'some description', 
    'user', 
    dbo, 
    'table', 
    table_name, 
    'column', 
    column_name

二、可以用下面的代码查询所有表的值：

SELECT     [Table Name] = i_s.TABLE_NAME,     [Column Name] = i_s.COLUMN_NAME,     [Description] = s.value 

     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

参考：

http://databases.aspfaq.com/schema-tutorials/schema-how-do-i-show-the-parameters-for-a-function-or-stored-procedure.html

1 -- 1. SqlServer2000数据库字典--表结构.sql
2 SELECT TOP 100 PERCENT -- a.id,
3 CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
4 CASE WHEN a.colorder = 1 THEN isnull (f.value, '' ) ELSE '' END AS 表说明,
5 a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY (a.id,
6 a.name, ' IsIdentity ' ) = 1 THEN ' √ ' ELSE '' END AS 标识,
7 CASE WHEN EXISTS
8 ( SELECT 1
9 FROM dbo.sysindexes si INNER JOIN
10 dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
11 dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
12 dbo.sysobjects so ON so.name = si.name AND so.xtype = ' PK '
13 WHERE sc.id = a.id AND sc.colid = a.colid) THEN ' √ ' ELSE '' END AS 主键,
14 b.name AS 类型, a.length AS 长度, COLUMNPROPERTY (a.id, a.name, ' PRECISION ' )
15 AS 精度, ISNULL ( COLUMNPROPERTY (a.id, a.name, ' Scale ' ), 0 ) AS 小数位数,
16 CASE WHEN a.isnullable = 1 THEN ' √ ' ELSE '' END AS 允许空, ISNULL (e. text , '' )
17 AS 默认值, ISNULL (g. [ value ] , '' ) AS 字段说明, d.crdate AS 创建时间,
18 CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
19 FROM dbo.syscolumns a LEFT OUTER JOIN
20 dbo.systypes b ON a.xtype = b.xusertype INNER JOIN
21 dbo.sysobjects d ON a.id = d.id AND d.xtype = ' U ' AND
22 d.status >= 0 LEFT OUTER JOIN
23 dbo.syscomments e ON a.cdefault = e.id LEFT OUTER JOIN
24 dbo.sysproperties g ON a.id = g.id AND a.colid = g.smallid AND
25 g.name = ' MS_Description ' LEFT OUTER JOIN
26 dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
27 f.name = ' MS_Description '
28 ORDER BY d.name, a.colorder
29 -- SqlServer2005\2008数据库字典--表结构.sql create view TableDict as
30 SELECT TOP 100 PERCENT -- a.id,
31 -- CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
32 d.name AS 表名,
33 CASE WHEN a.colorder = 1 THEN isnull (f.value, '' ) ELSE '' END AS 表说明,
34 a.colorder AS 字段序号, a.name AS 字段名, ISNULL (g. [ value ] , '' ) AS 字段说明,
35 CASE WHEN COLUMNPROPERTY (a.id,a.name, ' IsIdentity ' ) = 1 THEN ' √ ' ELSE '' END AS 标识,
36 CASE WHEN EXISTS
37 ( SELECT 1 FROM dbo.sysindexes si
38 INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid
39 INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid
40 INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = ' PK '
41 WHERE sc.id = a.id AND sc.colid = a.colid) THEN ' √ ' ELSE '' END AS 主键,
42 b.name AS 类型, a.length AS 长度, COLUMNPROPERTY (a.id, a.name, ' PRECISION ' ) AS 精度,
43 ISNULL ( COLUMNPROPERTY (a.id, a.name, ' Scale ' ), 0 ) AS 小数位数,
44 CASE WHEN a.isnullable = 1 THEN ' √ ' ELSE '' END AS 允许空, ISNULL (e. text , '' ) AS 默认值,
45 d.crdate AS 创建时间,
46 CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END AS 更改时间
47 FROM dbo.syscolumns a
48 LEFT OUTER JOIN dbo.systypes b ON a.xtype = b.xusertype
49 INNER JOIN dbo.sysobjects d ON a.id = d.id AND d.xtype = ' U ' AND d.status >= 0
50 LEFT OUTER JOIN dbo.syscomments e ON a.cdefault = e.id
51 LEFT OUTER JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id AND g.name = ' MS_Description '
52 LEFT OUTER JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0 AND f.name = ' MS_Description '
53 ORDER BY d.name, 字段序号
54 -- 2. SqlServer数据库字典--索引.sql
55 SELECT TOP 100 PERCENT -- a.id,
56 CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名,
57 CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名,
58 b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, ' isdescending ' )
59 WHEN 1 THEN ' 降序 ' WHEN 0 THEN ' 升序 ' END AS 排序, CASE WHEN p.id IS NULL
60 THEN '' ELSE ' √ ' END AS 主键, CASE INDEXPROPERTY (c.id, a.name, ' IsClustered ' )
61 WHEN 1 THEN ' √ ' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY (c.id,
62 a.name, ' IsUnique ' ) WHEN 1 THEN ' √ ' WHEN 0 THEN '' END AS 唯一,
63 CASE WHEN e.id IS NULL THEN '' ELSE ' √ ' END AS 唯一约束,
64 a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间
65 FROM dbo.sysindexes a INNER JOIN
66 dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN
67 dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN
68 dbo.sysobjects c ON a.id = c.id AND c.xtype = ' U ' LEFT OUTER JOIN
69 dbo.sysobjects e ON e.name = a.name AND e.xtype = ' UQ ' LEFT OUTER JOIN
70 dbo.sysobjects p ON p.name = a.name AND p.xtype = ' PK '
71 WHERE ( OBJECTPROPERTY (a.id, N ' IsUserTable ' ) = 1 ) AND ( OBJECTPROPERTY (a.id,
72 N ' IsMSShipped ' ) = 0 ) AND ( INDEXPROPERTY (a.id, a.name, ' IsAutoStatistics ' ) = 0 )
73 ORDER BY c.name, a.name, b.keyno
74 -- 3. SqlServer数据库字典--表.视图.函数.存储过程.触发器.主键.外键.约束.规则.sql
75 SELECT DISTINCT
76 TOP 100 PERCENT isnull (p.name, '' ) AS 父对象, o.xtype,
77 CASE o.xtype WHEN ' C ' THEN ' CHECK 约束 ' WHEN ' D ' THEN ' 默认值或DEFAULT约束 '
78 WHEN ' F ' THEN ' FOREIGNKEY约束 ' WHEN ' L ' THEN ' 日志 ' WHEN ' FN ' THEN ' 标量函数 '
79 WHEN ' IF ' THEN ' 内嵌表函数 ' WHEN ' P ' THEN ' 存储过程 ' WHEN ' PK ' THEN ' PRIMARYKEY约束 '
80 WHEN ' RF ' THEN ' 复制筛选存储过程 ' WHEN ' S ' THEN ' 系统表 ' WHEN ' TF ' THEN ' 表函数 '
81 WHEN ' TR ' THEN ' 触发器 ' WHEN ' U ' THEN ' 用户表 ' WHEN ' UQ ' THEN ' UNIQUE 约束 '
82 WHEN ' V ' THEN ' 视图 ' WHEN ' X ' THEN ' 扩展存储过程 ' WHEN ' R ' THEN ' 规则 ' ELSE NULL
83 END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间,
84 c. text AS 声明语句, OBJECTPROPERTY (o.id, N ' IsMSShipped ' )
85 FROM dbo.sysobjects o Left JOIN
86 dbo.sysobjects p ON o.parent_obj = p.id LEFT OUTER JOIN
87 dbo.syscomments c ON o.id = c.id
88 WHERE -- (o.xtype IN ('C','D','F','PK','UQ','L','FN','IF','TF','TR','P','R','RF','X','S','U','V')) AND
89 ( OBJECTPROPERTY (o.id, N ' IsMSShipped ' ) = 0 ) AND ( isnull (p.name, '' ) <> N ' dtproperties ' )
90 ORDER BY o.xtype DESC

posted on 2011-03-04 14:05 donqiang 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/donqiang/archive/2011/03/04/1970868.html