THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Generate DROP statements for all extended properties

This evening I have been attempting to migrate an existing on-premise database to SQL Azure using the wizard that is built-in to SQL Server Management Studio (SSMS). When I did so I received the following error:

The following objects are not supported =
[MS_Description] = Extended Property

Evidently databases containing extended properties can not be migrated using this particular wizard so I set about removing all of the extended properties – unfortunately there were over a thousand of them so I needed a better way than simply deleting each and every one of them manually. I found a couple of resources online that went some way toward this:

Unfortunately neither provided a script that exactly suited my needs. Angelo’s covered extended properties on tables and columns however I had other objects that had extended properties on them. Adam’s looked more complete 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, both great resources but I wasn’t able to use either on their own to get rid of all of my extended properties. Hence, I combined the excellent work that Angelo and Adam had provided in order to manufacture my own script which did successfully manage to generate calls to sp_dropextendedproperty for all of my extended properties. If you think you might be able to make use of such a script then feel free to download it from https://skydrive.live.com/redir.aspx?cid=550f681dad532637&resid=550F681DAD532637!16707&parid=550F681DAD532637!16706&authkey=!APxPIQCatzC7BQ8. This script will remove extended properties on tables, columns, check constraints, default constraints, views, sprocs, foreign keys, primary keys, table triggers, UDF parameters, sproc parameters, databases, schemas, database files and filegroups. If you have any object types with extended properties on them that are not in that list then consult Adam’s aforementioned article – it should prove very useful.

I repeat here the message that I have placed at the top of the script:

/*
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
*/

Hope this is useful to someone!

@Jamiet

Published Sunday, March 25, 2012 11:35 PM by jamiet

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

M A Srinivas said:

In Scripting Option if you have selected , Script extended Proprties to false . it will not script extended properties

March 26, 2012 4:15 AM
 

jamiet said:

Hello M A,

I never said that I was attempting to generate any scripts - I was using the "Deploy to SQL Azure" wizard. That wizard does not attempt to generate scripts for you and there is no such option in the wizard to turn off Extended Events.

I believe what the wizard actually does is generate a .bacpac file (http://blogs.msdn.com/b/sqlazure/archive/2011/03/24/10145578.aspx)

Thanks for the comment.

JT

March 26, 2012 4:37 AM
 

Ben Thul said:

I tried to do this via powershell and SMO and discovered a bug in SMO! http://www.spartansql.com/2012/03/do-you-have-extended-properties-dont.html

March 27, 2012 12:47 PM
 

Brucevt said:

Works great, many thanks!!!

November 20, 2012 12:47 PM
 

Brian Shurley said:

As always, thank you for the good work you do and SHARE!

April 11, 2013 12:17 PM
 

It's me said:

I am trying to figure out the properties of a drop do you guys know it ?

September 27, 2013 12:26 PM
 

It's me said:

I am trying to figure out the properties of a drop do you guys know it ?

September 27, 2013 12:26 PM
 

Antoine Peterson said:

Nice work! Thanks

I can confirm it doesen't capture all. A separate query needs to be added for UDF.

Am also not getting the Index EP's.

November 12, 2013 9:08 PM
 

Antoine Peterson said:

Updated for my use with addition of exist check and trapping of index + functions.

--***************************************************************

/*

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

*/

/* Added capture for Index and functions ep. Also Functionality added to Adam's script in order to verify the given property exists prior to deletion along with creation scripts

Antoine Peterson

13/11/2013*/

/*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  sep.Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

END

'

FROM    sys.extended_properties sep

       JOIN sys.tables t ON sep.major_id = t.object_id

WHERE   sep.class_desc = 'OBJECT_OR_COLUMN'

       AND sep.minor_id = 0

UNION

--columns

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''

,@level2type = ''column''

,@level2name = ''' + columns.name + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''

,@level2type = ''column''

,@level2name = ''' + columns.name + '''' + '

END

'

FROM    sys.extended_properties AS sep

       JOIN sys.columns ON columns.object_id = sep.major_id

                           AND columns.column_id = sep.minor_id

WHERE   sep.class_desc = 'OBJECT_OR_COLUMN'

       AND sep.minor_id > 0

UNION

-- Indexes

SELECT        sep.major_id, sep.minor_id, sep.name, sep.value AS Value,

                        'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  ' + CAST(sep.major_id AS NVARCHAR(MAX))

                        + '  AND [name] = ''' + sep.name + ''' AND [minor_id] =  ' + CAST(sep.minor_id AS NVARCHAR(MAX))

                        + '  ) ' + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(sep.value AS NVARCHAR(MAX))

                        + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id)

                        + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id)

                        + '''

,@level2type = ''index''

,@level2name = ''' + sys.indexes.name + '''' + '

End ' AS CreateScript,

                        'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  ' + CAST(sep.major_id AS NVARCHAR(MAX))

                        + '  AND [name] = ''' + sep.name + ''' AND [minor_id] =  ' + CAST(sep.minor_id AS NVARCHAR(MAX))

                        + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id)

                         + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(sep.major_id)

                        + '''

,@level2type = ''index''

,@level2name = ''' + sys.indexes.name + '''' + '

END

' AS DropScript

FROM            sys.extended_properties AS sep INNER JOIN

                        sys.indexes ON sys.indexes.object_id = sep.major_id AND sys.indexes.index_id = sep.minor_id

WHERE        (sep.class_desc = 'OBJECT_OR_COLUMN') AND (sep.minor_id > 0)

union

--check constraints

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + ''''

FROM    sys.extended_properties sep

       JOIN sys.check_constraints cc ON sep.major_id = cc.object_id

UNION

--check constraints

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + ''''

FROM    sys.extended_properties sep

       JOIN sys.default_constraints cc ON sep.major_id = cc.object_id

UNION

--views

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''view''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''view''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

END

'

FROM    sys.extended_properties sep

       JOIN sys.views t ON sep.major_id = t.object_id

WHERE   sep.class_desc = 'OBJECT_OR_COLUMN'

       AND sep.minor_id = 0

UNION

--sprocs

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''procedure''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''procedure''

,@level1name = ''' + OBJECT_NAME(sep.major_id) + '''' + '

END

' + '

END

'

FROM    sys.extended_properties sep

       JOIN sys.procedures t ON sep.major_id = t.object_id

WHERE   sep.class_desc = 'OBJECT_OR_COLUMN'

       AND sep.minor_id = 0

UNION

--FKs

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty

@name = ''' + sep.name + '''

,@level0type = ''schema''

,@level0name = ''' + OBJECT_SCHEMA_NAME(sep.major_id) + '''

,@level1type = ''table''

,@level1name = ''' + OBJECT_NAME(cc.parent_object_id) + '''

,@level2type = ''constraint''

,@level2name = ''' + cc.name + ''''

FROM    sys.extended_properties sep

       JOIN sys.foreign_keys cc ON sep.major_id = cc.object_id

UNION

--PKs

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''SCHEMA'', @level0name = [' + SCH.name

       + '], @level1type = ''TABLE'', @level1name = [' + TBL.name

       + '] , @level2type = ''CONSTRAINT'', @level2name = [' + SKC.name

       + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

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  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''SCHEMA'', @level0name = [' + SCH.name

       + '], @level1type = ''TABLE'', @level1name = [' + TBL.name

       + '] , @level2type = ''TRIGGER'', @level2name = [' + TRG.name

       + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

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

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''SCHEMA'', @level0name = [' + SCH.name

       + '], @level1type = ''FUNCTION'', @level1name = [' + OBJ.name

       + '] , @name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty  @level0type = N''SCHEMA'', @level0name = ['

       + SCH.name + '], @level1type = ''FUNCTION'', @level1name = ['

       + OBJ.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),

                                              '''', '''''') + '''' + '

END

'

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   SEP.class_desc = N'OBJECT_OR_COLUMN'

       AND OBJ.type IN ( 'FN', 'IF', 'TF' )

UNION

--UDF params

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''SCHEMA'', @level0name = [' + SCH.name

       + '], @level1type = ''FUNCTION'', @level1name = [' + OBJ.name

       + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name

       + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

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)),

                                              '''', '''''') + '''' + '

END

'

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  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '], @level0type = N''SCHEMA'', @level0name = [' + SCH.name

       + '],  @level1type = ''PROCEDURE'', @level1name = [' + SPR.name

       + '] , @level2type = ''PARAMETER'', @level2name = [' + PRM.name

       + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

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)),

                                              '''', '''''') + '''' + '

END

'

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  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                  '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty  @name = '''

       + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''', '''''') + '''' + '

END

'

FROM    sys.extended_properties SEP

WHERE   class_desc = N'DATABASE'

UNION

--schema

SELECT  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''SCHEMA'', @level0name = [' + SCH.name

       + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  )

Begin

EXEC sys.sp_dropextendedproperty  @level0type = N''SCHEMA'', @level0name = ['

       + SCH.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),

                                              '''', '''''') + '''' + '

END

'

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  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''FILEGROUP'', @level0name = [' + DSP.name

       + '], @level1type = ''LOGICAL FILE NAME'', @level1name = ' + DBF.name

       + ' ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                  '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX))

       + '  )

Begin

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)),

                                             '''', '''''') + '''' + '

END

'

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  Major_Id ,

       Minor_ID ,

sep.Name,

       Value = sep.value ,

       CreateScript = 'IF NOT EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX)) + '  ) '

       + '

Begin

EXEC sys.sp_addextendedproperty @value= [' + CAST(Sep.value AS NVARCHAR(MAX))

       + '],@level0type = N''FILEGROUP'', @level0name = [' + DSP.name

       + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)), '''',

                                   '''''') + '''' + '

End ' ,

       DropScript = 'IF EXISTS

(SELECT NULL FROM SYS.EXTENDED_PROPERTIES WHERE [major_id] =  '

       + CAST(major_Id AS NVARCHAR(MAX)) + '  AND [name] = ''' + sep.Name

       + ''' AND [minor_id] =  ' + CAST(minor_Id AS NVARCHAR(MAX))

       + '  )

Begin

EXEC sys.sp_dropextendedproperty  @level0type = N''FILEGROUP'', @level0name = ['

       + DSP.name + '] ,@name = ''' + REPLACE(CAST(SEP.name AS NVARCHAR(300)),

                                              '''', '''''') + '''' + '

END

'

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'

November 13, 2013 7:00 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement