Get Count of Non-Null Records Dynamically for Each Column in Table

[kjv1611]

I believe I've asked something like this question in the past. Next week, I think it very well may benefit me (time-wise) to be able to do this.

Basically, in adhoc situations, I'm given one table (well, there are a few pieces, but I'm focusing on one piece - one specific table of that piece) to make use of for our production group, and produce some analysis.

In this process, there are always several columns which are NULL (nearly 100 total columns in table - anywhere from 1/10 to 1/2 could be ALL NULL).

Well, I have to always look at the table, taking the time to see what data is there, and then of that data, what is useful, and what is not. I think that if I can pull a quick snapshot of the table upon learning the table name, and seeing what fields have ALL Null values vs Some/No Null values, it'll be a big help.

To that end, today I put together something that ALMOST gets me there. And this is the point where I want to ask for a little bit of guidance.

Here is what I have (I've changed it a few times b/c I've been working through different ideas):

DROP TABLE #Fields
CREATE TABLE #Fields(ColumnID int ,Field varchar(150) NOT NULL ,Records bigint NULL)

DECLARE @count INT
DECLARE @colCount INT
DECLARE @Column VARCHAR(150)
DECLARE @RecordCount BIGINT

SET @count = 1
SELECT @colCount = MAX(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),COLUMN_NAME, 'ColumnID'))
FROM	Sandbox.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTableName'

WHILE (@count < @colCount + 1)
BEGIN
	EXEC('DECLARE @RecordCount BIGINT SELECT @RecordCount = COUNT(1) FROM MyTableName WHERE [' + @Column + '] IS NOT NULL')
	
	SELECT	@RecordCount = COUNT(1) FROM MyTableName WHERE @Column IS NOT NULL
	INSERT INTO #Fields
	SELECT	COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),COLUMN_NAME, 'ColumnID') AS COLUMN_ID
			,COLUMN_NAME ,@RecordCount
	FROM	Sandbox.INFORMATION_SCHEMA.COLUMNS
	WHERE	TABLE_NAME = 'MyTableName'
		AND	COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),COLUMN_NAME, 'ColumnID') = @count
	SET @count = @count + 1
END

SELECT	* FROM #Fields

What I am getting is:[tt]
ColumnID ColumnName Records
1 Name 0
2 Add 0
3 City 0
4 State 0
5 Color 0
6 Car 0
7 Mule 0
[/tt]
Hopefully you can tell the values are totally made up.

So I'm getting the ColumnID and ColumnName, but not the Count. So what I'm guessing is the problem is that perhaps I need to have the entire thing run as a dynamic SQL Variable, rather than part as "normal" SQL, and only part dynamic.

If anybody can give me any insights that'd be most wonderful!

Thanks in advance.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[simian336]

Could you give a data sample (made up) and expected results.

Simi

 

[kjv1611]

Basically, just some columns with some NULLs, some with no NULLs, and some with all NULLS. Expected Results would be same as what I am getting, listed above, but having actual counts of non-null records (in the field)..

So, I'll just say a table has a few fields (for this example): (added 2 fields to above)
[tt]MyTableName
Name Add City State Color Car Mule Date Dollars
Joe NULL NULL TX Red NULL Big 1/1/2012 100.06
Sue NULL NULL GA Blue NULL Fast 2/1/2010 50.67
Ron NULL NULL NY NULL NULL Small 3/1/2009 0.00
Amy NULL NULL CA Orange NULL Fat 7/6/2011 2.05
[/tt]

Expecting:[tt]
ColumnID ColumnName RecordCount
1 Name 4
2 Add 0
3 City 0
4 State 4
5 Color 3
6 Car 0
7 Mule 4
8 Date 4
9 Dollars 4[/tt]

So, What I'd really like to see (long term) is how many of those have ALL Null Records... and perhaps how many have all Zero values when they have values... and then a sample of data from those which do have anything other than NULLs. And of those which are not ALL Nulls, now many records have real values..

But for now, I'm just looking for:

>ColumnID (number of column in table)
>ColumnName
>Number of Non-Null Records

