Sql-server – Using T-SQL to dynamically create Extended Properties

sql serversql-server-2008

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?

Best Answer

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
Related Question