CODEWITHSUNDEEP
sqlsql-server-2008
CPickler
CPickler

Reputation: 93

SQL Using results from query (Table Names) to search within identified Tables

I'm sure this is a very simple question, but I have not been able to find an answer online yet. I want to run a query that will identify a subset of tables within a database, and then will run a query against each of these tables individually and give the results. The tables get created on a weekly basis and so I want to be able to run a query against all the tables that cover a certain period. In effect, I am wanting to create a foreach loop.

The pieces independently work, but I can't get them merged correctly.

Query 1: Tables

Select name from DBName.sys.tables
where name like 'Table_Name_2015%'
and is_ms_shipped = 0

Query 2: Search within Table

Select Top 1000 [Column1], COUNT(*) As ctr
From Table_Name_20151026
Group By [Column1]
Order By ctr desc

I also tried having the results from Query 1 go into a table and then trying to call the table in Query 2, but I was not successful.

Select name into Table_Collection from DBName.sys.tables

I have tried setting the from in Query 2 as a variable but at best I can only get it to pull one table's worth of results.

I have searched through this and other websites and I have found a few more starts which didn't pan out such as sp_MSforeachtable.

Upvotes: 0

Views: 874

Answers (1)

Hogan
Hogan

Reputation: 70523

Select 'Select Top 1000 [Column1], COUNT(*) As ctr From '+name+' Group By [Column1] Order By ctr desc' as sql
from DBName.sys.tables
where name like 'Table_Name_2015%'
and is_ms_shipped = 0

Upvotes: 1

Related Questions