SQL Server表和字段的說明

1. 增加字段說明

EXEC sp_addextendedproperty

    'MS_Description',

    'some description',

    'user',

     dbo,

    'table',

     table_name,

    'column',

     column_name

Some Description ， 是要增加的說明内容

table_name, 是表名

column_name , 是字段名

2. 增加表的說明 EXEC sp_addextendedproperty

     table_name 參數說明同上 3. 取得字段說明内容

SQL Server 2000 SQL Server 2005 ( 包括 express)

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

    sysproperties s

ON

    s.id = OBJECT_ID(i_s.TABLE_SCHEMA+'.'+i_s.TABLE_NAME)

    AND s.smallid = 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 = 'table_name'

ORDER BY

    i_s.TABLE_NAME, i_s.ORDINAL_POSITION SELECT

    [Table Name] = OBJECT_NAME(c.object_id),

    [Column Name] = c.name,

    [Description] = ex.value

    sys.columns c

    sys.extended_properties ex

    ex.major_id = c.object_id

    AND ex.minor_id = c.column_id

    AND ex.name = 'MS_Description'

    OBJECTPROPERTY(c.object_id, 'IsMsShipped')=0

    -- AND OBJECT_NAME(c.object_id) = 'your_table'

ORDER

    BY OBJECT_NAME(c.object_id), c.column_id

4. 取得表說明    SELECT 表名 = case when a.colorder = 1 then d.name

                   else '' end,

       表說明 = case when a.colorder = 1 then isnull(f.value, '')

                     else '' end FROM syscolumns a

       inner join sysobjects d

          on a.id = d.id

             and d.xtype = 'U'

             and d.name <> 'sys.extended_properties'

       left join sys.extended_properties   f

         on a.id = f.major_id

            and f.minor_id = 0

Where (case when a.colorder = 1 then d.name else '' end) <>''

        (case when a.colorder=1 then d.name else '' end) 表名,

        a.colorder 字段序号,

        a.name 字段名,

       g.[value] AS 字段說明

FROM syscolumns a left join systypes b

on a.xtype=b.xusertype

inner join sysobjects d

on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'

left join sys.extended_properties g

on a.id=g.major_id AND a.colid = g.minor_id

WHERE d.[name] <>'table_desc' --你要檢視的表名，注釋掉，檢視目前資料庫所有表的字段資訊

order by a.id,a.colorder