Find unused columns

,

This script will find all columns in a table that have only 1 value.  If a column only has 1 value for all rows it probably isn't being used and is causing technical debt.  Consider removing unneeded columns.

SET ANSI_WARNINGS OFF
GO

DECLARE @tbl VARCHAR(100), @col VARCHAR(100), @cmd VARCHAR(MAX)

SET @tbl = 'TableName'

DECLARE curSingleValue CURSOR FOR

SELECT b.name
	FROM sys.tables a
		JOIN sys.columns b ON a.object_id = b.object_id
	WHERE a.Name = @tbl
	ORDER BY b.name

OPEN curSingleValue
FETCH NEXT FROM curSingleValue INTO @col
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @cmd = 'IF (SELECT COUNT(DISTINCT [' + @col + ']) FROM [' + @tbl + ']) = 1 
		OR ISNULL((SELECT COUNT(DISTINCT [' + @col + ']) FROM [' + @tbl + ']), 1) = 0 
		BEGIN print ''' + @col + ''' end'
	--PRINT @cmd
    EXEC(@cmd)

    FETCH NEXT FROM curSingleValue INTO @col
END
CLOSE curSingleValue
DEALLOCATE curSingleValue

SET ANSI_WARNINGS ON
GO

Rate

3.5 (4)

Share

Share

Rate

3.5 (4)