Columns that are not in use
August 2, 2010
Posted by on
I’m working on upgrading a client to our new framework. While doing this, we’re creating new, clean and sexy, data models. Changing column names that are partly Norwegian and partly English into only English.
While I was doing this, I found a table which were heavily used, and had A LOT of fields. 173 to be exact. So, how do I check which columns that are not in use? Fastest way is to check all fields if they’ve got any values at all. This is of course not that accurate, since a field could have been used once, 10 years ago, and never later, but we’ll start with this. Here’s a script to find all fields in one table, that does not have any values:
SET NOCOUNT ON
DECLARE @TableID AS NVARCHAR(128)
SET @TableID = 'your_table'
DECLARE @ColumnName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)
name AS ColumnName
WHERE id = OBJECT_ID(@TableID)
WHILE EXISTS (SELECT * FROM #columns)
SELECT TOP 1
@ColumnName = ColumnName
SET @SQL = 'IF NOT EXISTS (SELECT * '
SET @SQL = @SQL + ' FROM ' + @TableID
SET @SQL = @SQL + ' WHERE ' + @ColumnName + ' IS NOT NULL) '
SET @SQL = @SQL + 'BEGIN '
SET @SQL = @SQL + ' PRINT '''+ @ColumnName + ''''
SET @SQL = @SQL + 'END'
WHERE ColumnName = @ColumnName
DROP TABLE #columns