SQL Query to List Out All Identity Column

Once I want to get the list of all tables which contains the identity column. After some googling finally I got the sql query which return all the tables which has identity column. It will also return column name, the Seed Values, Increment Values and Current Identity Column value of the table.

[code:sql] SELECT TABLE_NAME, c.name AS ColumnName, IDENT_SEED(TABLE_NAME) AS Seed, IDENT_INCR(TABLE_NAME) AS Increment, IDENT_CURRENT(TABLE_NAME) AS Current_Identity FROM INFORMATION_SCHEMA.TABLES INNER JOIN sys.tables t ON t.name = TABLE_NAME INNER JOIN sys.columns c ON t.object_id = c.object_id AND c.is_identity = 1 WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘TableHasIdentity’) = 1 AND TABLE_TYPE = ‘BASE TABLE’ [/code]

On above query I use INFORMATION_SCHEMA.TABLES, an information schema view which provide an internal, system table-independent view of the SQL Server metadata. ‘OBJECTPROPERTY’ is a built in sql function which returns information about schema-scoped objects in the current database.