Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
TimS
Posting Yak Master
198 Posts |
 Posted - 2005-02-04 : 10:10:22
|
| 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 Tim S |
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2005-02-04 : 17:41:55
|
| Umh, Why not remove the column ?rockmoose |
 |
|
|
TimS
Posting Yak Master
198 Posts |
Posted - 2005-02-04 : 22:51:01
|
| Because I don't like to drop columns or drop & re-create tables.I have had problems using link servers on tables that had a column dropped in them.Tim S |
|
|
|
|
|
|
Obsolete Table Columns