SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select Count from multiple tables		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mmalaka
Starting Member

Ireland
33 Posts
Posted - 02/27/2013 :  09:30:39  Show Profile  Visit mmalaka's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

1530 Posts
Posted - 02/27/2013 :  09:58:59  Show Profile  Reply with Quote
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
Go to Top of Page

jimf
Flowing Fount of Yak Knowledge

USA
2868 Posts
Posted - 02/27/2013 :  10:06:23  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000