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