How do you determine which SQL tables have an identity column programmatically

Question

I want to create a list of columns in SQL Server 2005 with identity columns and a corresponding table in T-SQL.

The results will be something like this:

TableName, ColumnName

Answer 1

Another possible way to do this for SQL Server, which relies less on system tables (which can be changed from version to version), is to use INFORMATION_SCHEMA Views:

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 

Answer 2

sys.columns.is_identity = 1

eg.

 select o.name, c.name from sys.objects o inner join sys.columns c on o.object_id = c.object_id where c.is_identity = 1 

Answer 3

Another way (for 2000/2005/2012/2014):

 IF ((SELECT OBJECTPROPERTY( OBJECT_ID(N'table_name_here'), 'TableHasIdentity')) = 1) PRINT 'Yes' ELSE PRINT 'No' 

NOTE: table_name_here must be schema.table , if only a dbo .

Answer 4

In SQL 2005:

 select object_name(object_id), name from sys.columns where is_identity = 1 

Answer 5

This query seems to do the trick:

 SELECT sys.objects.name AS table_name, sys.columns.name AS column_name FROM sys.columns JOIN sys.objects ON sys.columns.object_id=sys.objects.object_id WHERE sys.columns.is_identity=1 AND sys.objects.type in (N'U') 

Answer 6

here is the working version for MSSQL 2000. I changed the 2005 code found here: http://sqlfool.com/2011/01/identity-columns-are-you-nearing-the-limits/

 /* Define how close we are to the value limit before we start throwing up the red flag. The higher the value, the closer to the limit. */ DECLARE @threshold DECIMAL(3,2); SET @threshold = .85; /* Create a temp table */ CREATE TABLE #identityStatus ( database_name VARCHAR(128) , table_name VARCHAR(128) , column_name VARCHAR(128) , data_type VARCHAR(128) , last_value BIGINT , max_value BIGINT ); DECLARE @dbname sysname; DECLARE @sql nvarchar(4000); -- Use an cursor to iterate through the databases since in 2000 there no sp_MSForEachDB command... DECLARE c cursor FAST_FORWARD FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN('master', 'model', 'msdb', 'tempdb'); OPEN c; FETCH NEXT FROM c INTO @dbname; WHILE @@FETCH_STATUS = 0 BEGIN SET @sql = N'Use [' + @dbname + ']; Insert Into #identityStatus Select ''' + @dbname + ''' As [database_name] , Object_Name(id.id) As [table_name] , id.name As [column_name] , t.name As [data_type] , IDENT_CURRENT(Object_Name(id.id)) As [last_value] , Case When t.name = ''tinyint'' Then 255 When t.name = ''smallint'' Then 32767 When t.name = ''int'' Then 2147483647 When t.name = ''bigint'' Then 9223372036854775807 End As [max_value] From syscolumns As id Join systypes As t On id.xtype = t.xtype Where id.colstat&1 = 1 -- this identifies the identity columns (as far as I know) '; EXECUTE sp_executesql @sql; FETCH NEXT FROM c INTO @dbname; END CLOSE c; DEALLOCATE c; /* Retrieve our results and format it all prettily */ SELECT database_name , table_name , column_name , data_type , last_value , CASE WHEN last_value < 0 THEN 100 ELSE (1 - CAST(last_value AS FLOAT(4)) / max_value) * 100 END AS [percentLeft] , CASE WHEN CAST(last_value AS FLOAT(4)) / max_value >= @threshold THEN 'warning: approaching max limit' ELSE 'okay' END AS [id_status] FROM #identityStatus ORDER BY percentLeft; /* Clean up after ourselves */ DROP TABLE #identityStatus; 

Answer 7

List of tables without an Identity column based on Guillermo's answer :

 SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_SCHEMA = 'dbo') AND (OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 0) ORDER BY TABLE_NAME 

Answer 8

I think this works for SQL 2000:

 SELECT CASE WHEN C.autoval IS NOT NULL THEN 'Identity' ELSE 'Not Identity' AND FROM sysobjects O INNER JOIN syscolumns C ON O.id = C.id WHERE O.NAME = @TableName AND C.NAME = @ColumnName 

Answer 9

This worked for me using Sql Server 2008:

 USE <database_name>; GO SELECT SCHEMA_NAME(schema_id) AS schema_name , t.name AS table_name , c.name AS column_name FROM sys.tables AS t JOIN sys.identity_columns c ON t.object_id = c.object_id ORDER BY schema_name, table_name; GO 

Answer 10

Use this:

 DECLARE @Table_Name VARCHAR(100) DECLARE @Column_Name VARCHAR(100) SET @Table_Name = '' SET @Column_Name = '' SELECT RowNumber = ROW_NUMBER() OVER ( PARTITION BY T.[Name] ORDER BY T.[Name], C.column_id ) , SCHEMA_NAME(T.schema_id) AS SchemaName , T.[Name] AS Table_Name , C.[Name] AS Field_Name , sysType.name , C.max_length , C.is_nullable , C.is_identity , C.scale , C.precision FROM Sys.Tables AS T LEFT JOIN Sys.Columns AS C ON ( T.[Object_Id] = C.[Object_Id] ) LEFT JOIN sys.types AS sysType ON ( C.user_type_id = sysType.user_type_id ) WHERE ( Type = 'U' ) AND ( C.Name LIKE '%' + @Column_Name + '%' ) AND ( T.Name LIKE '%' + @Table_Name + '%' ) ORDER BY T.[Name] , C.column_id 

Answer 11

This worked for SQL Server 2005, 2008, and 2012. I found that sys.identity_columns does not contain all of my tables with identity columns.

 SELECT a.name AS TableName, b.name AS IdentityColumn FROM sys.sysobjects a JOIN sys.syscolumns b ON a.id = b.id WHERE is_identity = 1 ORDER BY name; 

When looking at the documentation page, you can also use the status column. In addition, you can add an identifier of four parts, and it will work on different servers.

 SELECT a.name AS TableName, b.name AS IdentityColumn FROM [YOUR_SERVER_NAME].[YOUR_DB_NAME].sys.sysobjects a JOIN [YOUR_SERVER_NAME].[YOUR_DB_NAME].sys.syscolumns b ON a.id = b.id WHERE is_identity = 1 ORDER BY name; 

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