question

gteachman avatar image
gteachman asked

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
count
4 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

So you want a list of all of the distinct values (non-null) for every given column in every table? Is that correct?
0 Likes 0 ·
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
0 Likes 0 ·
Does NULL count as a valid value in your application?
0 Likes 0 ·
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
0 Likes 0 ·

1 Answer

· Write an Answer
KenJ avatar image
KenJ answered
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.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.