Auto-Generating a Script to Drop Deprecated Columns
We always mark columns to be deprecated with a “dep_” prefix. That way, a few days later, once I’m sure the column is no longer needed, we run this query to automatically generate the deprecation script. The script also checks whether there are any constraints on the column in the system, and automatically generates a script to drop those too.
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 SQL Server Consulting
Need expert support for SQL Server?
Our senior database team supports SQL Server performance tuning, health checks, migrations, Remote DBA services and urgent operational issues.
Related Reading
SQL Server Consulting
Do You Need Expert Support for Your SQL Server Environment?
Aryasoft’s senior database team provides end-to-end consulting, from SQL Server performance tuning to architecture decisions.
