所有表的SQL Server重置身份增量 [英] SQL Server Reset Identity Increment for all tables

问题描述

基本上，我需要将所有表的Identity Increment重置为其原始值. 在这里，我尝试了一些代码，但是失败了.

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

链接中的实际代码:

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: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '('.

如何修复该SQL或将所有表的身份重置为其原始身份?

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

推荐答案

您是否有很多没有种子且增量为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是一个未公开的文档，但存储非常方便，它可以对数据库中的所有表执行给定命令.

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

如果需要绝对精确，请使用此语句-它会生成SQL语句列表，以将所有表重新设置为原始SEED值:

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)

这篇关于所有表的SQL Server重置身份增量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！