Summarize columns by rowcount from a selected set of database tables

Question

Summarize columns by rowcount from a selected set of database tables

grover-823902

Grasshopper

Points: 21
More actions
August 20, 2009 at 3:50 pm

#209010

I am needing to provide a single summary table of with the total number of non null values from a selected group of tables from a database containing 1,801 tables.
I can easily query the dbo.sysobjects or information_schema.columns to obtain the table names that are required..
-- Result set yields 77 tables
Select Distinct TABLE_NAME, COLUMN_NAME, IS_NULLABLE
From [MyDatabase].information_schema.columns
Where table_name like '%xxxx%'
As well, I can find all rows with at least one column as not having the value for which I wish to count for each table by using the following:
Select C1, C2, C3, C4, C5, C6, C7
From [MyDatabase].[dbo.mytable]
Where (C1 is not null or C2 is not null or C3 is not null or C4 is not null or C5 is not null or C6 is not null or C7 is not null)
-- Result yields all rows that contain a non Null value
The final table output should have 1 row for each table [Table_Name], the total # of rows for each table (Null rows are okay) [TotalRow], with the remaining columns each having the total number of non Null values.
Table_Name -- TotalRow -- C1 -- C2 -- C3 -- C4 -- C5 -- C6 -- C7
It would be ideal if this could be written as a sp_ as this will be run more than once. Is this a far reaching request and can this even be done efficiently in SQL or should I look elsewhere? :w00t:

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 1

grover (8/20/2009)
Is this a far reaching request and can this even be done efficiently in SQL or should I look elsewhere?

Uh... and where would you look? 😉 I'll be back soon...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004646 More actions · Answer 2

By the way... do all the tables that match the pattern have an identical structure and identical column names?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

grover-823902 Grasshopper Points: 21 More actions · Answer 3

Why yes sensai, they do have an identical structure and identical column names...but no common relation. It's a vertical [slash] horizontal relationship summary quagmire [sans Glen] that I'm trying to work through. I thought about creating a single "master" table but I don't want to run an INSERT statement 77 times.

I am willing to adopt the "Modenist" approach and am new to set based code, all the while realizing your RBAR acronymn is spot on.

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

grover (8/21/2009)
I thought about creating a single "master" table but I don't want to run an INSERT statement 77 times.

How about creating a view ?

Create View MasterView

as

Select Col1,Col2,Col3 from taba

union all

Select Col1,Col2,Col3 from tabb

union all

Select Col1,Col2,Col3 from tabc

Clear Sky SQL
My Blog[/url]

grover-823902 Grasshopper Points: 21 More actions · Answer 5

Taking your idea and running with it I wound up utilizing view and union all and modified the Select statement to include the table name using Excel and the concatenate function to assemble the 77 select statements needed to construct the MasterView.

Create View MasterView

as

Select 'taba' as 'TableName', Col1,Col2,Col3 from taba

union all

Select 'tabb' as 'TableName'Col1,Col2,Col3 from tabb

union all

Select 'tabc' as 'TableName'Col1,Col2,Col3 from tabc

-- and so on for all 77 tables

The final summary table was merely a Select Count() set...

Select [Table Name], Count([Table Name]) as TotalRow, Count([C1]) as 'C1', Count([C2]) as 'C2', Count([C3]) as 'C3'

From MasterView

Where (C1 is not null or C2 is not null or C3 is not null)

Group By [Table Name]

Order By [Table Name] asc

Thanks for the input.