displaying all extended properties of an object

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 263
SYS.extended_properties is used to display all extended properties of all objects in a database.

It helps you to analyse the purpose of a column/table/schema...

Example:

--Create an extended property for DeptName of DEPT table
EXECUTE sys.sp_addextendedproperty
@name = N'DeptName_Description',
@value = N'Store department name',
@level0type = N'SCHEMA',
@level0name = N'dbo',
@level1type = N'TABLE',
@level1name = N'DEPT',
@level2type = N'COLUMN',
@level2name = N'DeptName';

--TO SEE ALL EXTENDED PROPERTIES
SELECT * FROM SYS.extended_properties
Where OBJECT_NAME( major_id) = 'DEPT'

Output:

class class_desc major_id minor_id name value
1 OBJECT_OR_COLUMN 802101898 2 DeptName_Description Store department name

Comments or Responses

Login to post response