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. That is how I delete deprecated fields without leaving a constraint behind.
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
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
7 Comments. Leave new
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…???
It means the columns that are no longer needed/used
I agree with prashant panday.
please share some info abt depreciated fields.
Interesting post. I really liked it.
Old is Gold,
I like your old script
Thanks
Really Superb.. thanks for this enhancement..
Great job! Thanks for sharing it!