ramlicious Blogs by Tina & Prabhu

October 8, 2010

Getting count of Nulls and NonNulls column totals from all tables in a database

Filed under: SQL Server — Prabhuram @ 6:39 am

The SQL given here will display all the table columns in the database followed by rows having nulls and non-nulls. It has to be executed in multiple phases.

The first one generates the insert script which can be later executed to collect the information from respective table.

--The following SQL statement create sql queries that will collect
--NULLS/non-NULLS from all tables in the database
;with TableObjects as (
       select name TableName, Id TableId 
       from sysobjects where xtype = 'u'
), TableColumns as
(
       Select TableName, Name TableColumn, ColId 
       From SysColumns c
              inner join TableObjects o on o.tableid = c.id
       WHERE
       --exceptions add 
       not exists (select * from systypes t 
              where t.xtype = c.xtype and name in ('image', 'text'))
)
Select 
'INSERT INTO TableStudy 
Select ''' + TableName + ''' TableName,''' + TableColumn +  ''' ColumnName,
        (Select count(*) From [' + TableName + '] Where [' + TableColumn + '] 
                     is NULL) NULLs,
        (Select count(*) From [' + TableName + '] Where [' + TableColumn + '] 
                     is not NULL) NonNULLs;
GO
' 
From TableColumns


--Execute the above results in another database
--which can take hours or days to run depending on data
--before execution create the following table
--create table TableStudy (
--     TableName VARCHAR(100),
--     ColumnName    VARCHAR(100),
--     Nulls  INT,
--     NonNulls      INT
--)

--drop table TableStudy

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

You must be logged in to post a comment.

Powered by WordPress