Hendrik Kleine Hendrik Kleine - 1 month ago 7
SQL Question

SQL Union multiple tables - Where substring(table_name) = x

Microsoft's Dynamics NAV has a feature where each SQL table-name is preceded by the name of a company, i.e.


  • Company1$G_L Entry

  • Company2$G_L Entry

  • Company3$G_L Entry

  • Company4$G_L Entry

  • etc...



I'd like to create UNION ALL (the table columns/types are identical) for all tables ending in '$G_L Entry', that is, without specifying all the tables.

This is because, at any time, the user of the NAV UI may add/remove companies, and I'd prefer not to update my query every time that happens.

Any directions much appreciated.

(Using SQL Server 2012)

Answer

You could build dynamic sql by concatenating your table names with a union all (obviously you'd need to list the columns)

DECLARE @allEntryTables VARCHAR(MAX);

SET @allEntryTables = ( SELECT
                         STUFF((SELECT
                                 ' UNION ALL SELECT col1, col2, col3 FROM ' + name
                                FROM sys.tables
                                WHERE name LIKE '%$G_L Entry'
                                FOR XML PATH('')), 1, 11, ''));

EXEC (@allEntryTables);