SQL Server Hosting – HostForLIFE.eu :: Auto Generate Script To Delete Deprecated Fields In Current Database

Peter Cole | June 14, 2017 | SQL 2016 Hosting | No Comments

This topic describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2016.
In computer programming, a deprecated language entity is one that is tolerated or supported but not recommended. For example, a number of elements and attributes are deprecated in HTML 4.0 , meaning that other means of accomplishing the task are preferred. Many deprecated features became obsolete in HTML5, although browsers that support the features may continue to support them. In the Java programming language, a particular method may be deprecated for a given class of objects.

I always mark fields to be deprecated with “dep_” as prefix. In this way, after few days, when I am sure that I do not need the field any more I run the query to auto generate the deprecation script. The script also checks for any constraint in the system and auto generate the script to drop it also.

   SELECT 'ALTER TABLE ['+po.name+'] DROP CONSTRAINT [' + so.name + ']'  
    FROM sysobjects so  
    INNER JOIN sysconstraints sc ON so.id = sc.constid  
    INNER JOIN syscolumns col ON sc.colid = col.colid  
    AND so.parent_obj = col.id AND col.name LIKE 'dep[_]%'  
    INNER JOIN sysobjects po ON so.parent_obj = po.id  
    WHERE so.xtype = 'D'  
    ORDER BY po.name, col.name   

    SELECT 'ALTER TABLE ['+table_schema+'].['+Table_name+'] DROP COLUMN [' + Column_name + ']'  
    FROM INFORMATION_SCHEMA.COLUMNS  
    WHERE column_name LIKE 'dep[_]%'  
    ORDER BY Table_name, Column_name

SELECT 'ALTER TABLE ['+po.name+'] DROP CONSTRAINT [' + so.name + ']'

FROM sysobjects so

INNER JOIN sysconstraints sc ON so.id = sc.constid

INNER JOIN syscolumns col ON sc.colid = col.colid

AND so.parent_obj = col.id AND col.name LIKE 'dep[_]%'

INNER JOIN sysobjects po ON so.parent_obj = po.id

WHERE so.xtype = 'D'

ORDER BY po.name, col.name

SELECT 'ALTER TABLE ['+table_schema+'].['+Table_name+'] DROP COLUMN [' + Column_name + ']'

FROM INFORMATION_SCHEMA.COLUMNS

WHERE column_name LIKE 'dep[_]%'

ORDER BY Table_name, Column_name

Related Posts

About The Author

Pete