SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database

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

Reference: Pinal Dave (http://www.SQLAuthority.com)

About these ads

6 thoughts on “SQL SERVER – Auto Generate Script to Delete Deprecated Fields in Current Database

  1. HI,
    I d’t understand wht u wann say using the above querry,?? Can u please explain me wht mean by Depreciated fields and when and wh to use the above querry…???

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #004 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s