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! |