Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I would like to add Description and Status in the Extended Properties of a large database to all my tables and columns so that someone can fill in the values.

It would be very laborious to add these two properties to each table and column. Is there a way to use T-SQL to be able to generate these via script?

share|improve this question
add comment

2 Answers

As Jon mentioned, you can do it using system stored procedures.

Ref: Glenn Berry wrote this for 2008 and this is to add extended property on a table. Also MSSQL Tips has a good example as well.

You can adapt his script for columns as well.

-- Routine to add or update an extended property on a table
DECLARE @TableName SYSNAME 
set @TableName = N'Kin_Test';   -- Name of table
DECLARE @MS_DescriptionValue NVARCHAR(200); 
SET @MS_DescriptionValue = N'Short table description - Kin';    -- Short description
DECLARE @MS_Description NVARCHAR(200) 
set @MS_Description = NULL;

SET @MS_Description = (SELECT CAST(Value AS NVARCHAR(200)) AS [MS_Description]
FROM sys.extended_properties AS ep
WHERE ep.major_id = OBJECT_ID(@TableName)
AND ep.name = N'MS_Description' AND ep.minor_id = 0); 

IF @MS_Description IS NULL
    BEGIN
        EXEC sys.sp_addextendedproperty 
         @name  = N'MS_Description', 
         @value = @MS_DescriptionValue, 
         @level0type = N'SCHEMA',
         @level0name = N'dbo', 
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
ELSE
    BEGIN
        EXEC sys.sp_updateextendedproperty 
         @name  = N'MS_Description', 
         @value = @MS_DescriptionValue, 
         @level0type = N'SCHEMA',
         @level0name = N'dbo', 
         @level1type = N'TABLE',
         @level1name = @TableName;
    END
GO
-- End of routine
share|improve this answer
add comment

Yes, there are system stored procedures that allow you to programmatically add/edit/delete extended properties:

sys.sp_addextendedproperty
sys.sp_updateextendedproperty
sys.sp_dropextendedproperty

Since you want to use these for a set of objects, you can use them with either dynamic SQL or a cursor, as you prefer.

If you require "merge" functionality (run the same script multiple times), be aware that these procedures blow up with an error if the extended property already exists/doesn't exist. You'll need to use the sys.extended_properties DMV to check for existence first.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.