In the two previous articles, you saw how to:

• Add extended properties to a range of database objects.
• Extract extended properties - as selected subsets or in their entirety for a database.

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.