--Written By David Gidcumb
    
create TABLE #NumColumnsRowsPerTable(
       ID  INT 
     , Name varchar(100)
     , Columns INT
     , Rows bigINT
     , TotalCellCount bigint
     , NonNullCellsPerTable bigINT
     , NullsPerTable bigint
)


-- Declare the Cursor for getting Row Count
		create TABLE #Columns(
		       Name varchar(100)
		     , NotNullCount bigint
		)


--Number of Columns per table
INSERT INTO #NumColumnsRowsPerTable
select so.ID
     , so.Name
     , count(*)
     , 0
     , 0
     , -1
     , 0
  from sysobjects so
       left join
       syscolumns sc
         on so.ID = sc.ID
 where so.type    = 'U'
  and sc.xtype not in(35,36,34,99)
 group by so.name, so.ID




Declare @tablename varchar(100)
Declare @columnname varchar(100)
Declare @SelectCMD varchar(2048) 
Declare @SelectCMD2 varchar(2048)
Declare @SelectCMD3 varchar(2048)
-- Declare the Cursor for getting Row Count
DECLARE GetRowCount_table_names_cursor  CURSOR FOR 
SELECT Name
  FROM #NumColumnsRowsPerTable

OPEN GetRowCount_table_names_cursor 
FETCH NEXT FROM GetRowCount_table_names_cursor 

INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

set @SelectCMD = 'Update #NumColumnsRowsPerTable  set rows = (select count(*) from xxxTABLExxx), TotalCellCount =(columns * (select count(*) from xxxTABLExxx)) where name = ''xxxTABLExxx'''
select @SelectCMD2 = REPLACE ( @SelectCMD , 'xxxTABLExxx' , @tablename )  
execute (@SelectCMD2) 





		INSERT INTO #Columns
		select sc.Name
                     , 0
		  from sysobjects so
		       left join
		       syscolumns sc
		         on so.ID = sc.ID
		 where so.type    = 'U'
		   and so.Name =  @tablename 
		   and sc.xtype not in(35,36,34,99)
		Declare @NotNullCount int
		-- Declare the Cursor for getting Null Count
		DECLARE GetNotNullCount_cursor  CURSOR FOR 
		SELECT Name
		  FROM #Columns
		
		OPEN GetNotNullCount_cursor 
		FETCH NEXT FROM GetNotNullCount_cursor 
		
		INTO @columnname
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
		set @SelectCMD = 'update #Columns set NotNullCount =(select count(xxxCOLUMNxxx) from xxxTABLExxx where xxxCOLUMNxxx is not null) where name = ''xxxCOLUMNxxx'''
		select @SelectCMD2 = REPLACE ( @SelectCMD , 'xxxTABLExxx' , @tablename )  
		select @SelectCMD3 = REPLACE ( @SelectCMD2 , 'xxxCOLUMNxxx' , @columnname ) 
		print  @SelectCMD3
		execute (@SelectCMD3) 
		--select @SelectCMD3
		-- Get the next table name
		   FETCH NEXT FROM GetNotNullCount_cursor 
		   INTO @columnname
		End
		
		CLOSE GetNotNullCount_cursor
		DEALLOCATE GetNotNullCount_cursor


		Update #NumColumnsRowsPerTable  set NonNullCellsPerTable = (select sum(NotNullCount) from #columns), NullsPerTable =(TotalCellCount-(select sum(NotNullCount) from #columns)) where name = @tablename 

		delete from #Columns

	
-- Get the next table name
   FETCH NEXT FROM GetRowCount_table_names_cursor 
   INTO @tablename
END

CLOSE GetRowCount_table_names_cursor
DEALLOCATE GetRowCount_table_names_cursor
GO



select * from #NumColumnsRowsPerTable

select cast(((sum(NullsPerTable)*100)/sum(TotalCellCount)) as varchar(100)) + '% Null Saturation'  from #NumColumnsRowsPerTable
--drop table #NumColumnsRowsPerTable
drop table #Columns