Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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)
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)
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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".
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".
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(*)
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.
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.
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.
I also tried
select count(*) from tablename1
union select count(*) from tablename2
etc...
but after the results are skewed -- count is way off.
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.
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 '
use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%]'')
BEGIN
PRINT ''?''
SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
where Type>0 and List is NULL END '
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
Invalid column name 'Type'
Msg 207, Level 16, State 1, Line 6
Invalid column name 'List'
Msg 207, Level 16, State 1, Line 6
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.
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.
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.
Try encasing the reserved words in single quotes or square brackets.
I renamed the column names to pType and pList, I also updated the query with these new names, and still get the same error.
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)
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)
It is possible that some of your “Comp” tables do not have these columns.
Check your tables and search table criteria
Check your tables and search table criteria
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?
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.
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.
If you got with “where” errors “ there are not such fields “
It means these tables do not have such fields
It means these tables do not have such fields
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?
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?
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 '
use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%]'')
BEGIN
PRINT ''?''
SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
END '
Only says "Query completed successfully." in the status bar at bottom of SSMS. Nothing shows up as a table.
use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%'')
BEGIN
PRINT ''?''
SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
END '
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''comp%'')
BEGIN
PRINT ''?''
SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
END '
next try:
use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''%comp%'')
BEGIN
PRINT ''?''
SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
END '
use yourDB
EXEC [sp_MSforeachtable] @command1='
IF (''?'' LIKE ''%comp%'')
BEGIN
PRINT ''?''
SELECT ''?'' TABLENAME,COUNT(*) CNT from ?
END '
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''))'
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''))'
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?
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
--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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial