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