I can get the rest later. For now, I'm just missing the Number of Non-Null Records. I think I've got the basic SQL right on that, except that I'm not running it correctly - dynamic vs non-dynamic, or else how I'm pullinging in the column name.. Which is what has me thinking that perhaps I need to do the whole thing as a dynamic SQL statement/variable..

Thanks again for any help.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

Here's my take on it...

DROP TABLE #Fields
CREATE TABLE #Fields(ColumnID int ,Field varchar(150) NOT NULL ,Records bigint NULL)

DECLARE @count INT
DECLARE @colCount INT
DECLARE @Column VARCHAR(150)
DECLARE @Table VarChar(150)
DECLARE @RecordCount BIGINT
DECLARE @Sql VarChar(8000)

Set NoCount On

[!]Set @Table = 'Student'[/!]

SET @count = 1
SELECT	@colCount = MAX(Ordinal_Position)
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE	TABLE_NAME = @Table

WHILE (@count < @colCount + 1)
BEGIN
  Select @Column = Column_Name 
  From   Information_Schema.Columns
  Where  Table_Name = @Table
         And Ordinal_Position = @Count

  Set    @SQL = '
           Insert
           Into	#Fields(ColumnId, Field, Records)
           Select ' + Convert(VarChar(10), @Count) + ',
                  ''' + @Column + ''',
                  Count(1) As Records
           From   [' + @Table + ']
           Where  [' + @Column + '] Is Not NULL

  Exec (@SQL)
  SET @count = @count + 1
END

SELECT * FROM #Fields

Note that I created another variable at the top named @Table. This way, all you need to do is change the value of that variable to run this for other tables.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

The reason why you are not getting any counts....


[tt] EXEC('DECLARE @RecordCount BIGINT SELECT @RecordCount = COUNT(1) FROM MyTableName WHERE [' + @Column + '] IS NOT NULL')[/tt]

You are declaring @Record count within an EXEC statement. No doubt the count is getting set inside the exec statement, but when the EXEC is done running, the variable goes out of scope.

Your next statement is...

[tt]
SELECT @RecordCount = COUNT(1) FROM MyTableName WHERE @Column IS NOT NULL
[/tt]

This is setting @RecordCount to 0 because it is treating @Column as a string instead of a column name. Since the string is not null, there are no records that match and the @RecordCount is set to 0.

Your second statement is essentially the same as...

Select @RecordCount = Count(1)
FROM   MyTable
Where  'AnyOleColumnName' is Not NULL

Does this make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kjv1611]

Thanks - yes, both make sense: Table variable and how I'm getting 0 records...

So if I get rid of the SELECT @RecorCount... statement after the EXEC statement, will it leave the @RecordCount to what it should be? Based on what I've shown so far? If so, then it was just a goof on my part..

I'll try to dig some more into this later on... hopefully this evening, as I've got to work on some other items before I leave work.. eek!

Thanks again for the time... I'll follow-up later. If I don't get to it this evening, then I'll try some time between tomorrow and Saturday. I won't be at work, at the office tomorrow, so not sure when/where after tonight..



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gmmastros]

I posted twice. Did you see the query in my first post? It should do everything you want.

If you removed the SELECT @RecordCount statement after the EXEC statement, you'll probably get NULLs for each row in the output because the @RecordCount within the EXEC goes out of scope when the EXEC is done running.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Take a look at the last problem in this blog post

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/interesting-t-sql-problems

PluralSight Learning Library

 

[kjv1611]

Thanks again... I'll see if I can get it squared away today..

gmmastros,

Thanks, yeah, I did see your 2 posts, just didn't get a chance to test it out yet. I had read it all in a hurry.



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Well, I must have something slightly off somewhere. Now, I'm getting no records in the final output. Before, I had everything but the record count.

I'll have to get back to this later, though, b/c I've got to spend what few minutes I've got left this morning before pulling away from PC to get another item finished.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Thanks, markros, also, for posting the link to the blog post. I'm going to hopefully look at that one today. I'll post back with any results there.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57