Tables with Identity columns

How to find table which use identity column in SQL Server using TSQL.

SELECT [Table] = s.[Name] + N'.' + t.[Name] 
  FROM [sys].tables t
  JOIN [sys].schemas  s ON t.[schema_id] = s.[schema_id]
  WHERE EXISTS
  (
      SELECT * FROM [sys].identity_columns i
      WHERE i.[object_id] = t.[object_id]
  );
--You can easily find the tables without identity columns by just using NOT EXISTS in the WHERE clause.

To list all identity column

select COLUMN_NAME, TABLE_NAME, *
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
--order by TABLE_NAME 

To list all identity column with table name, last value, seeding value incremental value, replication stetting, etc.

SELECT   OBJECT_NAME(object_id) AS [object], name,
  seed_value,
  increment_value,
  last_value,
  is_not_for_replication
FROM sys.identity_columns;

Re-assign the identity of all the tables.