Introduction to SQL Server Extended Properties _mssql

The description of Extended properties in SQL Server Help is:
The Extended Properties Sets or retrieves provider-specific connection information that cannot is explicitly desc Ribed through the property mechanism.
The following actions are available for extended properties:

Copy Code code as follows:

EXEC sp_addextendedproperty n ' ms_description ', n ' field description ', n ' user ', n ' dbo ',

n ' table ', n ' list name ', N ' column ', n ' field name '
Go

For example: EXEC sp_addextendedproperty n ' ms_description ', n ' address ', n ' user ', dbo,n ' table ',

Copy Code code as follows:

N ' a ', N ' column ', A_add
go--My table is a, to add a field description to the field A_add: Address

Other Related:

Delete:

Copy Code code as follows:

EXEC sp_dropextendedproperty n ' ms_description ', n ' user ', dbo,n ' table ', n ' list name ',

N ' column ', field name

Modify:

Copy Code code as follows:

EXEC sp_updateextendedproperty n ' ms_description ', n ' field description ', n ' user ',

Dbo,n ' table ', N ' tables name ', ' column ', field

As for queries, SQL Server provides system functions Fn_listextendedproperty ():

Copy Code code as follows:

--Get a description of a field
SELECT *
From:: Fn_listextendedproperty (NULL, ' user ', ' dbo ', ' table ', ' table name ', ' column ',

Default)--other variables, according to your request you can write, as long as the table name for your
where objname = ' field name '

You can also query the system tables yourself:

Copy Code code as follows:

SELECT O.name as TableName, c.name as ColumnName, P.[value] as Description
From Sysproperties P INNER JOIN
sysobjects o on o.id = p.id INNER JOIN
syscolumns C on p.id = c.id and p.smallid = C.colid
WHERE (p.name = ' ms_description ')
ORDER BY O.name