Peter Larsson Blog

Patron Saint of Lost Yaks

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

 

Legacy Comments


Terrence
2012-06-25
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.

homeopathy for trigeminal neural
2012-08-08
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!

Heather Morgan
2012-08-08
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!