Check if extended property description already exists before adding

Question

So, I have a script that adds extended properties, some describing a table, some describing a column. How can I check if the extended property exists before adding it so that the script does not throw an error?

ScubaSteve · Accepted Answer · 2013-07-30T18:06:12.670

This first script checks if the extended property describing the table exists:

IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('Table_Name') AND [name] = N'MS_Description' AND [minor_id] = 0)
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'This table is responsible for holding information.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_Name';

This second script checks if the extended property describing the column exists:

IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] = OBJECT_ID('Table_Name') AND [name] = N'MS_Description' AND [minor_id] = (SELECT [column_id] FROM SYS.COLUMNS WHERE [name] = 'Column_Name' AND [object_id] = OBJECT_ID('Table_Name')))
EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = N'This column is responsible for holding information for table Table_Name.', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Table_Name', @level2type = N'COLUMN', @level2name = N'Column_Name';

Generally, at least historically, SO has allowed this provided the question/answer provides value. They do ask, I believe, that you provide some time before accepting your own answer to allow others to also answer and that you objectively select the best answer, not just marking your own answer as correct to get the +rep. So this is fine, the only concern is people not giving others time to provide an answer before they accept their own. — DavidScherer, Jun 11 '18 at 15:07
*Important*: If you have two tables with the same name in the same database but in different schemas, that `if` condition will fail. I am trying to find a way to specify the schema for that `IF` condition (I am not a SQL Server hard user) and I will share here wether I find. — sdlins, Jun 12 '19 at 01:41

score 7 · Answer 2 · answered Jul 22 '16 at 14:19

Here is another stored procedure approach, similar to Ruslan K.'s, but that doesn't involve try/catch or explicit transactions:

-- simplify syntax for maintaining data dictionary

IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_addorupdatedescription;
GO

CREATE PROCEDURE usp_addorupdatedescription
        @table nvarchar(128),  -- table name
        @column nvarchar(128), -- column name, NULL if description for table
        @descr sql_variant     -- description text
AS
    BEGIN
        SET NOCOUNT ON;

        IF @column IS NOT NULL
            IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
            WHERE [major_id] = OBJECT_ID(@table) AND [name] = N'MS_Description'
                  AND [minor_id] = (SELECT [column_id]
                                    FROM SYS.COLUMNS WHERE [name] = @column AND [object_id] = OBJECT_ID(@table)))
                EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = @descr,
                                               @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE',
                                               @level1name = @table, @level2type = N'COLUMN', @level2name = @column;
            ELSE
                EXECUTE sp_updateextendedproperty @name = N'MS_Description',
                                                  @value = @descr, @level0type = N'SCHEMA', @level0name = N'dbo',
                                                  @level1type = N'TABLE', @level1name = @table,
                                                  @level2type = N'COLUMN', @level2name = @column;
        ELSE
            IF NOT EXISTS (SELECT NULL FROM SYS.EXTENDED_PROPERTIES
            WHERE [major_id] = OBJECT_ID(@table) AND [name] = N'MS_Description'
                  AND [minor_id] = 0)
                EXECUTE sp_addextendedproperty @name = N'MS_Description', @value = @descr,
                                               @level0type = N'SCHEMA', @level0name = N'dbo',
                                               @level1type = N'TABLE', @level1name = @table;
            ELSE
                EXECUTE sp_updateextendedproperty @name = N'MS_Description', @value = @descr,
                                                  @level0type = N'SCHEMA', @level0name = N'dbo',
                                                  @level1type = N'TABLE', @level1name = @table;
    END
GO

Ruslan K. · Answer 3 · 2017-09-05T10:46:59.097

5

I wrote simple stored procedure to add or update extended property 'MS_Description':

IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_addorupdatedescription;
GO

CREATE PROCEDURE usp_addorupdatedescription
    @table nvarchar(128),  -- table name
    @column nvarchar(128), -- column name, NULL if description for table
    @descr sql_variant     -- description text
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @c nvarchar(128) = NULL;

    IF @column IS NOT NULL
        SET @c = N'COLUMN';

    BEGIN TRY
        EXECUTE sp_updateextendedproperty  N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
    END TRY
    BEGIN CATCH
        EXECUTE sp_addextendedproperty N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
    END CATCH;
