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)
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.
[...] Auto Generate Script to Delete Deprecated Fields in Current Database In early career everytime I have to drop a column, I had hard time doing it because I was scared what if that column was needed somewhere in the code. Due to this fear I never dropped any column. I just renamed the column. If the column which I renamed was needed afterwards it was very easy to rename it back again. However, it is not recommended to keep the deleted column renamed in the database. At every interval I used to drop the columns which was prefixed with specific word. This script is 6 years old but still works. Give it a look, I am open for improvements. [...]
Old is Gold,
I like your old script
Thanks