Auto Generate Script To Delete Deprecated Fields In Current Database

  • Anu V
  • Anu V
  • Updated date Feb 21 2017
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. 
  1. SELECT 'ALTER TABLE ['+po.name+'] DROP CONSTRAINT [' + so.name + ']'  
  2. FROM sysobjects so  
  3. INNER JOIN sysconstraints sc ON so.id = sc.constid  
  4. INNER JOIN syscolumns col ON sc.colid = col.colid  
  5. AND so.parent_obj = col.id AND col.name LIKE 'dep[_]%'  
  6. INNER JOIN sysobjects po ON so.parent_obj = po.id  
  7. WHERE so.xtype = 'D'  
  8. ORDER BY po.name, col.name   
  1. SELECT 'ALTER TABLE ['+table_schema+'].['+Table_name+'] DROP COLUMN [' + Column_name + ']'  
  2. FROM INFORMATION_SCHEMA.COLUMNS  
  3. WHERE column_name LIKE 'dep[_]%'  
  4. ORDER BY Table_name, Column_name