END
GO

edited Sep 05 '17 at 10:46

answered Nov 23 '15 at 10:34

Ruslan K.

1,912
1
15
18

I like the approach this has, however i ran into an instance where i am having Open Transactions after executing this query. ``` Msg 266, Level 16, State 2, Procedure up_DataDict, Line 178 Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1. ``` – Damian Mar 21 '16 at 17:18
I posted a similar approach below, which doesn't use try/catch, transaction rollbacks (not as elegant, but avoids the concerns raised by Damian – Brian Westrich Jul 22 '16 at 14:16
Useful. However using exception handling to control program flow (i.e. what could be done with an IF statement), is a [code smell](https://en.wikipedia.org/wiki/Code_smell)!. See this [stackoverflow question](https://softwareengineering.stackexchange.com/questions/189222/are-exceptions-as-control-flow-considered-a-serious-antipattern-if-so-why). – Reversed Engineer Aug 03 '17 at 15:16
.. and [this](https://softwareengineering.stackexchange.com/questions/107723/arguments-for-or-against-using-try-catch-as-logical-operators) – Reversed Engineer Aug 03 '17 at 15:19

score 3 · Answer 4 · answered Dec 21 '18 at 06:23

Maybe my answer did not directly connect to this question, but I would like to point out that MS_Description is actually case sensitive, even we add it with SQL. If we use MS_DESCRIPTION instead of MS_Description, it will not show up in the SMSS table design view.

In my case, I have to do something like this to removed the existing description and add a correct one.

IF EXISTS (
    SELECT NULL
    FROM SYS.EXTENDED_PROPERTIES
    WHERE [major_id] = OBJECT_ID('TableName')
        AND [name] = N'MS_DESCRIPTION'
        AND [minor_id] = (
            SELECT [column_id]
            FROM SYS.COLUMNS
            WHERE [name] = 'ColumnName'
                AND [object_id] = OBJECT_ID('Tablename')
            )
    )
    EXEC sys.sp_dropextendedproperty @name = N'MS_DESCRIPTION'
        ,@level0type = N'SCHEMA'
        ,@level0name = N'dbo'
        ,@level1type = N'TABLE'
        ,@level1name = N'TableName'
        ,@level2type = N'COLUMN'
        ,@level2name = N'ColumnName'
    GO

EXEC sys.sp_addextendedproperty @name = N'MS_Description'
,@value = N'Description detail'
,@level0type = N'SCHEMA'
,@level0name = N'dbo'
,@level1type = N'TABLE'
,@level1name = N'TableName'
,@level2type = N'COLUMN'
,@level2name = N'ColumnName'

GO

score 2 · Answer 5 · answered Dec 24 '18 at 14:39

Please find my extension to Brian Westrich's answer above, but this version allows the update and addition of any extended property on a table and column and not just the MS_Description. Also, it allows you to use the the stored procedure to add and update extended properties in different databases so you only need one copy on a server.

CREATE PROCEDURE dbo.AddOrUpdateExtendedProperty
     @Database      NVARCHAR(128)   -- Database name
    ,@Schema        NVARCHAR(128)   -- Schema name
    ,@Table         NVARCHAR(128)   -- Table name
    ,@Column        NVARCHAR(128)   -- Column name, NULL if description for table
    ,@PropertyName  NVARCHAR(128)   -- Property name
    ,@PropertyValue SQL_VARIANT     -- Property value
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @NSQL NVARCHAR(MAX);

    DECLARE @Level2Type NVARCHAR(128) = NULL;

    DECLARE @Params NVARCHAR(MAX) = N'@Schema NVARCHAR(128), @Table NVARCHAR(128), @Column NVARCHAR(128), @PropertyName NVARCHAR(128), @PropertyValue SQL_VARIANT';

    IF @Column IS NOT NULL
    BEGIN
        SET @NSQL = 'USE ' + @Database + ';

            IF NOT EXISTS
            (
                SELECT NULL FROM sys.extended_properties
                 WHERE     major_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
                       AND name = @PropertyName
                      AND minor_id = (SELECT column_id
                                        FROM sys.columns
                                       WHERE     name = @Column
                                             AND object_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
                                     )
            )
            BEGIN
                EXECUTE sp_addextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table
                    ,@level2type = N''COLUMN''
                    ,@level2name = @Column;
            END
            ELSE
            BEGIN
                EXECUTE sp_updateextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table
                    ,@level2type = N''COLUMN''
                    ,@level2name = @Column;
            END
        ';

        EXECUTE sp_executesql
             @NSQL
            ,@Params
            ,@Schema
            ,@Table
            ,@Column
            ,@PropertyName
            ,@PropertyValue;
    END
    ELSE
    BEGIN
        SET @NSQL = 'USE ' + @Database + ';

            IF NOT EXISTS
            (
                SELECT NULL
                  FROM sys.extended_properties
                 WHERE     major_id = OBJECT_ID(''' + @Schema + '.' + @Table + ''')
                       AND name = @PropertyName
                       AND minor_id = 0
            )
            BEGIN
                EXECUTE sp_addextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table;
            END
            ELSE
            BEGIN
                EXECUTE sp_updateextendedproperty
                     @name = @PropertyName
                    ,@value = @PropertyValue
                    ,@level0type = N''SCHEMA''
                    ,@level0name = @Schema
                    ,@level1type = N''TABLE''
                    ,@level1name = @Table;
            END
        ';

        EXECUTE sp_executesql
             @NSQL
            ,@Params
            ,@Schema
            ,@Table
            ,@Column
            ,@PropertyName
            ,@PropertyValue;
    END
END

score 1 · Answer 6 · answered Aug 08 '14 at 06:02

To check any of extended property that available for the given table use as below.

IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID('<schema>.<table_name>') AND [name] = N'MS_Description')

If your table has more than one extended property, give the column ID as minor_id.

IF EXISTS(SELECT 1 FROM sys.extended_properties WHERE [major_id] = OBJECT_ID('<schema>.<table_name>') AND [name] = N'MS_Description' AND minor_id = 3)

Query sys.extended_properties catalog view to get all the extended properties in your database.

For more details use http://msdn.microsoft.com/en-us/library/ms177541(v=sql.110).aspx

score 0 · Answer 7 · answered Jun 17 '19 at 17:07

Building on ScubaSteve's answer, the following queries will allow you to check for an MS_Description property on a column or table within a specified schema by name. To check for a table simply replace the predicate c.name = '<column>' with c.name IS NULL or d.minor_id = 0

To enumerate the MS_Description properties by schema use -

SELECT a.name as [schema], b.name as [table], c.name as [column], d.name, d.value
FROM sys.schemas a
JOIN sys.tables b ON a.schema_id = b.schema_id
LEFT JOIN sys.columns c ON b.object_id = c.object_id
JOIN sys.extended_properties d ON d.major_id = b.object_id AND d.minor_id = ISNULL(c.column_id,0)
WHERE a.name = '<schema>' AND b.name = '<table>' AND c.name = '<column>' AND d.name = 'MS_Description'

To check if an MS_Description property does not exist before adding it surround your add statement with -

IF NOT EXISTS (SELECT 1 FROM sys.schemas a JOIN sys.tables b ON a.schema_id = b.schema_id LEFT JOIN sys.columns c ON b.object_id = c.object_id JOIN sys.extended_properties d ON d.major_id = b.object_id AND d.minor_id = ISNULL(c.column_id,0)
               WHERE a.name = '<schema>' AND b.name = '<table>' AND c.name = '<column>' AND d.name = 'MS_Description'
               )
BEGIN
    --EXEC sp_addextendedproperty statement goes here
END

The joins in these queries could probably be ordered better to eliminate the ISNULL function, but this should get what you're looking for.

score 0 · Answer 8 · answered Sep 13 '22 at 01:50

Loved the answers by Brian Westrich & corky_bantam

Below is my combination of the two for what I was trying to achieve. Not sure if anyone would be interested but I thought Id share just in case.

I wanted to be able to set multiple different extended properties for the description, source field, source system etc. I actually sit another Stored Procedure over the top which I can then feed the standard values to.

I did like the option of running across databases that corky_bantam did but I decided to keep it simple for this version. Maybe in the future I will find the need to run it across databases and I will steal that version :)

