Check if extended property description already exists before adding

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';

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 dictionaryIF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL    DROP PROCEDURE dbo.usp_addorupdatedescription;GOCREATE PROCEDURE usp_addorupdatedescription        @table nvarchar(128),  -- table name        @column nvarchar(128), -- column name, NULL if description for table        @descr sql_variant     -- description textAS    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;    ENDGO

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;GOCREATE PROCEDURE usp_addorupdatedescription    @table nvarchar(128),  -- table name    @column nvarchar(128), -- column name, NULL if description for table    @descr sql_variant     -- description textASBEGIN    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;ENDGO