Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check if extended property description already exists before adding

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?

like image 232
ScubaSteve Avatar asked Jun 18 '13 15:06

ScubaSteve


People also ask

How do I view extended properties in SQL Server?

To look at the Extended Properties for a column, in SSMS expand Tables, find the table we created and then expand the Columns. Then right click on column "sno" and select Properties and go to the Extended Properties page.

What is use of SYS Sp_addextendedproperty?

Sp_addextendedproperty stored procedure is used to create metadata about our database objects. Using this stored procedure we can put information about Sql server objects like name of developer who created the object, to track the version of objects inside database.

What is MS_Description extended property?

Microsoft provides one extended property, MS_Description, which can be used from both Enterprise Manager and SSMS to provide a description of the object to which it is bound.

What is Fn_listextendedproperty?

fn_listextendedproperty() : This function returns the extended property value of database objects such as (table, column, index, schema and etc). Using this function we can retrieve single property value of an object or all property values of any object type.


2 Answers

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'; 
like image 95
ScubaSteve Avatar answered Sep 20 '22 14:09

ScubaSteve


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 
like image 43
Brian Westrich Avatar answered Sep 19 '22 14:09

Brian Westrich