Creating a Query for each row in a SQL Table

Question

I have a schema in my SQL table out of which some table has a time value stamp (same column name 'timestamp' in all the tables in the schema) and I need to create a new table which will give the latest time stamp for each such table. I have achieved a part which will give me a table with 2 columns, one the table name column and another column which gives the query for each table which if runs will give me the latest timeStamp for each table in table name Column. The script I used is as follows and I show 3 rows as an example:

WITH CTE AS
(
    SELECT
        CONCAT(schema_name(t.schema_id), '.',t.name) AS table_name,
        c.name AS 'time_stamp'
    FROM
        sys.tables t
    INNER JOIN 
        sys.columns c ON c.object_id = t.object_id
    WHERE
        schema_name(t.schema_id) = 'PROD' 
        AND c.name = 'timestamp'
)
SELECT table_name, time_stamp 
INTO #TEMP_TABLE
FROM CTE 

DECLARE @i int = 1, @c int = (SELECT COUNT(*) FROM #TEMP_TABLE)
DECLARE @Result TABLE 
                (
                    tName varchar(500), 
                    tStamp varchar(500)
                )

WHILE (@i <= @c)
BEGIN
    INSERT INTO @Result
        SELECT 
            table_name, 
            'SELECT MAX('+ time_stamp +')  FROM ' + table_name
        FROM #TEMP_TABLE;

    SET @i = @i + 1
END

DROP TABLE #TEMP_TABLE

SELECT * FROM @RESULT

When I run this script I get the following table (3 rows shown as an illustration)

My output (O)

    tName              tStamp        
    -----------------------------------------------------------
    PROD.table_A       SELECT MAX(time_stamp) FROM PROD.table_A 
    PROD.table_B       SELECT MAX(time_stamp) FROM PROD.table_B 
    PROD.table_C       SELECT MAX(time_stamp) FROM PROD.table_C

However what I want is the value of the query in the tStamp column and not the query string. So actually the output table should look like (say assuming the query in each of the above rows in column tStamp. I put in some max values as an example when we run each query in tStamp column)

My final expected output (F)

tName             tStamp        
------------------------------------------
PROD.table_A      2021-10-12 14:20:56.000
PROD.table_B      2021-11-01 19:04:35.000
PROD.table_C      2021-10-23 08:07:12.000

I am in a limbo at this stage not sure, how to get the table F from table O. So I will really appreciate any help. If it can be possible to tweak something which I am doing to get directly the output table F or if we can work on the table O to get to table F anything can help.

Thanks in advance.

score 0 · Answer 1 · answered Nov 13 '21 at 02:50

If this is a one shot thing, I would consider just using a macro (vim, excel) to generate the query text for each table using your CTE results and then paste it back in and run.

If not, you could consider some of the suggestions for dynamic sql in this article: [https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/][1]

Creating a Query for each row in a SQL Table

1 Answers1