What columns are used? Quick analysis of legacy SQL table

Got a table with lots of columns? Use this SQL to identify which have values.

My table name is manTransaction and I’m only interested in those of type = 2

declare @tn nvarchar(200)
declare @whereclause nvarchar(1024)
set @tn='manTransactionDetail' --table
set @whereclause = 'TransactionID in (select TransactionID from manTransaction where TransactionTypeID=8)' --particular records
declare @col nvarchar(200)
declare @typ int
declare @sql0 nvarchar(2048)
declare @sql nvarchar(2048)

set @sql0 = 'with a(v) as (select __COLNAME__ from __TABLENAME__ where __WHERECLAUSE__ )
 , d(v) as (select v from a group by v)
 insert into #ColumnStatus(ColName, DistinctValues) select ''__COLNAME__'', COUNT(*) from d'
set @sql0 = REPLACE(@sql0, '__TABLENAME__',@tn)
set @sql0 = REPLACE(@sql0, '__WHERECLAUSE__',@whereclause)

if OBJECT_ID('tempdb..#ColumnStatus') is not null drop table #ColumnStatus
create table #ColumnStatus(ColName nvarchar(200), DistinctValues int)
declare c cursor for
select name, system_type_id from sys.columns where object_id=object_id(@tn)
open c
fetch next from c into @col,@typ
set @sql = REPLACE (@sql0, '__COLNAME__', @col)
print @sql
exec(@sql) 
while @@FETCH_STATUS=0
begin
 fetch next from c into @col,@typ
 set @sql = REPLACE (@sql0, '__COLNAME__', @col)
 exec(@sql) 
end
close c
deallocate c
select ColName + ',' from #ColumnStatus where DistinctValues > 1

--select * from #ColumnStatus





 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

Website by Daneswood