SQL query to count records in a set of tables

I'm trying to get a count of all SQL tables that start with "comp", but I also want to filter out the count to only records inside these tables that have "Type" column > 0 (some are null, I don't want to count them), and "List" column should be NULL. The query below will get me as far as the count by table name, but I can't figure out how to update the query so it filters the count based on the values as well.

SELECT t.name, s.row_count from sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
AND t.type_desc = 'USER_TABLE'
AND t.name like 'comp%'
AND s.index_id IN (0,1)
N2012Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

PatHartmanCommented:
Add a WHERE clause.  I moved the other criteria down to the WHERE clause since it doesn't belong in the join.

SELECT t.name, s.row_count from sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
WHERE t.Type Is Not Null
AND t.List Is Null
AND t.type_desc = 'USER_TABLE'
AND t.name like 'comp%'
AND s.index_id IN (0,1)

PS - you didn't mention what table Type and List come from so I assumed "t".
0
aikimarkCommented:
If you only need the count, there's no good reason to return any data other than count.  It is much more efficient
Select Count(*)

Open in new window

0
N2012Author Commented:
The return is
  Invalid column name Type
  Invalid column name List

The first query I posted works, so 't' would suffice (you'd think!), but as stated originally, I'm still having trouble adding any "where" condition to it.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

N2012Author Commented:
select count(*) works for individual tables, but very clunky if you have dozens of tables, especially if you want to run periodic queries.

I also tried
  select count(*) from tablename1
  union select count(*) from tablename2
  etc...
but after the results are skewed -- count is way off.
0
PatHartmanCommented:
I'm still having trouble adding any "where" condition to it.
It is difficult to guess what might be the problem if you don't post the query and any error or a description of what is wrong with the result.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
try this
use yourDB

EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%]'')
BEGIN
 PRINT ''?''
 SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
 where Type>0 and List is NULL END '
0
N2012Author Commented:
Eugene Z, I tried your query and it's still a no go - the errors I get is as follows

Invalid column name 'Type'
Msg 207, Level 16, State 1, Line 6
Invalid column name 'List'
Msg 207, Level 16, State 1, Line 6
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
are "type" "list" names  of columns in your tables?

you posted;
"also want to filter out the count to only records inside these tables that have "Type" column > 0 (some are null, I don't want to count them), and "List" column should be NULL.
0
N2012Author Commented:
yes, they're column names
0
PatHartmanCommented:
I think Type is a reserved word and List might also be.  It is always best to never use reserved words as column names even if the db engine allows it.

Try encasing the reserved words in single quotes or square brackets.
0
N2012Author Commented:
I renamed the column names to pType and pList, I also updated the query with these new names, and still get the same error.
0
PatHartmanCommented:
It would be helpful if you would post the complete query each time you modify it.
0
N2012Author Commented:
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%]'')
BEGIN
 PRINT ''?''
 SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
 where pType>0 and pList is NULL END '

also tried the previous suggestion


SELECT t.name, s.row_count from sys.tables t
JOIN sys.dm_db_partition_stats s
ON t.object_id = s.object_id
WHERE t.pType Is Not Null
AND t.pList Is Null
AND t.type_desc = 'USER_TABLE'
AND t.name like 'comp%'
AND s.index_id IN (0,1)
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
It is possible that some of your “Comp” tables do not have these columns.

Check your tables and search table criteria
0
N2012Author Commented:
I reduced the query so only six tables would get queried (I added more characters to the tablename), and confirmed the tables all have these columns. Same error. Note - of the two columns - one of them has datatype INT and the other BIT -- would this make a difference?
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Try to run this against of table:
Select count(*) from your table where ..
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
And post exact error text
0
N2012Author Commented:
No error, I get an actual count that is accurate.

As I posted 3d ago, I also tried
  select count(*) from tablename1
  union select count(*) from tablename2
  etc...

after the first table or two, the results are skewed -- count is WAY off.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Add “where yourcolum is null...” to your union t-sql
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
And try the opposite with posted queries ( without “where”)
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
If you got with “where” errors “ there are not such fields “
It means these tables do not have such fields
0
N2012Author Commented:
As I posted 3d ago, I also tried
  select count(*) from tablename1
  union select count(*) from tablename2
  etc...
this query does not get me the proper totals, even if I use a 'where' condition (columns are found), which is why I posted

I tried the suggested queries, but the column names are not found.

I use SSMS, SQL back end. What do you mean t-sql?
0
N2012Author Commented:
I verified the columns specified in query exist in tables, so that's not the problem.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Make sure that you ran in the right db
0
N2012Author Commented:
Yep - confirmed.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
are you getting the expected result if you run?


use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%]'')
BEGIN
 PRINT ''?''
 SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
 END '
0
N2012Author Commented:
Only says "Query completed successfully." in the status bar at bottom of SSMS. Nothing shows up as a table.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
It means it did not find “if-ed criteria” tables in this db an you remove ]from”if”?
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%'')
BEGIN
 PRINT ''?''
 SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
 END '
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Try this one
0
N2012Author Commented:
same thing -
sql1.png
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
next try:


use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''%comp%'')
BEGIN
 PRINT ''?''
 SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
 END '
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
try this -- it should work for you (just adjust  "WHERE" )

EXEC [sp_MSforeachtable]
@command1=
      '
      BEGIN
       PRINT ''?''
       SELECT ''?'' TABLENAME,COUNT(*) CNT from ? where type >2 and list >3
       END '
 ,@whereand =
       ' AND OBJECT_ID in ( select  t.object_id
       from sys.tables t inner join sys.columns c
       ON t.object_id=c.object_id
       where t.name like ''Comp%''
       and C.name in (''List'',''type''))'ee092929018PNG.PNG
0
N2012Author Commented:
Thanks, Eugene. This query's more complicated than I'm used to but it seems to be working. I'm getting counts for each table, but they are broken up in separate tables. My intention was to get the totals to show up in one table (kind of like a 'union select' would do) so I can later use them in a report. can this query be modified to join all totals together in one table?
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
Declare @tbl table (tblname nvarchar(124), cnt int)
--count2

insert into @tbl
EXEC [sp_MSforeachtable]
@command1=
      '
      BEGIN
       PRINT ''?''
       SELECT ''?'' TABLENAME,COUNT(*) CNT from ? where type >2 and list >3
       END '
 ,@whereand =
       ' AND OBJECT_ID in ( select  t.object_id
       from sys.tables t inner join sys.columns c
       ON t.object_id=c.object_id
       where t.name like ''Comp%''
       and C.name in (''List'',''type''))'

--result table
       select * from @tbl
0

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
N2012Author Commented:
It works!!!  :)
Many thanks!!!!!
0
N2012Author Commented:
Thanks, Eugene!!!!
0
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
Query Syntax

From novice to tech pro — start learning today.