Description of SQL Server tables and fields

1. Add Field DescriptionsEXEC sp_addextendedproperty
' Ms_description ',
' Some description ',
' User ',
Dbo
' Table ',
TABLE_NAME,
' Column ',
column_name

• Some Description, is to add the description content
• TABLE_NAME, the name of the table.
• COLUMN_NAME, is the field name

2. Instructions for adding tablesEXEC sp_addextendedproperty
' Ms_description ',
' Some description ',
' User ',
Dbo
' Table ',
TABLE_NAME parameter Description Ibid. 3. Get the field description

SQL Server 2000

SQL Server 2005 (including 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 From Sys.columns C Left OUTER JOIN Sys.extended_properties EX On ex.major_id = c.object_id and ex.minor_id = c.column_id and ex.name = ' ms_description ' WHERE 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. Obtain the table description

SELECT Table name = case if A.colorder = 1 then D.name Else ' End, Table Description = Case If 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 if A.colorder = 1 then d.name Else ' end) <> '

SELECT

         (case if A.colorder=1 then d.name Else ' end) Table name,        a.co Lorder field ordinal,        a.name field name,       G.[value] As field description  from syscolumns A left join systypes bon A.xtype=b.xusertypeinner join sysobjects Don A.id=d.id and d.xtype= ' U ' and d.name<> ' Dtprop Erties ' left join Sys.extended_properties gon a.id=g.major_id and a.colid = G.minor_idwhere D.[name] <> ' Table_desc ' --The name of the table you want to view, comment out, view field information for all tables in the current database order by a.id,a.colorder 

--Create tables and descriptive information

Create   Table   table (A1   varchar), A2   char (2))

--Add descriptive information to the table
EXECUTE   sp_addextendedproperty   n ' ms_description ',   ' personnel information table ',   n ' user ',   n ' dbo ',   n ' table ',   N ' table ',   NULL,   NULL

--Add descriptive information for field A1
EXECUTE   sp_addextendedproperty   n ' ms_description ',   ' name ',   n ' user ',   n ' dbo ',   n ' table ',   n ' table ',   n ' column ',   n ' A1 '

--Add descriptive information for field A2
EXECUTE   sp_addextendedproperty   n ' ms_description ',   ' gender ',   n ' user ',   n ' dbo ',   n ' table ',   n ' table ',   n ' column ',   n ' A2 '
--Update the Description property of the column A1 in the table:
EXEC   sp_updateextendedproperty   ' ms_description ', ' Field 1 ', ' user ', dbo, ' table ', ' table ', ' column ', A1

--Delete The Description property of the column A1 in the table:
EXEC   sp_dropextendedproperty   ' ms_description ', ' user ', dbo, ' table ', ' Tables ', ' column ', A1

--Delete test
Drop   Table   

Description of SQL Server tables and fields