Extended properties can be all sorts of annotations added about an object. They can be added manually, or by a tool.
The extended properties has no effect on queries accessing the object.
For specifying extended properties, the objects in a SQL Server database are classified into three levels: 0, 1, and 2. Level 0 is the highest level and is defined as objects that are contained at the database scope. Level 1 objects are contained in a schema or user scope, and level 2 objects are contained by level 1 objects. Extended properties can be defined for objects at any of these levels.
References to an object in one level must be qualified with the names of the higher level objects that own or contain them. For example, when you add an extended property to a table column (level 2), you must also specify the table name (level 1) that contains the column and the schema (level 0) that contains the table.
If all object types and names are null, the property belongs to the current database itself.
Extended properties are not allowed on system objects, objects outside the scope of a user-defined database, or objects not listed in Arguments as valid inputs.
Extended properties are not allowed on memory-optimized tables.
EXEC sp_addextendedproperty N'MS_Description', N'Year and Month integer.', 'SCHEMA', N'dbo', 'TABLE', N'tbXXXX', 'COLUMN', N'colName'
EXEC sp_addextendedproperty
@name = N'MS_Description', @value = N'The primary key is Id',
@level0type = 'SCHEMA', @level0name = N'dbo',
@level1type = 'TABLE', @level1name = N'tblXXXXX',
@level2type = 'CONSTRAINT', @level2name = N'XPK_tblXXXXX';
GO
EXEC sp_addextendedproperty N'MS_Description', N'This is the description for the table.', 'SCHEMA', N'dbo', 'TABLE', N'tblTable01', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_Description', N'This is the description for the column.', 'SCHEMA', N'dbo', 'TABLE', N'tblTable01', 'COLUMN', N'col01'
GO-- Add extended properties for TABLE
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=N'This is the description for the table.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'tbImageReviewStaging'--Table Name
GO
-- Add extended properties for COLUMN
EXEC sys.sp_addextendedproperty
@name=N'MS_Description',
@value=N'This is the description for the column.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'tblTable01', --Table Name
@level2type=N'COLUMN',
@level2name=N'col01' --Column Name
GORetrieving Extended Properties
SELECT
S.name as [Schema Name],
O.name AS [Object Name],
c.name AS [Column Name],
ep.name,
ep.value AS [Extended property]
FROM
sys.extended_properties EP
INNER JOIN sys.all_objects O ON ep.major_id = O.object_id
INNER JOIN sys.schemas S on O.schema_id = S.schema_id
INNER JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
SELECT S.name as [Schema Name], O.name AS [Object Name], ep.name, ep.value AS [Extended property]
FROM sys.extended_properties EP
LEFT JOIN sys.all_objects O ON ep.major_id = O.object_id
LEFT JOIN sys.schemas S on O.schema_id = S.schema_id
LEFT JOIN sys.columns AS c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
SELECT
O.name AS [Object Name],
c.name,
ep.value AS [Extended property]
FROM
sys.columns AS c INNER JOIN
sys.all_objects O ON c.object_id = O.object_id LEFT JOIN
sys.extended_properties EP ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
WHERE
O.name = 'tblXXXX'
SELECT
TABLE_NAME,
q.epExtendedProperty
FROM
information_schema.tables AS t
LEFT OUTER JOIN (
SELECT
OBJECT_NAME(ep.major_id) AS [epTableName],
CAST(ep.Value AS nvarchar(500)) AS [epExtendedProperty]
FROM
sys.extended_properties ep
WHERE
ep.name = N'MS_Description' AND ep.minor_id = 0) As q ON t.table_name = q.epTableName
WHERE
TABLE_TYPE = N'BASE TABLE'
ORDER BY
TABLE_NAME
Important: Remember to use “MS_Description” instead of “Description”.
Sources:
Comments