SQL Server Reset Identity Increment for all tables

Question

Basically I need to reset Identity Increment for all tables to its original. Here I tried some code, but it fails.

http://pastebin.com/KSyvtK5b

Actual code from link:

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 

Error message:

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

How can I either fix that SQL or reset identity for all tables to its original?

Answer 1

Do you have lots of tables which do not have a seed and increment of 1 ??

If not (by default, all tables have that), use this code:

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

MSforeachtable is an undocumented, but extremely handy stored proc which executes a given command against all tables in your database.

If you need to be absolutely exact, use this statement - it will generate a list of SQL statements to reseed all tables to their original SEED value:

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' 

Grab that last column in the output, and execute those statements and you're done! :-)

(inspired by a blog post by Pinal Dave)