Blog ala Vidar

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)

SELECT
  name AS ColumnName
    INTO #columns
    FROM sys.syscolumns
    WHERE id = OBJECT_ID(@TableID)

WHILE EXISTS (SELECT * FROM #columns)
BEGIN
    SELECT TOP 1
      @ColumnName = ColumnName
        FROM #columns

    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'
    EXEC(@SQL)

    DELETE
        FROM #columns
        WHERE ColumnName = @ColumnName
END

DROP TABLE #columns