Udostępnij za pośrednictwem

Delete Table Based Extended Properties on a SQL Database

  • Artykuł

There’s probably a much better way to do this, but here is a snippet of TSQL that will delete all of the table 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 @prop_name  NVARCHAR(256)
DECLARE @table_name NVARCHAR(256)

DECLARE db_cursor CURSOR FOR  
    SELECT 
        ext.name, 
        tb.name
    FROM 
        sys.extended_properties ext,
        sys.tables tb
    WHERE 
        ext.major_id = tb.object_id 

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @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

    FETCH NEXT FROM db_cursor INTO @prop_name, @table_name
END   

CLOSE db_cursor   
DEALLOCATE db_cursor