When I find that a column should no longer be used because of design changes. ( Moved column to other table to correct design normalization etc.)
I added a check to make sure the column must be NULL.
The following list the obsolete columns
SELECT col.TABLE_NAME + '.' + col.COLUMN_NAME AS FULL_COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS col JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON col.COLUMN_NAME = ccu.COLUMN_NAME AND col.TABLE_SCHEMA = ccu.TABLE_SCHEMA AND col.TABLE_NAME = ccu.TABLE_NAME JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON ccu.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND ccu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME WHERE IS_NULLABLE = 'YES' AND cc.CHECK_CLAUSE = '([' + col.COLUMN_NAME + '] IS NULL)' ORDER BY col.TABLE_NAME, col.COLUMN_NAME
In the past columns could not be set to null. This script list those columns that I need to add null checks to.
SELECT col.TABLE_NAME + '.' + col.COLUMN_NAME AS FULL_COLUMN_NAME, cc.CHECK_CLAUSE FROM INFORMATION_SCHEMA.COLUMNS col JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON col.COLUMN_NAME = ccu.COLUMN_NAME AND col.TABLE_SCHEMA = ccu.TABLE_SCHEMA AND col.TABLE_NAME = ccu.TABLE_NAME JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON ccu.CONSTRAINT_SCHEMA = cc.CONSTRAINT_SCHEMA AND ccu.CONSTRAINT_NAME = cc.CONSTRAINT_NAME WHERE cc.CHECK_CLAUSE LIKE '([[]' + col.COLUMN_NAME + '] =%' AND NOT cc.CHECK_CLAUSE LIKE '% OR %' AND NOT cc.CHECK_CLAUSE LIKE '% AND %' AND NOT cc.CHECK_CLAUSE LIKE '% IS %' AND NOT cc.CHECK_CLAUSE LIKE '% IN %' ORDER BY col.TABLE_NAME, col.COLUMN_NAME
Obsolete Table Columns
Posted - 02/04/2005 : 10:10:22
