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