1

I can select the searching tables in database with a pattern by its names in sysobjects.

Then I have to apply same select for data of every found tables and get the output as the combined union result.

Can anyone advise how that can be implemented in SQL Server 2012 (v11)?

Thanks in advance!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
F F
  • 13
  • 2
  • 2
    Does this answer your question? [SQL Server Dynamic SQL - Get output from list of tables](https://stackoverflow.com/questions/67369265/sql-server-dynamic-sql-get-output-from-list-of-tables) Although needing to do this in the first place is a sign of bad design – Charlieface Jul 13 '21 at 19:20
  • Thanks for your comment and for the reference to the most close topic. However it does not work for me as does not make iteration-loop through the tables for UNION results. However it works for only one table from the list. Still looking for iterator by subqueried tables. – F F Jul 14 '21 at 11:16
  • `does not make iteration-loop through the tables for UNION results` That's exactly what those answers do. If you want something else you need to explain better. How about a demonstration of what a dynamic query would look like once it is constructed from `sys.objects` – Charlieface Jul 14 '21 at 11:47
  • The list of my tables I can get like following: SELECT OBJECT_NAME(i.id) FROM sysindexes i INNER JOIN sysobjects o ON i.id=o.id WHERE OBJECT_NAME(i.id) like '%_snap_%' AND indid<2 All the _snap_ tables having common field Date, that I need to get unique values from this field across all the selected tables. – F F Jul 14 '21 at 12:08
  • Why do you need to query `sysindexes` also? – Charlieface Jul 14 '21 at 12:46
  • you are right it is not for this case and just reused, sysobject is only enough – F F Jul 14 '21 at 12:56

1 Answers1

1

You need to use dynamic SQL for this.

  • Always use QUOTENAME on object names
  • Don't use the old sysobjects tables, they are deprecated
  • Check the generated SQL with PRINT @sql;
DECLARE @sql nvarchar(max) =
(SELECT
    STRING_AGG(CAST('
SELECT [Date] FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
    AS nvarchar(max)), N'
UNION
'    )
  FROM sys.tables t
  WHERE t.name LIKE '%_snap_%'
);

PRINT @sql;

EXEC (@sql);

If your SQL Server version is 2016 or earlier, you cannot use STRING_AGG, so you need to use the FOR XML method

DECLARE @separator nvarchar(100) = N'
UNION
';

DECLARE @sql nvarchar(max) = STUFF(
(SELECT
    @separator + '
SELECT [Date] FROM ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name)
  FROM sys.tables t
  WHERE t.name LIKE '%_snap_%'
  FOR XML PATH(''), TYPE
).value('text()[1]','nvarchar(max)'),
1, LEN(@separator), '');

PRINT @sql;

EXEC (@sql);
Charlieface
  • 52,284
  • 6
  • 19
  • 43