Description of SQL Server tables and fields

1. Added field descriptions.EXEC sp_addextendedproperty
'Ms _ description ',
'Some description ',
'User ',
Dbo,
'Table ',
Table_name,
'Column ',
Column_name

• Some Description: The Description to be added.
• Table_name, table name
• Column_name, which is the field name

2. Add Table descriptionEXEC sp_addextendedproperty
'Ms _ description ',
'Some description ',
'User ',
Dbo,
'Table ',
The table_name parameter is described above. 3. Obtain field descriptions

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 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. Get table description

SELECT Table name = case when a. colorder = 1 then d. name Else ''end, Table description = case when a. colorder = 1 then isnull (f. value ,'') Else ''end FROM syscolumns 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) <>''

 

 

 

SELECT

(Case when. colorder = 1 then d. name else ''end) Table name,. colorder field number,. name field name, g. [value] AS field description FROM syscolumns a left join policypes bon. xtype = B. xusertypeinner join sysobjects don. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties' left join sys. extended_properties gon. id = g. major_id AND. colid = g. minor_idWHERE d. [name] <> 'table _ desc' -- name of the table to be viewed, comment out, and view the field information of all tables in the current database order by. id,. colorder

-- Create a table and its description

Create table (a1 varchar (10), a2 char (2 ))

-- Add description information for the table
EXECUTE sp_addextendedproperty N 'Ms _ description', 'personnel info table ', N 'user', N 'dbo', N 'table', N 'table', NULL, NULL

-- Add description information for field a1
EXECUTE sp_addextendedproperty N 'Ms _ description', 'name', N 'user', N 'dbo', N 'table', N 'table', N 'column ', N 'a1'

-- Add description 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 attribute of column a1 in the table:
EXEC sp_updateextendedproperty 'Ms _ description', 'field 1', 'user', dbo, 'table', 'table', 'column', a1

-- Delete the description attribute of column a1 in the table:
EXEC sp_dropextendedproperty 'Ms _ description', 'user', dbo, 'table', 'table', 'column', a1

-- Delete test
Drop table