Menu
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?
232
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.
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.
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.
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.
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 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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
