Include Metadata in DB Objects

In case if you want to include extended properties to the database objects you can use sp_addextendedproperty of SQL Server. Which comes handy incase if you want to include Description for a column/table/any db objects.

Suppose if I have table name Employee with columns

  • EmpId
  • FirstName
  • LastName

To Include Additional/Extended Properties to Table
EXECUTE sp_addextendedproperty @name = N’MS_Description’, @value = ‘Employee Name details’, @level0type = N’SCHEMA’, @level0name = N’dbo’, @level1type = N’TABLE’, @level1name = N’Employee’;

To Include Additional/Extended Properties to Column
EXECUTE sp_addextendedproperty @name = N’MS_Description’, @value = ‘Employee Identification ‘, @level0type = N’SCHEMA’, @level0name = N’dbo’, @level1type = N’TABLE’, @level1name = N’Employee’, @level2type = N’COLUMN’, @level2name = N’EmpId’;

Here if you notice

@name = N’MS_Description’ is name of the extended property name & it can be anything

@level0type = N’SCHEMA’
@level1type = N’TABLE’
@level2type = N’COLUMN’

This defines the type of database object & this should match with the naming convention of MS SQL.

and others for identifying the DB Objects

@level0name = N’dbo’
@level1name = N’Employee’
@level2name = N’EmpId’

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.