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?
1 Answer
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 theINSERT
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 firstINSERT
operation, the value in thelast_value
column isNULL
.