Querying multiple tables

[zimmer_04]

I'm writing a query for multiple tables that follow a certain naming convention. I'm wanting to know if there is a way to write a query that will hit all of these tables without actually writing in the names of each and every individual table.

For example, say the naming convention is ST_ERR* - where a couple of the tables are named ST_ERR_CODES or ST_ERR_NAMES. Is there a way to write a query that would hit all of these tables, and put the table name that each row of data came from? More specifically what I'm trying to do is: there is an error_flag and process_dt_tm field on each of these tables that i'm wanting to know how many times each error_flag occurs on each date (process_dt_tm) for each table.

[Shuja Ali]

Not unless you write a dynamic query. Which database are you using?

[zimmer_04]

Not sure what you mean by which database - sorry, not too database savvy. I'm working in sqldeveloper.

That being said, I'm also not expecting an exact step-by-step, but if I could be pointed in the right direction, I can do some research and messing around and hopefully figure it out.

[Shuja Ali]

By which Database, I meant whether u r using Oracle or SQL and you have already answered my question
So you are using SQL Developer. Dynamic queries are the ones that you can write on the fly, something like this Declare @SQL varchar(1000)
Set @SQL = 'Select * From '
Declare @Table varchar(50)
Set @Table = 'TABLE1';
Exec(@SQL + @Table) Now you can change this code to use any table. The only problem with this is that it does not get compiled and the SQL cannot generate an execution plan for this.

Your other option is to use CLR stored procedures, where you can write .NET Code in side a stored procedure. Check MSDN for more.

[zimmer_04]

Thanks!