SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Obsolete Table Columns
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

TimS
Posting Yak Master

USA
198 Posts

Posted - 02/04/2005 :  10:10:22  Show Profile  Reply with Quote
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

Sweden
3279 Posts

Posted - 02/04/2005 :  17:41:55  Show Profile  Reply with Quote
Umh, Why not remove the column ?

rockmoose
Go to Top of Page

TimS
Posting Yak Master

USA
198 Posts

Posted - 02/04/2005 :  22:51:01  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000