SQL SERVER : List all the tables in a DB which contains Identity Columns
In Sql Server we have some system tables and Views which will help us to identify the information about our custom Tables we have Created.
I have many tables in my DB and I want to list all the tables that contain the identity column.
The System Tables which I used here are
SELECT SCHEMA_NAME(ST.[SCHEMA_ID])+'.'+OBJECT_NAME(ST.[OBJECT_ID])AS TableName, SIC.name As [Column Name], seed_value As [Seed Value], increment_value As [INCREMENT VALUE], ISNULL(last_value,0) AS [LAST IDENTITY VALUE] FROM SYS.TABLES ST JOIN SYS.IDENTITY_COLUMNS SIC ON ST.object_id = SIC.object_id
The above will display the result as below