Get all SQL Server tables that have IDENTITY enabled

Here is a useful script. If you need to find out tables that have Identity property enabled, you can simply query the sys.tables Catalog View combining with OBJECTPROPERTY function.

view plainprint?

1. USE AdventureWorks2014;  
2. GO  
3.   
4. SELECT SCHEMA_NAME(schema_id) + '.' + name TableName   
5. FROM sys.tables  
6. WHERE OBJECTPROPERTY(object_id, 'TableHasIdentity') = 1;  

USE AdventureWorks2014;
GO

SELECT SCHEMA_NAME(schema_id) + '.' + name TableName 
FROM sys.tables
WHERE OBJECTPROPERTY(object_id, 'TableHasIdentity') = 1;

Remember, this OBJECTPROPERTY function can be used to check many properties related to SQL Server objects. See this MSDN page for more details on it;

https://msdn.microsoft.com/en-us/library/ms176105.aspx