Check if extended property description already exists before adding

sql sql-server exists extended-properties database-table
ScubaSteve picture ScubaSteve · Jun 18, 2013 · Viewed 30.2k times · Source

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?

Answer

ScubaSteve picture ScubaSteve · Jun 18, 2013

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

Related questions

SQL Server: IF EXISTS ; ELSE

I have a tableA: ID value 1 100 2 101 2 444 3 501 Also TableB ID Code 1 2 Now I want to populate col = code of table B if there exists ID = 2 in tableA. for multiple values , get max value. else populate it with '123'. Now here …

Read More
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

My query is as follows, and contains a subquery within it: select count(distinct dNum) from myDB.dbo.AQ where A_ID in (SELECT DISTINCT TOP (0.1) PERCENT A_ID, COUNT(DISTINCT dNum) AS ud FROM myDB.dbo.AQ WHERE M &…

Read More
How to use SQL Select statement with IF EXISTS sub query?

How to select Boolean value from sub query with IF EXISTS statement (SQL Server)? It should be something like : SELECT TABEL1.Id, NewFiled = (IF EXISTS(SELECT Id FROM TABLE2 WHERE TABLE2.ID = TABEL1.ID) SELECT 'TRUE' ELSE SELECT 'FALSE') FROM …

Read More