Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Modifying and deleting extended properties

By Adam Aspin,

In the two previous articles, you saw how to:

So now it is time to look at the remaining two of the three system stored procedures which concern extended properties. These are:

  • sp_updateextendedproperty
  • sp_dropextendedproperty

Once these two are understood (which will not take very long) we will look at ways to modify and delete one or a set of extended properties. This will include

  • Output the extended properties from a database, wrapped in the "sp_updateextendedproperty" system stored procedure, to let you make multiple modifications to existing extended properties.
  • Output the list of extended properties from a database, wrapped in the "sp_dropextendedproperty" system stored procedure, to let you drop a set of existing extended properties.

So, firstly let's take a look at modifying extended properties one-by-one and type by type, and then at a script to allow you to select a set or a range of extended properties for modification - or deletion.

Modify an extended property

Assuming that you can now see how extended properties are stored, and how to output them, you might well need to modify them - other than individually through the SSMS interface, (which I will let you extrapolate from the first article) that is.

The system stored procedure "pr_updateextendedproperty" is virtually identical to "pr_addextendedproperty". The same hierarchy of objects still applies, and the same definitions (schema, table, view etc...) are still used.

To modify an existing extended property, you will need something like this (for a table):

EXEC sys.sp_updateextendedproperty 
@level0type = N'SCHEMA' -- The object schema
,@level0name = [Sales] -- The object schema name
,@level1type = N'TABLE' -- The object type
,@level1name = [Stock] -- The object name
,@name = N'Overview' -- The "Classification"
,@value = N'This is a great table' -- The "Comment"
;

Essentially, you are using the same structure as when you added the extended property, but without specifying the "name". So I will not give examples of each one, but will let you extrapolate from the examples given in the first article.

Output all the extended properties from a database for modification

Things get more interesting when you want to script out all the extended properties from a database for modification, wrapped in the "pr_updateextendedproperty" system stored procedure. So let's delve into this.
Once again, let's first look at an example of each level of extended property. I described how extended properties are stored in four "levels" in the first article in this series, as I find this helps to understand their storage, and so will continue with this approach here.

First level - Database:

SELECT 
'EXEC sys.sp_updateextendedproperty @name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
WHERE class_desc = N'DATABASE'

The output is:

Schema (level 0):

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.schemas SCH
ON SEP.major_id = SCH.schema_id
WHERE SEP.class_desc = N'SCHEMA'

The output is:

Table (level 1):

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
ON TBL.object_id = SEP.major_id
WHERE SEP.class = 1
AND SEP.minor_id = 0
AND (SEP.value <> '1' AND SEP.value <> 1)

The output is:

