SQL Server Reset Identity Increment pour toutes les tables

fondamentalement, je dois réinitialiser L'incrément D'identité pour toutes les tables à son original. Ici, j'ai essayé un code, mais il échoue.

http://pastebin.com/KSyvtK5b

code actuel du lien: 

USE World00_Character
GO

-- Create a cursor to loop through the System Ojects and get each table name
DECLARE TBL_CURSOR CURSOR
-- Declare the SQL Statement to cursor through
FOR ( SELECT Name FROM Sysobjects WHERE Type='U' )

-- Declare the @SQL Variable which will hold our dynamic sql
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '';
-- Declare the @TblName Variable which will hold the name of the current table
DECLARE @TblName NVARCHAR(MAX);

-- Open the Cursor
OPEN TBL_CURSOR

-- Setup the Fetch While that will loop through our cursor and set @TblName
FETCH NEXT FROM TBL_CURSOR INTO @TblName
-- Do this while we are not at the end of the record set
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- Appeand this table's select count statement to our sql variable
SET @SQL = @SQL + ' ( SELECT '''+@TblName+''' AS Table_Name,COUNT(*) AS Count FROM '+@TblName+' ) UNION';

-- Delete info
EXEC('DBCC CHECKIDENT ('+@TblName+',RESEED,(SELECT IDENT_SEED('+@TblName+')))');

-- Pull the next record
FETCH NEXT FROM TBL_CURSOR INTO @TblName
-- End the Cursor Loop
END

-- Close and Clean Up the Cursor
CLOSE TBL_CURSOR
DEALLOCATE TBL_CURSOR

-- Since we were adding the UNION at the end of each part, the last query will have
-- an extra UNION. Lets  trim it off.
SET @SQL = LEFT(@SQL,LEN(@SQL)-6);

-- Lets do an Order By. You can pick between Count and Table Name by picking which
-- line to execute below.
SET @SQL = @SQL + ' ORDER BY Count';
--SET @SQL = @SQL + ' ORDER BY Table_Name';

-- Now that our Dynamic SQL statement is ready, lets execute it.
EXEC (@SQL);
GO

message d'erreur: 

Error: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.

Comment puis-je soit corriger ce SQL ou réinitialiser l'identité pour toutes les tables à son origine?

22
demandé sur wonea 2010-03-02 19:26:39

9 réponses

avez-vous beaucoup de tables qui n'ont pas une graine et un accroissement de 1 ??

si non (par défaut, toutes les tables ont cela), utilisez ce code: 

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED, 1)'

MSforeachtable est un proc stocké non documenté, mais extrêmement pratique, qui exécute une commande donnée contre toutes les tables de votre base de données.

si vous avez besoin d'être absolument exact, Utilisez cette instruction - elle générera une liste D'instructions SQL pour réensemencer toutes les tables à leur valeur initiale: 

SELECT 
    IDENT_SEED(TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_NAME) AS Current_Identity,
    TABLE_NAME,
    'DBCC CHECKIDENT(' + TABLE_NAME + ', RESEED, ' + CAST(IDENT_SEED(TABLE_NAME) AS VARCHAR(10)) + ')'
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'

saisissez cette dernière colonne dans la sortie, et exécutez ces instructions et vous avez terminé! :- )

(inspiré par un blog post par Pinal Dave)

56
répondu marc_s 2013-02-07 12:17:38

légère modification de la réponse marc_s. 

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'

ces guillemets autour du ? le caractère est important. Cette déclaration fera que SQL Server recalculera automatiquement la valeur d'identité suivante pour chaque table.

11
répondu AgileJon 2010-05-18 14:47:38

légère variation qui gère les schémas un peu mieux... 

SELECT 
    IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS Seed,
    IDENT_INCR(TABLE_SCHEMA+'.'+TABLE_NAME) AS Increment,
    IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME) AS Current_Identity,
    TABLE_SCHEMA+'.'+TABLE_NAME,
    'DBCC CHECKIDENT('''+TABLE_SCHEMA+'.'+TABLE_NAME+''', RESEED, '+CAST(IDENT_SEED(TABLE_SCHEMA+'.'+TABLE_NAME) AS VARCHAR(10))+')'
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), 'TableHasIdentity') = 1
AND TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA, TABLE_NAME
5
répondu Dog Ears 2010-12-02 15:27:29

faites attention en utilisant cette commande si votre table contient des données toutes vos nouvelles insertions résulteront erreur dupliquée 

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED,1)'

pour résoudre le problème, vous devez exécuter ce après que 

exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT(''?'', RESEED)'

cela va réinitialiser la graine à la dernière identité de colonne si les données existent

5
répondu Alireza Rinan 2015-06-25 22:13:20

une autre façon d'utiliser sp_MSForEachTable et de vérifier si la Table A ou non une valeur d'identité avant de la Réinitialiser: 

EXEC sp_MSForEachTable '
 Print ''?''
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
  DBCC CHECKIDENT (''?'', RESEED, 0)
 else
  Print ''Table does not have an identity value''
'

NOTE: si vous voulez que la valeur d'identité commence à 1, alors la commande DBCC devrait utiliser CHECKIDENT (''?'', RESEED, 0) et non CHECKIDENT (''?'', RESEED, 1) comme indiqué dans certaines des réponses. MS SQL Server documentation :

l'exemple suivant force valeur actuelle de l'identité AddressTypeID colonne dans la table AddressType à une valeur de 10. Parce que la table a des lignes existantes, la ligne suivante insérée utilisera 11 comme valeur, c'est-à-dire la nouvelle valeur incrémentielle actuelle définie pour valeur de la colonne plus 1 

USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO
3
répondu JasonMcF 2014-01-24 13:04:01

une méthode facile peut être d'utiliser la commande sp_MSforeachtable, une commande non documentée, mais relativement bien connue qui regarde au-dessus de vos tables.

2
répondu SQL 2010-03-02 16:37:20

pour ne réensemencer que les tables avec une colonne d'identité, vous pouvez utiliser le script suivant. Il utilise également sp_MSforeachtable mais en tenant compte des tableaux corrects. 

EXEC sp_MSforeachtable '
IF (SELECT COUNT(1) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = ''BASE TABLE'' 
    AND ''[''+ TABLE_SCHEMA + ''].['' + TABLE_NAME + '']'' = ''?'' 
    AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ''TableHasIdentity'') = 1) > 0 
BEGIN
    DBCC CHECKIDENT (''?'', RESEED, 1)
END'
2
répondu 7ynk3r 2012-01-29 11:10:48

utilisez le code ci-dessous, 

CREATE TABLE #tmptable
(
    [seednvalue] int not null,
    [tablename] [nvarchar] (100) NULL
) 


declare @seedvalue AS INT
DECLARE @tablename AS VARCHAR(100)

Declare #tablesIdentityCursor CURSOR
    for 
    SELECT 
    IDENT_CURRENT(TABLE_NAME)+1 AS Current_Identity,
    TABLE_NAME
    FROM INFORMATION_SCHEMA.TABLES
    WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'  --AND TABLE_NAME='test11'

delete from #tmptable
Open #tablesIdentityCursor
FETCH NEXT FROM #tablesIdentityCursor into @seedvalue, @tablename
WHILE @@FETCH_STATUS = 0 BEGIN

    Insert into #tmptable Select @seedvalue , @tablename   
    DBCC CHECKIDENT (@tablename, reseed, @seedvalue) 
    FETCH NEXT FROM #tablesIdentityCursor into @seedvalue, @tablename
END
CLOSE #tablesIdentityCursor
DEALLOCATE #tablesIdentityCursor
SELECT * FROM #tmptable
DROP TABLE #tmptable
0
répondu Mian Ghous 2012-03-09 16:08:33

(je reprends ma réponse de cette autre page SO)

peut-être que la façon la plus simple (aussi fou que cela puisse paraître et aussi code-puant que cela puisse paraître) est d'exécuter DBCC CHECKIDENT deux fois comme ceci: 

-- sets all the seeds to 1
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED, 1)'

-- run it again to get MSSQL to figure out the MAX/NEXT seed automatically
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'')'

fait.

si vous voulez, vous pouvez l'exécuter une fois de plus pour voir à quoi toutes les graines ont été placées: 

-- run it again to display what the seeds are now set to
exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'')'

C'est juste une façon créative de profiter du commentaire extrait de la documentation:

si la valeur actuelle d'identité pour une table est inférieure au maximum valeur d'identité stockée dans la colonne d'identité, elle est réinitialisée en utilisant le valeur maximale dans la colonne identité.

0
répondu 010110110101 2017-05-23 12:10:31