SQL Server loop select through tables selected in sysobjects

Question

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!

Answer 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);