How to loop a SELECT statement on a list of tables in Azure Synapse using Dynamic SQL?

Question

I often have to find out what is the latest date of a record count of multiple tables. Currently I'm trying to find out the maximum loaddate from a list of tables.

I have the below list of example tables:

TableA
TableB
TableC
TableD

So far I'm able to declare a variable and pass the single table name as a parameter into my SQL statemen as follows:

DECLARE @SQLstmnt varchar(500)
 
DECLARE @tname varchar(200)
SET @tname = 'TableA'
 
SET @SQLstmnt = 'SELECT MAX(loaddate) FROM ' + @tname
 
EXEC (@SQLstmnt)

But I don't know how to pass my entire table name list (TableA, TableB, TableC, TableD) and loop the above SELECT statement on each of those tables in the list.

I searched for a solution but I just don't seem to find the one that suits my case.

Can someone please help?

Answer 1

You could build a dynamic union query for all tables and execute it this way:

DECLARE @SQL NVARCHAR(MAX);
SELECT  @SQL = CONCAT('SELECT TableName, Date FROM (',
                STRING_AGG(CONCAT('SELECT TableName = ''', t.Name, ''',  Date = MAX(', c.name, ') 
                                  FROM ', QUOTENAME(s.name), '.', QUOTENAME(t.name)), ' UNION ALL '),
                ') AS t;')
FROM    sys.columns AS c
        INNER JOIN sys.tables AS t
            ON t.object_id = c.object_id
        INNER JOIN sys.schemas AS s
            ON s.schema_id = t.schema_id
WHERE   c.name = 'LoadDate' -- Check Column Exists
AND     t.name IN ('TableA', 'TableB', 'TableC', 'TableD') -- Limit to required tables;
HAVING COUNT(*) > 0

PRINT @sql
EXECUTE sp_executesql @SQL;

This will build a SQL Statement something like this for your example:

SELECT TableName, Date 
FROM (SELECT TableName = 'TableA',  Date = MAX(LoadDate) FROM dbo.TableA
     UNION ALL 
     SELECT TableName = 'TableB',  Date = MAX(LoadDate) FROM dbo.TableB
     UNION ALL
     SELECT TableName = 'TableC',  Date = MAX(LoadDate) FROM dbo.TableC
     UNION ALL
     SELECT TableName = 'TableD',  Date = MAX(LoadDate) FROM dbo.TableD
    ) AS t;

And return all tables/dates as a single set:

If you really did want to iterate over the tables then I would still use the system views as a starting to point to verify that (a) the table exists and (b) contains the load date column, but use a CURSOR to execute the statement against each table rather than building a single statement:

DECLARE TableCursor CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
    SELECT  CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name))
    FROM    sys.columns AS c
            INNER JOIN sys.tables AS t
                ON t.object_id = c.object_id
            INNER JOIN sys.schemas AS s
                ON s.schema_id = t.schema_id
    WHERE   c.name = 'LoadDate'
    AND     t.name IN ('TableA', 'TableB', 'TableC', 'TableD') -- Limit to required tables;

DECLARE @TableName SYSNAME;
OPEN TableCursor;
FETCH NEXT FROM TableCursor INTO @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = CONCAT('SELECT TableName = ''', @TableName, ''', Date = MAX(LoadDate) FROM ', @TableName);

    EXECUTE sp_executesql @SQL;
    FETCH NEXT FROM TableCursor INTO @TableName;

END

CLOSE TableCursor;
DEALLOCATE TableCursor;