SQL Statement for getting the list of tables which has identity columns

SQL statements to get the list of tables which has IDENTITY columns.

Statement 1:

-------------

SELECT 

     QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName 

FROM sys.columns AS c  

    INNER JOIN 

    sys.tables AS t  

    ON t.[object_id] = c.[object_id] 

WHERE c.is_identity = 1

Results

--------

[dbo].[Table1]

[dbo].[Table2]

[dbo].[Table3]

Statement 2:

-------------

SELECT T.NAME AS TableName FROM sys.columns AS c

    INNER JOIN sys.tables AS t

ON t.[object_id] = c.[object_id] WHERE c.is_identity = 1

Results

--------

Table1

Table2

Table3