Column (level 2):

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''COLUMN'', @level2name = [' + COL.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.columns COL
ON SEP.major_id = COL.object_id
AND SEP.minor_id = COL.column_id
INNER JOIN sys.tables TBL
ON SEP.major_id = TBL.object_id
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
WHERE SEP.class = 1
AND (SEP.value <> '1' AND SEP.value <> 1)

The output is:

So, armed with this knowledge, here are the code snippets to modify all existing extended properties in your database. I will let you see the output, when you run it against your own databases.

Column description

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''COLUMN'', @level2name = [' + COL.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.columns COL
ON SEP.major_id = COL.object_id
AND SEP.minor_id = COL.column_id
INNER JOIN sys.tables TBL
ON SEP.major_id = TBL.object_id
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
WHERE SEP.class = 1
AND (SEP.value <> '1' AND SEP.value <> 1)
and sep.name = 'Overview'

Primary keys

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SKC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
INNER JOIN sys.key_constraints SKC
ON SEP.major_id = SKC.object_id
ON TBL.object_id = SKC.parent_object_id
WHERE SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT'

Unique constraints

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SKC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
INNER JOIN sys.key_constraints SKC
ON SEP.major_id = SKC.object_id
ON TBL.object_id = SKC.parent_object_id
WHERE SKC.type_desc = N'UNIQUE_CONSTRAINT'
AND (SEP.value <> '1' AND SEP.value <> 1)

Check constraints

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + CHK.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.check_constraints CHK
ON SEP.major_id = CHK.object_id
INNER JOIN sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
ON CHK.parent_object_id = TBL.object_id

Indexes

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''INDEX'', @level2name = [' + SIX.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.indexes SIX
INNER JOIN sys.extended_properties SEP
ON SIX.object_id = SEP.major_id
AND SIX.index_id = SEP.minor_id
INNER JOIN sys.tables TBL
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id
ON SIX.object_id = TBL.object_id
WHERE SEP.class_desc = N'INDEX'
AND SIX.is_primary_key = 0

Foreign Keys

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SFK.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.foreign_keys SFK
ON SEP.major_id = SFK.object_id
INNER JOIN sys.tables TBL
ON SFK.parent_object_id = TBL.object_id
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id

Defaults

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SDC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.default_constraints SDC
INNER JOIN sys.schemas SCH
INNER JOIN sys.tables TBL
ON SCH.schema_id = TBL.schema_id
ON SDC.parent_object_id = TBL.object_id
INNER JOIN sys.extended_properties SEP
ON SDC.object_id = SEP.major_id

Views

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''VIEW'', @level1name = [' + VIW.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.views VIW
ON SEP.major_id = VIW.object_id
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id
WHERE SEP.minor_id = 0

View Columns

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''VIEW'', @level1name = [' + VIW.name + '] , @level2type = ''COLUMN'', @level2name = [' + COL.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.columns COL
ON SEP.major_id = COL.object_id
AND SEP.minor_id = COL.column_id
INNER JOIN sys.views VIW
ON SEP.major_id = VIW.object_id
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id
WHERE SEP.class = 1
AND (SEP.value <> '1' AND SEP.value <> 1)

View Indexes

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''VIEW'', @level1name = [' + VIW.name + '] , @level2type = ''INDEX'', @level2name = [' + SIX.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.indexes SIX
INNER JOIN sys.extended_properties SEP
ON SIX.object_id = SEP.major_id
AND SIX.index_id = SEP.minor_id
INNER JOIN sys.views VIW
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id
ON SIX.object_id = VIW.object_id
WHERE SEP.class_desc = N'INDEX'

User-defined Functions

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''FUNCTION'', @level1name = [' + OBJ.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.objects OBJ
ON SEP.major_id = OBJ.object_id
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
WHERE OBJ.type_desc LIKE N'%FUNCTION%'
AND SEP.minor_id = 0

User-defined Function parameters

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''FUNCTION'', @level1name = [' + OBJ.name + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.objects OBJ
ON SEP.major_id = OBJ.object_id
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.parameters PRM
ON SEP.major_id = PRM.object_id
AND SEP.minor_id = PRM.parameter_id
WHERE SEP.class_desc = N'PARAMETER'
AND OBJ.type IN ('FN', 'IF', 'TF')

Stored Procedures

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''PROCEDURE'', @level1name = [' + PRC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.procedures PRC
ON SEP.major_id = PRC.object_id
INNER JOIN sys.schemas SCH
ON PRC.schema_id = SCH.schema_id
WHERE SEP.minor_id = 0

Stored Procedure parameters

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''PROCEDURE'', @level1name = [' + SPR.name + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.procedures SPR
ON SEP.major_id = SPR.object_id
INNER JOIN sys.schemas SCH
ON SPR.schema_id = SCH.schema_id
INNER JOIN sys.parameters PRM
ON SEP.major_id = PRM.object_id
AND SEP.minor_id = PRM.parameter_id
WHERE SEP.class_desc = N'PARAMETER'

DDL Triggers

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''TRIGGER'', @level0name = [' + TRG.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.triggers TRG
ON SEP.major_id = TRG.object_id
WHERE TRG.parent_class_desc = N'DATABASE'

Table triggers

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''TRIGGER'', @level2name = [' + TRG.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.tables TBL
INNER JOIN sys.triggers TRG
ON TBL.object_id = TRG.parent_id
INNER JOIN sys.extended_properties SEP
ON TRG.object_id = SEP.major_id
INNER JOIN sys.schemas SCH
ON TBL.schema_id = SCH.schema_id

View triggers

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''VIEW'', @level1name = [' + VIW.name + '] , @level2type = ''TRIGGER'', @level2name = [' + TRG.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.views VIW
INNER JOIN sys.triggers TRG
ON VIW.object_id = TRG.parent_id
INNER JOIN sys.extended_properties SEP
ON TRG.object_id = SEP.major_id
INNER JOIN sys.schemas SCH
ON VIW.schema_id = SCH.schema_id

Partition Functions

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''PARTITION FUNCTION'', @level0name = [' + PFN.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.partition_functions PFN
ON SEP.major_id = PFN.function_id

Partition Schemes

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''PARTITION SCHEME'', @level0name = [' + PSC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.partition_schemes PSC
ON SEP.major_id = PSC.function_id

Filegroups

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + DSP.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.data_spaces DSP
ON SEP.major_id = DSP.data_space_id
WHERE DSP.type_desc = 'ROWS_FILEGROUP'

Filegroups and logical file

SELECT 
'EXEC sys.sp_updateextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + DSP.name + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + DBF.name + ' ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''' ,@value = ''' + REPLACE(CAST(SEP.value AS NVARCHAR(4000)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.database_files DBF
ON SEP.major_id = DBF.file_id
INNER JOIN sys.data_spaces DSP
ON DBF.data_space_id = DSP.data_space_id
WHERE SEP.class_desc = N'DATABASE_FILE'

Notes:

  • You cannot update a non-existent extended property - you must create it first. Unfortunately "sys.sp_updateextendedproperty" will not create the extended property if it is not already there!
  • You can, of course not use named parameters, but merely enter the parameter values in the correct order.

Delete one or a set of extended properties.

Deleting (dropping) extended properties is remarkably similar to updating them - except that you will use the system stored procedure sys.sp_dropextendedproperty, and that you do not need to specify the value (the comment or descriptive text) - after all, this is what you want to modify.

Basically, you will need to write something like (for a table):

EXEC sys.sp_dropextendedproperty 
@level0type = N'SCHEMA' -- The object schema
,@level0name = [Sales] -- The object schema name
,@level1type = N'TABLE' -- The object type
,@level1name = [Stock] -- The object name
,@name = N'Overview' -- The "Classification"
;

Here are a couple of examples:

Database

SELECT 
'EXEC sys.sp_dropextendedproperty @name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
FROM sys.extended_properties SEP
WHERE class_desc = N'DATABASE'

Schema

SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
FROM sys.extended_properties SEP
INNER JOIN sys.schemas SCH
ON SEP.major_id = SCH.schema_id
WHERE SEP.class_desc = N'SCHEMA'

Once again, I see no point in repeating a set of scripts which have been given earlier, and so refer you to the first article - or the attached script [GetExtendedPropertiesForDelete.sql], which contains drop scripts for each object type covered in these articles. If you are using the scripts from the first article to drop individual extended properties, remember to remove the "value" parameter from the script. If you are using the scripts given above to list out sets of extended properties, then you must also exclude the final generation of the "value" parameter. And, of course, in both cases, alter sp_addextendedproperty or sp_updateextendedproperty to read sp_dropextendedproperty.

Finally, as promised at the top of this article, here is a script to let you produce the statements to either drop or to update extended properties for a set of database objects. This script does not attempt to cover all objects, but limits itself to a subset of core schema-scoped objects (tables and views with their dependent objects such as columns, constraints and views). The choice of these objects is purely personal, and is based on the principal requirements that I have encountered over the years. You can extend this as you think fit so suit your needs.

Note that if you do not specify any schema or table or view you will get all schemas with all their tables and views, by default. To limit the output, merely enter a comma-separated list of table or view objects for the variable "@TableViewListIN" . Similarly to restrict to a set of schemas, enter a comma-separated list of table or view objects for the variable "@SchemaListIN". Replacing 'Append' with 'Drop' will script drop statements for extended properties.
-----------------------------------------------------------------------
-- User-defined variables
-----------------------------------------------------------------------
DECLARE @SchemaListIN VARCHAR(4000)
DECLARE @TableViewListIN VARCHAR(4000)
DECLARE @DropAppend VARCHAR(15)
SET @SchemaListIN = ''
SET @TableViewListIN = ''
SET @DropAppend = 'Append'
-----------------------------------------------------------------------
-- Process variables
-----------------------------------------------------------------------
DECLARE @SchemaList VARCHAR(4000)
DECLARE @TableViewList VARCHAR(4000)
DECLARE @CharPosition int
SET @SchemaList = ''
SET @TableViewList = ''
-----------------------------------------------------------------------
-- Start the process:
IF OBJECT_ID('tempdb..#ModifyDrop') IS NOT NULL
DROP TABLE tempdb..#ModifyDrop;
CREATE TABLE #ModifyDrop (PrimaryObjectType VARCHAR(25), SecondaryObjectType VARCHAR(25), SchemaName NVARCHAR(128), PrimaryObjectName NVARCHAR(128), SecondaryObjectName NVARCHAR(128), Classification NVARCHAR(128), DescriptionText NVARCHAR(1700), SQLText NVARCHAR(2500))
-----------------------------------------------------------------------
-- Parse list of schemas to process / get list of schemas according to types required
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#SchemaList') IS NOT NULL
DROP TABLE tempdb..#SchemaList;
CREATE TABLE #SchemaList (SchemaName VARCHAR(128)) -- Used to hold list of schemata to process
IF @SchemaListIN <> '' -- See if there is a list of columns to process
BEGIN
-- Process list
SET @SchemaList = @SchemaListIN + ','
SET @CharPosition = 0
WHILE CHARINDEX(',', @SchemaList) > 0
BEGIN
SET @CharPosition = CHARINDEX(',', @SchemaList)
INSERT INTO #SchemaList (SchemaName) VALUES (LTRIM(RTRIM(LEFT(@SchemaList, @CharPosition - 1))))
SET @SchemaList = STUFF(@SchemaList, 1, @CharPosition, '')

END -- While loop
END -- If test for list of schema names
ELSE
BEGIN -- Use all schema names, to avoid filtering
INSERT INTO #SchemaList (SchemaName)
SELECT [name]
FROM [sys].[schemas]
WHERE schema_id < 16000
END -- Else test to get all schema names
-----------------------------------------------------------------------
-- Parse list of schemas to process / get list of schemas according to types required
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#TableViewList') IS NOT NULL
DROP TABLE tempdb..#TableViewList;
CREATE TABLE #TableViewList (TableViewName VARCHAR(128)) -- Used to hold list of tables & views to process
IF @TableViewListIN <> '' -- See if there is a list of columns to process
BEGIN
-- Process list
SET @TableViewList = @TableViewListIN + ','
SET @CharPosition = 0
WHILE CHARINDEX(',', @TableViewList) > 0
BEGIN
SET @CharPosition = CHARINDEX(',', @TableViewList)
INSERT INTO #TableViewList (TableViewName) VALUES (LTRIM(RTRIM(LEFT(@TableViewList, @CharPosition - 1))))
SET @TableViewList = STUFF(@TableViewList, 1, @CharPosition, '')

END -- While loop
END -- If test for list of TableView names
ELSE
BEGIN -- Use all TableView names, to avoid filtering
INSERT INTO #TableViewList (TableViewName)
SELECT OBJ.[name]
FROM sys.objects OBJ
WHERE OBJ.type_desc IN ('USER_TABLE','VIEW')
END -- Else test to get all TableView names
-----------------------------------------------------------------------
-- Table or View objects
-----------------------------------------------------------------------
INSERT INTO #ModifyDrop (PrimaryObjectType, SecondaryObjectType, SchemaName, PrimaryObjectName, SecondaryObjectName, Classification, DescriptionText)
SELECT
CASE
WHEN OBJ.type_desc = 'USER_TABLE' THEN 'Table'
WHEN OBJ.type_desc = 'VIEW' THEN 'View'
END AS PrimaryObjectType
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectType
,SCH.name AS SchemaName
,OBJ.name AS PrimaryObjectName
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectName
,SEP.name AS Classification
,CAST(SEP.value AS NVARCHAR(1700)) AS DescriptionText
FROM sys.extended_properties SEP
INNER JOIN sys.objects OBJ
ON SEP.major_id = OBJ.object_id
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN #SchemaList tmpSCH
ON tmpSCH.SchemaName = SCH.name
INNER JOIN #TableViewList tmpTbv
ON tmpTbv.TableViewName = OBJ.name
WHERE (SEP.minor_id = 0 AND SEP.class_desc = N'OBJECT_OR_COLUMN' AND OBJ.type_desc IN ('USER_TABLE','VIEW'))
AND SEP.name NOT LIKE 'MS_%'
-----------------------------------------------------------------------
-- Column objects
-----------------------------------------------------------------------
INSERT INTO #ModifyDrop (PrimaryObjectType, SecondaryObjectType, SchemaName, PrimaryObjectName, SecondaryObjectName, Classification, DescriptionText)
SELECT
CASE
WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table'
WHEN OBJ.type_desc = N'VIEW' THEN 'View'
END AS PrimaryObjectType
,'Column' AS SecondaryObjectType
,SCH.name AS SchemaName
,OBJ.name AS PrimaryObjectName
,COL.name AS SecondaryObjectName
,SEP.name AS Classification
,CAST(SEP.value AS NVARCHAR(1700)) AS DescriptionText
FROM sys.objects OBJ
INNER JOIN sys.columns COL
ON OBJ.object_id = COL.object_id
INNER JOIN sys.extended_properties SEP
ON COL.object_id = SEP.major_id
AND COL.column_id = SEP.minor_id
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN #SchemaList tmpSCH
ON tmpSCH.SchemaName = SCH.name
INNER JOIN #TableViewList tmpTbv
ON tmpTbv.TableViewName = OBJ.name
WHERE OBJ.type_desc = N'USER_TABLE'
OR OBJ.type_desc = N'VIEW'
-----------------------------------------------------------------------
-- Index objects
-----------------------------------------------------------------------
INSERT INTO #ModifyDrop (PrimaryObjectType, SecondaryObjectType, SchemaName, PrimaryObjectName, SecondaryObjectName, Classification, DescriptionText)
SELECT
CASE
WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table'
WHEN OBJ.type_desc = N'VIEW' THEN 'View'
END AS PrimaryObjectType
,'Index' AS SecondaryObjectType
,SCH.name AS SchemaName
,OBJ.name AS PrimaryObjectName
,SIX.name AS SecondaryObjectName
,SEP.name AS Classification
,CAST(SEP.value AS NVARCHAR(1700)) AS DescriptionText
FROM sys.objects OBJ
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.indexes SIX
ON OBJ.object_id = SIX.object_id
INNER JOIN sys.extended_properties SEP
ON SIX.object_id = SEP.major_id
AND SIX.index_id = SEP.minor_id
INNER JOIN #SchemaList tmpSCH
ON tmpSCH.SchemaName = SCH.name
INNER JOIN #TableViewList tmpTbv
ON tmpTbv.TableViewName = OBJ.name
WHERE SEP.class_desc = N'INDEX'
-----------------------------------------------------------------------
-- Constraints
-----------------------------------------------------------------------
INSERT INTO #ModifyDrop (PrimaryObjectType, SecondaryObjectType, SchemaName, PrimaryObjectName, SecondaryObjectName, Classification, DescriptionText)
SELECT
CASE
WHEN OBJ.type_desc = N'USER_TABLE' THEN 'Table'
WHEN OBJ.type_desc = N'VIEW' THEN 'View'
END AS PrimaryObjectType
,'Constraint' AS SecondaryObjectType
,SCH.name AS SchemaName
,OBJ.name AS PrimaryObjectName
,OBJ_1.name AS SecondaryObjectName
,SEP.name AS Classification
,CAST(SEP.value AS NVARCHAR(1700)) AS DescriptionText
FROM sys.objects OBJ
INNER JOIN sys.objects AS OBJ_1
ON OBJ.object_id = OBJ_1.parent_object_id
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
ON OBJ_1.object_id = SEP.major_id
INNER JOIN #SchemaList tmpSCH
ON tmpSCH.SchemaName = SCH.name
INNER JOIN #TableViewList tmpTbv
ON tmpTbv.TableViewName = OBJ.name
WHERE OBJ_1.type_desc IN ('DEFAULT_CONSTRAINT','FOREIGN_KEY_CONSTRAINT','PRIMARY_KEY_CONSTRAINT','UNIQUE_CONSTRAINT')
-----------------------------------------------------------------------
-- Stored Procedures and Functions
-----------------------------------------------------------------------
INSERT INTO #ModifyDrop (PrimaryObjectType, SecondaryObjectType, SchemaName, PrimaryObjectName, SecondaryObjectName, Classification, DescriptionText)
SELECT
CASE
WHEN OBJ.type_desc = 'SQL_STORED_PROCEDURE' THEN 'Procedure'
WHEN OBJ.type_desc = 'SQL_SCALAR_FUNCTION' THEN 'Function'
END AS PrimaryObjectType
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectType
,SCH.name AS SchemaName
,OBJ.name AS PrimaryObjectName
,CAST(NULL AS VARCHAR(25)) AS SecondaryObjectName
,SEP.name AS Classification
,CAST(SEP.value AS NVARCHAR(1700)) AS DescriptionText
FROM sys.objects OBJ
INNER JOIN sys.schemas SCH
ON OBJ.schema_id = SCH.schema_id
INNER JOIN sys.extended_properties SEP
ON OBJ.object_id = SEP.major_id
INNER JOIN #SchemaList tmpSCH
ON tmpSCH.SchemaName = SCH.name
WHERE OBJ.type_desc IN ('SQL_STORED_PROCEDURE','SQL_SCALAR_FUNCTION')
-----------------------------------------------------------------------
-- Output
-----------------------------------------------------------------------
IF @DropAppend = 'Append'
BEGIN
UPDATE #ModifyDrop
SET SQLText =
CASE
WHEN PrimaryObjectType IN ('Table','View','Procedure','Function') AND SecondaryObjectType IS NULL THEN 'sp_updateextendedproperty @name = N''' + Classification + ''', @level0type = N''Schema'', @level0name = [' + SchemaName + '], @level1type = N''' + PrimaryObjectType + ''', @level1name = [' + PrimaryObjectName + '], @value = ''' + DescriptionText + ''';'
WHEN SecondaryObjectType IN ('Column','Index','ForeignKey','Default', 'PrimaryKey', 'UniqueConstraint') THEN 'sp_updateextendedproperty @name = N''' + Classification + ''', @level0type = N''Schema'', @level0name = [' + SchemaName + '], @level1type = N''' + PrimaryObjectType + ''', @level1name = [' + PrimaryObjectName + '], @level2type = N''' + ISNULL(SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(SecondaryObjectName,'') + '], @value = ''' + DescriptionText + ''';'
END
END
ELSE
BEGIN
UPDATE #ModifyDrop
SET SQLText =
CASE
WHEN PrimaryObjectType IN ('Table','View','Procedure','Function') AND SecondaryObjectType IS NULL THEN 'sp_dropextendedproperty @name = N''' + Classification + ''', @level0type = N''Schema'', @level0name = [' + SchemaName + '], @level1type = N''' + PrimaryObjectType + ''', @level1name = [' + PrimaryObjectName + '];'
WHEN SecondaryObjectType IN ('Column','Index','ForeignKey','Default', 'PrimaryKey', 'UniqueConstraint') THEN 'sp_dropextendedproperty @name = N''' + Classification + ''', @level0type = N''Schema'', @level0name = [' + SchemaName + '], @level1type = N''' + PrimaryObjectType + ''', @level1name = [' + PrimaryObjectName + '], @level2type = N''' + ISNULL(SecondaryObjectType,'') + ''', @level2name = [' + ISNULL(SecondaryObjectName,'') + '];'
END
END
SELECT SQLText FROM #ModifyDrop

Notes:

  • This script will work with SQL Server 2005 and SQL Server 2008 - if you are working with SQL Server 2008 then you could replace the list parsing with a table-valued parameter, and initialise the variables as part of the variable declaration.

The next article will deal with industrial-strength database documentation using Extended Properties - aka how to add hundreds of comments at a time to a range of objects.

Resources:

GetExtendedPropertiesForModification.sql | GetExtendedPropertiesForDelete.sql | DropOrUpdateExistingExtendedProperties.sql
Total article views: 4353 | Views in the last 30 days: 15
 
Related Articles
FORUM

Replacing Schema with new schema of Database objects

Replace "Emp" schema with "Employee" Schema

FORUM
BLOG

IN vs INNER JOIN

Often in forum threads discussing query performance I’ll see people recommending replacing an INNER ...

ARTICLE

Industrial-strength database documentation using Extended Properties

To finish this short series on extended properties a look at documenting sets of database objects

FORUM

Coalesce on inner queries

Coalesce on inner queries

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones