Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Remove all Extended Properties in a database

During my tests to port several databases to SQL Azure, one of the recurring things that fails export is the Extended Properties. So I just wanted to remove them.
This is a simple wayh to list all Extended Properties and the corresponding delete statement.


SELECT 'EXEC sp_dropextendedproperty @name = ' + QUOTENAME(ep.name, '''') + ', @level0type = ''schema'', @level0name = ''dbo''' + ', @level1type = ''table'', @level1name = ' + QUOTENAME(OBJECT_NAME(c.[object_id]), '''') + ', @level2type = ''column'', @level2name = ' + QUOTENAME(c.name, '''') + ';' FROM sys.extended_properties AS ep INNER JOIN sys.columns AS c ON c.[object_id] = ep.major_id AND c.column_id = ep.minor_id

 

Print | posted on Sunday, May 27, 2012 10:02 PM | Filed Under [ Denali ]

Feedback

Gravatar

# re: Remove all Extended Properties in a database

Man that statement is the KING Daddy of them all. I am doing the same thing (going to azure) and killing all of those extended props was going to be a beast.

Thanks a million.
6/25/2012 10:54 PM | Terrence
Gravatar

# Mr

Wow, awesome blog structure! How lengthy have you ever been blogging for? you make running a blog look easy. The entire glance of your web site is wonderful, neatly as} the content!
8/8/2012 10:10 AM | homeopathy for trigeminal neural
Gravatar

# re: Remove all Extended Properties in a database

Your script is so simple and efficient. I am building out the extended properties and needed a way to delete all ext. props and begin anew. Thank you very much and I will continue reading your blog!
8/8/2012 10:13 PM | Heather Morgan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET