syntaxhelp.com Your online cheat sheet!

A query that returns all the IDENTITY columns in a SQL Server Database

SELECT 
    OBJECT_NAME(object_id) AS TableName, 
    c.name AS ColumnName,
    t.name AS DataType
FROM sys.columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE is_identity = 1

/*
TableName            ColumnName           DataType
-------------------- -------------------- --------------------
Address              AddressID            int
ProductReview        ProductReviewID      int
TransactionHistory   TransactionID        int
AddressType          AddressTypeID        int
ProductSubcategory   ProductSubcategoryID int
AWBuildVersion       SystemInformationID  tinyint
BillOfMaterials      BillOfMaterialsID    int
...                  ...                  ...
*/

Retrieve the SEED, INCREMENT and LAST IDENTITY value of all tables in a database

WITH cte AS (
    SELECT
        s.name + '.' + t.name AS TableName,
        c.name AS ColumnName
    FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.columns c ON c.object_id = t.object_id
        AND c.is_identity = 1
)
SELECT 
    TableName,
    ColumnName,
    IDENT_SEED(TableName) AS Seed,
    IDENT_INCR(TableName) AS Increment,
    IDENT_CURRENT(TableName) AS LastIdentity
FROM cte
/*
TableName                      ColumnName                Seed        Increment   LastIdentity
------------------------------ ------------------------- ----------- ----------- ------------
Person.Address                 AddressID                 1           1           32521
Production.ProductReview       ProductReviewID           1           1           4
Production.TransactionHistory  TransactionID             100000      1           213442
Person.AddressType             AddressTypeID             1           1           6
Production.ProductSubcategory  ProductSubcategoryID      1           1           37
...
...
*/