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:
Post a Comment