Display columns with no values in table, select count(*)

I have a table with 250 columns and 10 000 000 rows. I want to display all the columns that doesn't have  a value except for the default value.

I'm looping through the syscolumns for the table SalesTransactions by using select count like below, I'm checking what data type it is and I have different conditions for each column type. (Int and varchar in this example)

SELECT COUNT(*) From SalesTransactions WHERE CustomerNo >0

SELECT COUNT(*) From SalesTransactions WHERE CustomerName <>''

This works fine on table with not so many rows. Is there a more efficient way to count rows than using select count(*) for each column ?

I don't care to know the exact numer of rows, I'd rather just display columns that doesn't have values.

Any suggestions ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Not unless you have the columns indexed, but then indexing 250 columns for 10 mil rows itself would take too long.

I am presuming that this is a one time thing that you are doing to clean up the db.

I would just set it up as a stored procedure and let it run overnight.

If it is a periodic thing that you need to do, set aside a time on the server to run the stored procedure as needed.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Counting all columns will actually slow it down on its own, try this:

SELECT COUNT(CustomerNo) From SalesTransactions WHERE CustomerNo > 0


SELECT COUNT(CustomerName) From SalesTransactions WHERE LEN(CustomerName) > 0

Probably wont make much difference thou but try anyway. As sshah254 says, look at indexes or settings a time to do it.
Patrick MatthewsCommented:

While this may not have a bearing on this particular question, please keep this in mind about the difference between COUNT(*) and COUNT(ColumnName):

If you use COUNT(*), SQL Server will return a count of all the rows that meet the conditions specified.

However, if you use COUNT(ColumnName), SQL Server will return a count of all the rows that meet the conditions specified, and for which ColumnName is not null.  Sometimes that will be exactly what you want, but sometimes it will not be.


CREATE TABLE xxTest (ID int identity(1, 1), CustomerName varchar(50))

INSERT INTO xxTest (CustomerName) VALUES ('1')
INSERT INTO xxTest (CustomerName) VALUES ('2')
INSERT INTO xxTest (CustomerName) VALUES ('3')
INSERT INTO xxTest (CustomerName) VALUES ('4')
INSERT INTO xxTest (CustomerName) VALUES ('5')
INSERT INTO xxTest (CustomerName) VALUES ('6')
INSERT INTO xxTest (CustomerName) VALUES ('7')
INSERT INTO xxTest (CustomerName) VALUES ('8')
INSERT INTO xxTest (CustomerName) VALUES ('')
INSERT INTO xxTest (CustomerName) VALUES (NULL)

SELECT COUNT(CustomerName) AS CustName FROM xxTest


Open in new window

In that example, COUNT(*) returns 10, while COUNT(CustomerName) returns 9 (because one of the rows has a null in CustomerName).

Mackey_manAuthor Commented:
Thanks guys for trying to help me, I just have to let my procedure run and just use it on tables less rows.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.