How to determine the maximum id of a set of tables in my database

Question

I have a requirement to determine the maximum Id int value for a set of tables in my database. The column is always 'Id' and is the primary key. Is there a simple way I can make this determination without resorting to a cursor or looping?

Answer 1

could you create a sp_excutesql query for something like?

select max(id) from (
    select id from t1
    union all
    select id from t2
    union all
    select id from t3)

Possibly by putting the table names into a temp table/cte to start with?

Actually think there is a loop happening.

Answer 2

SELECT MAX(MaxId) As MaxId FROM (
    SELECT MAX(id) AS MaxId FROM Table1
    UNION ALL
    SELECT MAX(id) AS MaxId FROM Table2
) AS T1