Hi,
Welcome to my first post.
I was asked today, to display all columns in all tables in a database that had no data in them. The reason behind this was to identify unused data fields.
Showing empty tables is something I have done many times, but this was different.
Being a fan of not reinventing the wheel, I had a look to see if there was any thing out there. I came across this:-
http://www.sqlservercentral.com/articles/Advanced+Querying/exoticsqlnullcolumnanalysis/1442/
This was almost it. However this system did not work if you had schema names. (and we all should, right)
So after a small amount of editing. Here we have (with credit to Leo Peysakhovich for the orgional idea, an edited option:-
drop table tempdb..tmp_count
Create table tempdb..tmp_count
([schema] varchar(500), dbnm varchar(500), tbl varchar(255), col varchar(255), nulls int, tot int)
go
BEGIN
declare @sql Nvarchar(1000), @minid int, @maxid int,
@schema_n varchar(255) , @table_n varchar(255), @col varchar(255), @dbnm varchar(500)
declare @tmp1 table (id
int identity(1,1), tbl varchar(255), col varchar(255),[schema] varchar(500) , [database] varchar(500))
set @dbnm = db_name()
truncate table tempdb..tmp_count
insert into @tmp1(tbl,col,[schema], [database])
select TABLE_NAME, COLUMN_NAME, TABLE_SCHEMA , TABLE_CATALOG from information_schema.columns
where data_type not in ('image','text','timestamp','binary','uniqueidentifier')
and IS_NULLABLE = 'yes'
/*select so.name, sc.name from syscolumns sc
inner
join sysobjects so on so.id = sc.id
where so.name not like 'ERR_%' and so.type = 'U' and
*/
select @minid = 1, @maxid = max(id) from @tmp1
while (@minid <=@maxid)
begin
select @schema_n = [schema], @table_n = tbl,@col = col from @tmp1
where id = @minid
select @sql = ' insert into tempdb..tmp_count(dbnm, tbl, col , nulls , tot)'
select @sql = @sql + ' select ''' + @dbnm + ''', ''' + @table_n + ''', ''' +
@col + ''', (select count(*) from [' + @schema_n +'].[' +@table_n + '] where ['+ @col + '] is null) '
select @sql = @sql + ' , (select count(*) from [' + @schema_n +'].[' +@table_n + '])'
--select @sql
exec ( @sql )
set @minid = @minid + 1
end
END
select left(dbnm,10) as dbnm, left(tbl,20) as tbl,
left(col,20) as col,tot, nulls, tot - nulls as NotNulls
from tempdb..tmp_count
where tot - nulls =0
order by tot