I tried to simplify the if not exist section - debatable if its really any better, but I find it easier to follow.

I made most values variables (My High School IT Teacher will be proud) so I can run this for views etc.

Also note the @column_name VARCHAR(500) = NULL I did that so I don't have to provide the variable at all when running. Im too lazy to put @column_name = NULL all the time when setting values at an object level

    CREATE OR ALTER PROCEDURE [tools].[sp_set_extended_properties]

        @extended_property_name VARCHAR(500), --the extended property to set/update
        @schema_name VARCHAR(500), --schema name
        @object_name VARCHAR(500), --object name
        @column_name VARCHAR(500) = NULL, -- column name, NULL if description for object
        @value sql_variant --the value to assign
AS
    BEGIN
        SET NOCOUNT ON;

        
        DECLARE @object_id INT
        DECLARE @object_type VARCHAR(50)
        DECLARE @column_id INT
        

        SELECT 
        @object_id = o.object_id
        ,@object_type = CASE o.type 
                            WHEN 'U' THEN 'TABLE'
                            ELSE o.type_desc END
        ,@column_id = c.column_id
        FROM sys.objects o 
        INNER JOIN sys.schemas s on o.schema_id = s.schema_id
        INNER JOIN sys.columns c on o.object_id = c.object_id
        WHERE o.name = @object_name
        AND s.name = @schema_name
        AND c.name = ISNULL(@column_name, c.name)

        --SELECT @object_id, @object_type, @column_id
        
        IF @column_name IS NOT NULL
            IF NOT EXISTS (SELECT NULL 
                            FROM SYS.EXTENDED_PROPERTIES
                            WHERE [major_id] = @object_id 
                            AND [name] = @extended_property_name
                            AND [minor_id] = @column_id)
                EXECUTE sp_addextendedproperty @name = @extended_property_name, @value = @value,
                                               @level0type = N'SCHEMA', @level0name = @schema_name, @level1type = @object_type,
                                               @level1name = @object_name, @level2type = N'COLUMN', @level2name = @column_name;
            ELSE
                EXECUTE sp_updateextendedproperty @name = @extended_property_name,
                                                  @value = @value, @level0type = N'SCHEMA', @level0name = @schema_name,
                                                  @level1type = @object_type, @level1name = @object_name,
                                                  @level2type = N'COLUMN', @level2name = @column_name;
        ELSE
            IF NOT EXISTS (SELECT NULL 
                            FROM SYS.EXTENDED_PROPERTIES
                            WHERE [major_id] = @object_id 
                            AND [name] = @extended_property_name
                            AND [minor_id] = 0)
                EXECUTE sp_addextendedproperty @name = @extended_property_name, @value = @value,
                                               @level0type = N'SCHEMA', @level0name = @schema_name,
                                               @level1type = @object_type, @level1name = @object_name;
            ELSE
                EXECUTE sp_updateextendedproperty @name = @extended_property_name, @value = @value,
                                                  @level0type = N'SCHEMA', @level0name = @schema_name,
                                                  @level1type = @object_type, @level1name = @object_name;
    
    END
GO

score 0 · Answer 9 · edited Jan 28 '23 at 20:46

For this I use the fn_listextendedproperty function which allows to specify schema.

declare @description nvarchar(max)=N'New description'
if exists (select 1 from fn_listextendedproperty(N'MS_Description', 
           N'SCHEMA', N'dbo', 
           N'TABLE',N'TableName', N'COLUMN',N'ColumnName'))
   EXEC sys.sp_updateextendedproperty @name=N'MS_Description', @value=@description, 
           @level0type=N'SCHEMA', @level0name=N'dbo',
           @level1type=N'TABLE' , @level1name=N'TableName', 
           @level2type=N'COLUMN', @level2name=N'ColumnName'
ELSE
   EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=@description , 
           @level0type=N'SCHEMA', @level0name=N'dbo', 
           @level1type=N'TABLE' ,  @level1name=N'TableName', 
           @level2type=N'COLUMN', @level2name=N'ColumnName'
GO

Check if extended property description already exists before adding

9 Answers9