Wednesday, July 2, 2008

Adding Extended Properties Using the System Stored Proc

I'm working on adding extended properties to our database project. There are so many tables with huge numbers of columns and very few descriptions.

I found some code on the web for using the sp_addextendedproperty to extended properties without having to open each table or objectt. Its still tedious but is saving me about 30% on the time.

Here's the code snippet:

USE [NameOfDatabase]
GO

--Script to add an Extended Property to the Table
EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPERTY1',
@value=N'Used by CRM to support Account, Contact, Opportunity, Lead Entity Model, and Service Entity Model.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPRTY2',
@value=N'Created by CRM application.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

EXEC sys.sp_addextendedproperty
@name=N'NAMEOF_EXPROPERTY3',
@value=N'Used by multiple entities.' ,
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase' --Table Name
GO

--COLUMN SECTION STARTS HERE
--Script to add an Extended Property to a columnEXEC
EXEC sys.sp_addextendedproperty
@name=N'EXPROPERTY_Column',
@value=N'Uses as a FOREIGN KEY constraint.',
@level0type=N'SCHEMA',
@level0name=N'dbo', --Schema Name
@level1type=N'TABLE',
@level1name=N'ActivityPointerBase',--Table Name
@level2type=N'COLUMN',
@level2name=N'OwningBusinessUnit'--Column Name
GO

No comments: