Select a table's current identity from another database

How can I get the current identity of a table using OPENQUERY in SQL Server?
How to select the current identity on the table from another database?

If the Database is on the same Instance, then you don't need to use OPENQUERY. But even if the Database is on another Instance, then you still shouldn't need OPENQUERY (as @AndriyM reminded me of in a comment on this Answer): since the query uses JOINs to system Views instead of using the built-in meta-data functions (e.g. OBJECT_ID), fully qualifying the object names with the 4-part name (beginning with the Linked Server name) should work just fine. And in fact, using the 4-part names is probably better anyway as it would allow you to parameterize the query, whereas OPENQUERY requires a string literal for the Query (which then requires Dynamic SQL to generate the query that calls OPENQUERY).

Hence, in either case, the following query should work:

SELECT ISNULL(sic.last_value, 0) AS [LastValueReserved],
ISNULL(sic.last_value, 0) + sic.increment_value AS [NextValue]
FROM [ [ {linked_server_name}. ] {database_name}. ][sys].[identity_columns] sic
INNER JOIN [ [ {linked_server_name}. ] {database_name}. ][sys].[objects] so
ON so.[object_id] = sic.[object_id]
INNER JOIN [ [ {linked_server_name}. ] {database_name}. ][sys].[schemas] ss
ON ss.[schema_id] = so.[schema_id]
WHERE ss.[name] = N'{schema_name}'
AND so.[name] = N'{table_name}';


Of course, if you are getting this info for the current database, then you don't need to fully qualify those system view names with {database_name}..

Please note:

• I labeled the first field with the qualifier of "Reserved" because there are reasons that Identity values can be reserved but never used (i.e. not found in the table). Examples being: if an INSERT statement fails, or if the INSERT is done within a Transaction that gets rolled-back, or if the server restarts (in which case you sometimes see a jump of approximately 1000; this behavior has been asked and documented on here and other sites).

• ISNULL(sic.last_value, 0) is used because prior to the first INSERT operation, the value in the last_value column is NULL.

StackExchange Database Administrators Q#138889, answer score: 2