Select Count from multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select Count from multiple tables

Author

Topic

mmalaka
Starting Member

33 Posts

Posted - 2013-02-27 : 09:30:39

Experts

I am trying to select a count of a specific field in multiple tables...all the required tables are named like Table_1, Table_2,Table_3..etc

I am looking for something like Select Count(Field) from Tables where Table name is like Table_%

Any advice please?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 09:58:59

SQL does not let you specify a wild-card for table names, so you have to do something else. One possibility is as follows:

SELECT SUM(n) AS TotalCount FROM
(
	SELECT COUNT(Field) AS N FROM Table_1 
	UNION ALL
	SELECT COUNT(Field) FROM Table_2 t
	-- etc
) s

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-02-27 : 10:06:23

Or dynamically

SELECT
' select count(yourColumn) as ' + Name +'Counts
from '
+ '[' + SCHEMA_NAME(schema_id) +'].'
+ '[' + name + ']'
FROM sys.tables
WHERE name like 'TABLE_%'

Jim

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources