Drop all extended properties on SQL Server

Question

How to drop all extended properties on SQL Server in a scriptable way?

Allege

please mark one answer as accepted/correct. At least 2 are correct. – Claudy

Answer 1

If you want a script that will drop all extended properties in one go then use a script that Jamie Thomson has created that will generate drops for all extended properties. You can download it from this article.

I've pasted his script here (in full with acknowledgements in-case the article is removed):

/*
This script will generate calls to sp_dropextendedproperty for every
extended property that exists in your database.
Actually, a caveat: I don't promise that it will catch each and every 
extended property that exists, but I'm confident it will catch most of them!

It is based on this: 
http://blog.hongens.nl/2010/02/25/drop-all-extended-properties-in-a-mssql-database/ 
by Angelo Hongens.

Also had lots of help from this:
http://www.sqlservercentral.com/articles/Metadata/72609/
by Adam Aspin

Adam actually provides a script at that link to do something very similar
but when I ran it I got an error:
Msg 468, Level 16, State 9, Line 78
Cannot resolve the collation conflict between "Latin1_General_100_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

So I put together this version instead. 

Use at your own risk.

Jamie Thomson
2012-03-25
*/


/*Are there any extended properties? Let's take a look*/
select  *,OBJECT_NAME(major_id) from    sys.extended_properties xp

/*Now let's generate sp_dropextendedproperty statements for all of them.*/
--tables
set nocount on;
select 'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''table''
,@level1name = ''' + object_name(xp.major_id) + ''''
from sys.extended_properties xp
join sys.tables t on xp.major_id = t.object_id
where xp.class_desc = 'OBJECT_OR_COLUMN'
and xp.minor_id = 0
union
--columns
select 'EXEC sp_dropextendedproperty
@name = '''+sys.extended_properties.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(extended_properties.major_id) + '''
,@level1type = ''table''
,@level1name = ''' + object_name(extended_properties.major_id) + '''
,@level2type = ''column''
,@level2name = ''' + columns.name + ''''
from sys.extended_properties
join sys.columns
on columns.object_id = extended_properties.major_id
and columns.column_id = extended_properties.minor_id
where extended_properties.class_desc = 'OBJECT_OR_COLUMN'
and extended_properties.minor_id > 0
union
--check constraints
select  'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''table''
,@level1name = ''' + object_name(cc.parent_object_id) + '''
,@level2type = ''constraint''
,@level2name = ''' + cc.name + ''''
from    sys.extended_properties xp
join sys.check_constraints cc       on  xp.major_id = cc.object_id
union
--check constraints
select  'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''table''
,@level1name = ''' + object_name(cc.parent_object_id) + '''
,@level2type = ''constraint''
,@level2name = ''' + cc.name + ''''
from    sys.extended_properties xp
join sys.default_constraints cc     on  xp.major_id = cc.object_id
union
--views
select 'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''view''
,@level1name = ''' + object_name(xp.major_id) + ''''
from sys.extended_properties xp
join sys.views t on xp.major_id = t.object_id
where xp.class_desc = 'OBJECT_OR_COLUMN'
and xp.minor_id = 0
union
--sprocs
select 'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''procedure''
,@level1name = ''' + object_name(xp.major_id) + ''''
from sys.extended_properties xp
join sys.procedures t on xp.major_id = t.object_id
where xp.class_desc = 'OBJECT_OR_COLUMN'
and xp.minor_id = 0
union
--FKs
select  'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''table''
,@level1name = ''' + object_name(cc.parent_object_id) + '''
,@level2type = ''constraint''
,@level2name = ''' + cc.name + ''''
from    sys.extended_properties xp
join sys.foreign_keys cc        on  xp.major_id = cc.object_id
union
--PKs
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SKC.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
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'
union
--Table triggers
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''TABLE'', @level1name = [' + TBL.name + '] , @level2type = ''TRIGGER'', @level2name = [' + TRG.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
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
union
--UDF params
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''FUNCTION'', @level1name = [' + OBJ.name + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
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') 
union
--sp params
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''SCHEMA'', @level0name = [' + SCH.name + '], @level1type = ''PROCEDURE'', @level1name = [' + SPR.name + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
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'
union
--DB
SELECT 
'EXEC sys.sp_dropextendedproperty @name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
FROM sys.extended_properties SEP
WHERE class_desc = N'DATABASE'
union
--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'
union
--DATABASE_FILE
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + DSP.name + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + DBF.name + ' ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
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'
union
--filegroup
SELECT 
'EXEC sys.sp_dropextendedproperty @level0type = N''FILEGROUP'', @level0name = [' + DSP.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),'''','''''') + ''''
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'
union
-- INDEX
select 'EXEC sp_dropextendedproperty
@name = '''+sys.extended_properties.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(extended_properties.major_id) + '''
,@level1type = ''table''
,@level1name = ''' + object_name(extended_properties.major_id) + '''
,@level2type = ''index''
,@level2name = ''' + indexes.name + ''''
from sys.extended_properties
join sys.indexes
on indexes.object_id = extended_properties.major_id
and indexes.index_id = extended_properties.minor_id
where extended_properties.class_desc = 'INDEX'
and extended_properties.minor_id > 0

Fieldfare

I got this to work eventually but had to a) delete database diagrams from the database, and b) the script assumes all columns properties are on tables, I had some on views to had to modify the script. – Hypochondriac

it works, but only one note, it will try to remove extended properties of some system sp, so check some commands at the beginning, then run the script. – Hort

like Dave and had problems with column on view and functions. I've posted modified script – Vashtee

Answer 2

use this script

EXEC sys.sp_dropextendedproperty 
     @name  =N'MS_Description', 
     @level0type = N'SCHEMA', 
     @level0name = N'dbo',
     @level1type = N'TABLE',
     @level1name = N'ActivityEventRecipient';
GO 

Source:drop extended property

Dominicadominical

Answer 3

I tried running the scripts and nothing seemed to work for me. I did however find that I could delete the extended properties by going to my tables/views in Object Explorer and doing to following:

• Right click on Table/View in Object Explorer, select Properties
• Select Extended Properties from the pane on the left
• Select the extended properties and click Delete at the bottom right of the window

Georgiageorgian

Answer 4

Please see the Below URL to drop the extended property:

http://msdn.microsoft.com/en-us/library/ms178595.aspx

Shawanda

Answer 5

Can't add a comment, I only can add this as answer.

The script is wrong for columns of views. The part for columns should be changed to:

--columns
select 'EXEC sp_dropextendedproperty
@name = '''+sys.extended_properties.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(extended_properties.major_id) + '''
,@level1type = ''' + Case so.type
                         When 'u'
                             Then
                             'table'
                         When 'v'
                             Then
                             'view'
                     End + '''
,@level1name = ''' + object_name(extended_properties.major_id) + '''
,@level2type = ''column''
,@level2name = ''' + columns.name + ''''
from sys.extended_properties
join sys.columns
on columns.object_id = extended_properties.major_id
and columns.column_id = extended_properties.minor_id
    Join
        sys.objects As so
            On
            columns.object_id     = so.object_id
Where
    extended_properties.class_desc   = 'OBJECT_OR_COLUMN'
    And extended_properties.minor_id > 0
    And so.type In
        ( 'u', 'v' )

Kapok

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review – Rollin

Answer 6

You need to add this block for functions
UNION
-- functions
select 'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''function''
,@level1name = ''' + object_name(xp.major_id) + ''''
from sys.extended_properties xp
join sys.objects t ON xp.major_id = t.object_id AND t.type IN ('FN', 'IF', 'TF')  
where xp.class_desc = 'OBJECT_OR_COLUMN'
and xp.minor_id = 0;
UNION
-- for synonyms
select 'EXEC sp_dropextendedproperty
@name = '''+xp.name+'''
,@level0type = ''schema''
,@level0name = ''' + object_schema_name(xp.major_id) + '''
,@level1type = ''synonym''
,@level1name = ''' + object_name(xp.major_id) + ''''
from sys.extended_properties xp
join sys.synonyms t ON xp.major_id = t.object_id
where xp.class_desc = 'OBJECT_OR_COLUMN'
and xp.minor_id = 0

Painty

Answer 7

This script similar to other answers, but will allows you to inspect and filter extended properties.

For example I wanted to leave Description properties Just add a where clause at the end: WHERE Name not like '%desc%'

/*
It is based on this: 
http://sqlblog.com/blogs/jamie_thomson/archive/2012/03/25/generate-drop-statements-for-all-extended-properties.aspx

http://www.sqlservercentral.com/articles/Metadata/72609/
by Adam Aspin

/*Are there any extended properties? Let's take a look*/
--select  *,OBJECT_NAME(major_id) from    sys.extended_properties xp

SET NOCOUNT ON;

SELECT props.*, DropStatement = 'EXEC sp_dropextendedproperty @name = ''' + name + ''',@level0type = ''' + level0type + ''',@level0name =''' + level0name + ''',@level1type = ''' + level1type + ''',@level1name = ''' + level1name + '''' + CASE 
        WHEN level2type IS NULL
            THEN ''
        ELSE ',@level2type = ''' + level2type + ''',@level2name = ''' + level2name + ''''
        END
FROM (
    --tables
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'table', level1name = object_name(xp.major_id), level2Type = cast(NULL AS VARCHAR(255)), level2Name = cast(NULL AS VARCHAR(255)), xp.value
    FROM sys.extended_properties xp
    JOIN sys.tables t ON xp.major_id = t.object_id
    WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
        AND xp.minor_id = 0

    UNION
    
    --views
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'view', level1name = object_name(xp.major_id), level2Type = NULL, level2Name = NULL, xp.value
    FROM sys.extended_properties xp
    JOIN sys.VIEWS t ON xp.major_id = t.object_id
    WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
        AND xp.minor_id = 0 

    UNION
    
    --columns views and tables, inline table valued functions
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id)
        , level1type = CASE 
                        WHEN obj.type = 'IF' THEN 'function'
                        WHEN obj.type = 'V' THEN 'view'
                        ELSE 'table' END 
        , level1name = object_name(xp.major_id), level2type = 'column', level2name = columns.name, xp.value
    FROM sys.extended_properties xp
    JOIN sys.columns ON columns.object_id = xp.major_id
        AND columns.column_id = xp.minor_id
    JOIN sys.objects obj ON xp.major_id = obj.object_id
    WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
        AND xp.minor_id > 0
    
    UNION
    
    --check constraints
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'table', level1name = object_name(cc.parent_object_id), level2type = 'constraint', level2name = cc.name, xp.value
    FROM sys.extended_properties xp
    JOIN sys.check_constraints cc ON xp.major_id = cc.object_id
    
    UNION
    
    --check default constraints
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'table', level1name = object_name(cc.parent_object_id), level2type = 'constraint', level2name = cc.name, xp.value
    FROM sys.extended_properties xp
    JOIN sys.default_constraints cc ON xp.major_id = cc.object_id
    

    
    UNION
    
    --sprocs
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'procedure', level1name = object_name(xp.major_id), level2Type = NULL, level2Name = NULL, xp.value
    FROM sys.extended_properties xp
    JOIN sys.procedures t ON xp.major_id = t.object_id
    WHERE xp.class_desc = 'OBJECT_OR_COLUMN'
        AND xp.minor_id = 0
    
    UNION
    
    --FKs
    SELECT xp.Name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'table', level1name = object_name(cc.parent_object_id), level2type = 'constraint', level2name = cc.name, xp.value
    FROM sys.extended_properties xp
    JOIN sys.foreign_keys cc ON xp.major_id = cc.object_id
    
    UNION
    
    --PKs
    SELECT Name = REPLACE(CAST(xp.name AS NVARCHAR(300)), '''', ''''''), level0type = 'schema', level0name = SCH.name, level1type = 'table', level1name = '[' + TBL.name + ']', level2type = 'constraint', level2name = SKC.name, xp.value
    FROM sys.tables TBL
    INNER JOIN sys.schemas SCH ON TBL.schema_id = SCH.schema_id
    INNER JOIN sys.extended_properties xp
    INNER JOIN sys.key_constraints SKC ON xp.major_id = SKC.object_id ON TBL.object_id = SKC.parent_object_id WHERE SKC.type_desc = N'PRIMARY_KEY_CONSTRAINT'
    
    UNION
    
    --Table triggers
    SELECT Name = REPLACE(CAST(xp.name AS NVARCHAR(300)), '''', ''''''), level0type = 'schema', level0name = SCH.name, level1type = 'table', level1name = '[' + TBL.name + ']', level2type = 'TRIGGER', level2name = TRG.name, xp.value
    FROM sys.tables TBL
    INNER JOIN sys.triggers TRG ON TBL.object_id = TRG.parent_id
    INNER JOIN sys.extended_properties xp ON TRG.object_id = xp.major_id
    INNER JOIN sys.schemas SCH ON TBL.schema_id = SCH.schema_id
    
    UNION
    
    --UDF params
    SELECT Name = REPLACE(CAST(xp.name AS NVARCHAR(300)), '''', ''''''), level0type = 'schema', level0name = SCH.name, level1type = 'FUNCTION', level1name = '[' + OBJ.name + ']', level2type = 'PARAMETER', level2name = PRM.name, xp.value
    FROM sys.extended_properties xp
    INNER JOIN sys.objects OBJ ON xp.major_id = OBJ.object_id
    INNER JOIN sys.schemas SCH ON OBJ.schema_id = SCH.schema_id
    INNER JOIN sys.parameters PRM ON xp.major_id = PRM.object_id
        AND xp.minor_id = PRM.parameter_id
    WHERE xp.class_desc = N'PARAMETER'
        AND OBJ.type IN ('FN','IF','TF')
    
    UNION
    
    --sp params
    SELECT Name = REPLACE(CAST(xp.name AS NVARCHAR(300)), '''', ''''''), level0type = 'schema', level0name = SCH.name, level1type = 'PROCEDURE', level1name = '[' + SPR.name + ']', level2type = 'PARAMETER', level2name = PRM.name, xp.value
    FROM sys.extended_properties xp
    INNER JOIN sys.procedures SPR ON xp.major_id = SPR.object_id
    INNER JOIN sys.schemas SCH ON SPR.schema_id = SCH.schema_id
    INNER JOIN sys.parameters PRM ON xp.major_id = PRM.object_id
        AND xp.minor_id = PRM.parameter_id
    WHERE xp.class_desc = N'PARAMETER'
    
    UNION
    
    -- INDEX
    SELECT Name = xp.name, level0type = 'schema', level0name = object_schema_name(xp.major_id), level1type = 'table', level1name = object_name(xp.major_id), level2type = 'index', level2name = indexes.name, xp.value
    FROM sys.extended_properties xp
    JOIN sys.indexes ON indexes.object_id = xp.major_id
        AND indexes.index_id = xp.minor_id
    WHERE xp.class_desc = 'INDEX'
        AND xp.minor_id > 0
    ) AS props

where
level2Name not like 'sp_%'  and level2Name not like 'sys%' --ignore system procedures

Vashtee

@GertArnold: instead of text based output you get nice table that is easy to filter out. I took me quite a while to edit the script so that it would not delete some extended properties that I wanted to keep – Vashtee