CODEWITHSUNDEEP
sqlsql-server
Vetras
Vetras

Reputation: 2001

SQL inner join based on table name pattern

On this legacy SQL Database with hundreds of tables, I need to do a inner join on all tables whose name follows a format:

I am not sure this is possible at all.

Clearly, with this syntax it is not (but it may help understand what I'm aiming at):

USE [LegacyDB_Name]

SELECT *
FROM '%_foo_%' inner join '%_foo_%'
where my_stuff_is(some condition)

Any Suggestions? Ideas on how I can do this? Maybe there is an easier path this young padawan is not seeing...

Many Thanks!

Upvotes: 2

Views: 2409

Answers (3)

trincot
trincot

Reputation: 351128

If your tables all have the same structure (i.e. columns), then you could do this in two steps.

  1. Generate the SQL statement:

    select 'UNION ALL SELECT ''' + table_name + ''' AS table_name, * FROM ' 
       + table_name AS stmt
from   information_schema.tables
where  table_type = 'BASE TABLE' 
   and table_catalog = 'LegacyDB_Name'
   and table_name LIKE '%foo%';

    The output will be something like:

    stmt
--------------------------------------------------------------------
UNION ALL SELECT 'barX_foo_bazX' AS table_name, * FROM barX_foo_bazX
UNION ALL SELECT 'barX_foo_bazY' AS table_name, * FROM barX_foo_bazY
UNION ALL SELECT 'barX_foo_bazZ' AS table_name, * FROM barX_foo_bazZ

    From this output, copy the SQL rows and remove the first 2 words (UNION ALL) from the first line. This is a valid SQL statement.

  2. Execute the SQL statement derived above

    If you need this SQL more often, then create a view for it:

    CREATE OR REPLACE VIEW all_foo AS
              SELECT 'barX_foo_bazX' AS table_name, * FROM barX_foo_bazX
    UNION ALL SELECT 'barX_foo_bazY' AS table_name, * FROM barX_foo_bazY
    UNION ALL SELECT 'barX_foo_bazZ' AS table_name, * FROM barX_foo_bazZ;

    Now you can query like

    SELECT * FROM all_foo WHERE ...

Upvotes: 1

Joe C
Joe C

Reputation: 3993

Maybe the simplest way to do this is to declare a cursor based on the below query and build a dynamic sql query. Research tsql cursor and dynamic sql execution and it should be fairly simple.

SELECT * 
    FROM information_schema.tables
    Where Table_Type = 'Base Table' And Table_Name Like '%_foo_%'

Upvotes: 1

Magisch
Magisch

Reputation: 7352

I am not sure this is possible at all.

Nope, table names cannot contain or use wildcards, they must be strings.

My advice would be to find whatever program makes these select queries and include whatever pattern matching you need in the queries in there.

But your finished query must contain table names as strings.

Upvotes: 2

Related Questions