Delete Column Based Extended Properties on a SQL Database

  • Article

There’s probably a much better way to do this, but here is a snippet of TSQL that will delete all of the column based Extended Properties on a SQL Server database.  This might be useful for people migrating an on-premise  SQL database to SQL Azure, which does not support extended properties.

 DECLARE @col_name   NVARCHAR(256)
DECLARE @prop_name  NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)


DECLARE db_cursor CURSOR FOR  
    SELECT 
        col.name, 
        ext.name, 
        tb.name
    FROM 
        sys.columns col,
        sys.extended_properties ext,
        sys.tables tb
    WHERE 
        ext.major_id = col.object_id 
        AND tb.object_id = col.object_id
        AND ext.minor_id = col.column_id 
        AND ext.name IS NOT NULL


OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @col_name, @prop_name, @table_name

WHILE @@FETCH_STATUS = 0   
BEGIN
    EXEC sp_dropextendedproperty 
        @name=@prop_name,
        @level0type=N'SCHEMA',
        @level0name=N'dbo', 
        @level1type=N'TABLE',
        @level1name=@table_name,       
        @level2type=N'COLUMN',
        @level2name=@col_name

    FETCH NEXT FROM db_cursor INTO @col_name, @prop_name, @table_name
END   


CLOSE db_cursor   
DEALLOCATE db_cursor