x

Select count for every column in every table?

First, I should tell you that I am a rookie at working with SQL. I am working with a database has about 350 tables holding of interest to me. I am only interested in those tables containing business type information. i.e. not the tables dealing with system type information. I would like to determine the number of entries (non-null) there are in every column in every table.

Any ideas and/or guidance would be much appreciated.

Thank you,

George Teachman

more ▼

asked Sep 14, 2016 at 05:59 PM in Default

avatar image

gteachman
0

So you want a list of all of the distinct values (non-null) for every given column in every table? Is that correct?

Sep 14, 2016 at 08:23 PM JohnM

Sorry, I wasn't clear. No, what I'm looking for is just a count of the number of entries in each column in each table in the database. What I'm trying to do is find out which columns are rarely, if ever, used. If the count of entries in column Alpha is 30,000 and the count of entries in column Beta is 0, then maybe I need to talk with the business folks and see if Beta is really what they are after or if it just needs to be tweaked. Or, isn't really needed. Does that make sense?

Thanks, George Teachman

Sep 15, 2016 at 11:32 AM gteachman

Does NULL count as a valid value in your application?

Sep 15, 2016 at 02:10 PM JohnM

Good question. Users have been told (and told and told and told....) that if they have zero as a measurement, then enter zero. They told...do not leave the field blank if the measurement is really zero.

So, in answer to your question, NULL is not a valid value in our database.

Thanks, George

Sep 15, 2016 at 06:35 PM gteachman
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

So, running with the Alpha Beta example, it sounds like you'd like something like this for each table

 CountOfAllRowsInTable Alpha_CountOfNonNullRows Beta_CountOfNonNullRows Gamma_CountOfNonNullRows Delta_CountOfNonNullRows
 --------------------- ---------------------    ----------------------- ------------------------ ------------------------
 30000                 30000                    0                       24661                   30000                    

Something like this should generate your numbers:

 SELECT 
     count(*) as CountOfAllRowsInTable, 
     count([Alpha]) as [Alpha_CountOfNonNullRows], 
     count([Beta]) as [Beta_CountOfNonNullRows], 
     count([Gamma]) as [Gamma_CountOfNonNullRows], 
     count([Delta]) as [Delta_CountOfNonNullRows] 
 FROM 
     dbo.YOUR_TABLE_NAME


You can both generate and execute that query programmatically using the data in sys.tables and sys.columns:

 set ansi_warnings off;
 
 declare @schema_name nvarchar(255) = N'dbo';
 declare @table_name nvarchar(255) = N'YOUR_TABLE_NAME';
 
 declare @count_query nvarchar(max);
 
 select 
     @count_query = coalesce(@count_query + ', ', '') + 'count([' + c.name + ']) as [' + c.name + '_CountOfNonNullRows]' 
 from 
     sys.tables as t
     inner join sys.columns as c 
         on t.object_id = c.object_id
 where 
     t.name = @table_name
     and t.schema_id = schema_id(@schema_name)
 order by 
     c.column_id;
 
 
 select @count_query = 'SELECT count(*) as CountOfAllRowsInTable, ' + @count_query + ' FROM ' + @schema_name + '.' + @table_name;
 
 exec(@count_query);

I'll leave the table loop as an exercise for the reader. Hopefully, this gets you started.

more ▼

answered Sep 15, 2016 at 08:43 PM

avatar image

KenJ
25k 3 13 20

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53

asked: Sep 14, 2016 at 05:59 PM

Seen: 82 times

Last Updated: Sep 15, 2016 at 08:44 PM

Copyright 2018 Redgate Software. Privacy Policy