How To Identify Tables that has identity columns in a database? and what are incremental values of these identitity columns?(IDENT_INCR Function)

dbtut

6 years ago

With the IDENT_INCR function we can find the incremental value of the identity column in a table. If there is no Identity column in the table, the result will return NULL.

USE AdventureWorks2012; 
GO 
SELECT IDENT_INCR('[Person].[Person]') AS Incremental_Value
GO 
SELECT IDENT_INCR('[Production].[Product]') AS Incremental_Value
GO

USE AdventureWorks2012;

SELECT IDENT_INCR('[Person].[Person]') AS Incremental_Value

SELECT IDENT_INCR('[Production].[Product]') AS Incremental_Value

If you want to identify all identity column’s incremental value in the database, you should use the below script:

SELECT
  [Name] = '['+s.name+ +'].['+ t.name+']',
  IDENT_INCR('['+s.name+ +'].['+ t.name+']')  AS Incremental_Value
FROM sys.schemas AS s
INNER JOIN sys.tables AS t
  ON s.[schema_id] = t.[schema_id]
WHERE EXISTS
(
  SELECT 1 FROM sys.identity_columns
    WHERE [object_id] = t.[object_id]
);

SELECT

[Name] = '['+s.name+ +'].['+ t.name+']',

IDENT_INCR('['+s.name+ +'].['+ t.name+']') AS Incremental_Value

FROM sys.schemas AS s

INNER JOIN sys.tables AS t

ON s.[schema_id] = t.[schema_id]

WHERE EXISTS

(

SELECT 1 FROM sys.identity_columns

WHERE [object_id] = t.[object_id]

);