find_nonconstant_columns in Database Tables

If your database is designed by Lord Voldemort, I guess that its tables are highly denormalized, worse they contain a lot of redundant columns.

Here I present a power-muggle tool to get rid of all columns containing the same value or calling it the other way, to keep the columns with different values.

Beeing a muggle, here I have a very different opinion about the equality of null values than the ANSI-Wizzards, who are proud of making simple syntax weired.

set nocount on

if OBJECT_ID ('find_nonconstant_columns', 'P' ) > 0 drop procedure find_nonconstant_columns
go

create procedure dbo.find_nonconstant_columns( @tableName sysname)
as
begin

declare @res table (colname sysname, count int, has_nulls int)
Declare @typname varchar(30)   
Declare @name varchar(60)
Declare @value varchar (100)
declare @cmd varchar(max)
declare @result varchar(max)

-- select name from systypes

Declare c CURSOR FOR
    select c.name, t.name
    from syscolumns c
        join sysobjects o on c.id = o.id
        join systypes t on c.xtype = t.xtype
        where o.name = @TableName
OPEN c
FETCH NEXT From c into @name, @typname
WHILE @@FETCH_STATUS = 0
begin
    -- select name from systypes -- I guess I must cast a few more types
    set @value = case when @typname in ('text') then 'cast( ' + @name + ' as varchar(max) )' else  @name  end
    -- this statement is dedicated to all ansi fools who enforce Bad Influenced syntax
    set @cmd = 'select ''' + @name +''',  (select count(distinct '+@value+') from '+@TableName
        +' where '+@name+' is not null), (Select max (case when ' + @name + ' is null then 1 else 0 end) from '+@TableName+')'
    --print @cmd
    insert into @res  exec( @cmd )
    FETCH NEXT From c into @name, @typname
end
CLOSE c
DEALLOCATE c

select * from @res

Declare r cursor for
    Select colname from @res where (count + has_nulls) > 1
OPEN r
FETCH NEXT From r into @name
WHILE @@FETCH_STATUS = 0
begin
    set @result = case when @result is null then @name else @result + ', ' + @name end
    FETCH NEXT From r into @name
end
close r
deallocate r

print @result


end