Comment déterminez-vous quelles tables SQL ont une colonne d'identité par programmation

Une autre méthode possible pour SQL Server, qui dépend less des tables système (susceptibles d'être modifiées, de version en version), consiste à utiliser les vues INFORMATION_SCHEMA:

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 

sys.columns.is_identity = 1

par exemple,

 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 

Un autre moyen (pour 2000 / 2005/2012/2014):

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

REMARQUE: table_name_here doit être schema.table , sauf si le schéma est dbo .

En SQL 2005:

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

Cette requête semble faire l'affaire:

 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') 

Voici une version de travail pour MSSQL 2000. J'ai modifié le code 2005 trouvé ici: 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's 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; /* Resortingeve 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; 

Liste des tables sans colonne Identité basée sur la réponse de Guillermo :

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

Je pense que cela fonctionne pour 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 

Cela a fonctionné pour moi en utilisant 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 

Utilisez ceci :

 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 

Cela a fonctionné pour SQL Server 2005, 2008 et 2012. J'ai trouvé que sys.identity_columns ne contenait pas toutes mes tables avec des colonnes d'identité.

 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; 

En regardant la page de documentation, la colonne d'état peut également être utilisée. Vous pouvez également append l'identifiant en quatre parties et cela fonctionnera sur différents 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/fr-fr/library/ms186816.aspx