SQL Server 重置所有表的标识增量

SQL Server Reset Identity Increment for all tables

提问by DanSpd

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

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

http://pastebin.com/KSyvtK5b

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?

如何修复该 SQL 或将所有表的标识重置为其原始值？

回答by marc_s

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

你有很多没有种子和增量 1 的表吗？

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

如果没有（默认情况下，所有表都有），请使用以下代码：

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

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

MSforeachtable是一个未记录但非常方便的存储过程，它对数据库中的所有表执行给定的命令。

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:

如果您需要绝对准确，请使用此语句 - 它会生成一个 SQL 语句列表，将所有表重新设定为其原始 SEED 值：

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 postby Pinal Dave)

（灵感来自Pinal Dave的博客文章）

回答by AgileJon

Slight tweak on marc_s answer.

对 marc_s 答案稍作调整。

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

Those single-quotes around the ? character are important. That statement will cause SQL Server to automatically recalculate the next identity value for each table.

周围的那些单引号？性格很重要。该语句将导致 SQL Server 自动重新计算每个表的下一个标识值。

回答by Dog Ears

Slight variation that handles Schemas a bit better...

稍微处理架构更好一点的变化......

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   

回答by Alireza Rinan

Be careful when using this command if your table contains data all your new inserts will result duplicate error

使用此命令时要小心，如果您的表包含数据，所有新插入都会导致重复错误

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

to solve the problem you need to run this after that

要解决您需要在此之后运行的问题

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

this will reset the seed to the last column identity if the data exists

如果数据存在，这会将种子重置为最后一列标识

回答by JasonMcF

Another way of using sp_MSForEachTableand checking whether or not the Table has an identity value before resetting it:

sp_MSForEachTable在重置表之前使用和检查表是否具有标识值的另一种方法：

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

NOTE:If you want the identity value to start at 1 then the DBCC command should use CHECKIDENT (''?'', RESEED, 0)not CHECKIDENT (''?'', RESEED, 1)as indicated in some of the answers. Quote from MS SQL Server documentation:

注意：如果你想标识值从1开始，则DBCC命令应该使用CHECKIDENT (''?'', RESEED, 0)没有CHECKIDENT (''?'', RESEED, 1)在一些问题的答案表示。引自MS SQL Server 文档：

The following example forces the current identity value in the AddressTypeID column in the AddressType table to a value of 10. Because the table has existing rows, the next row inserted will use 11 as the value, that is, the new current increment value defined for the column value plus 1

下面的示例将 AddressType 表中 AddressTypeID 列中的当前标识值强制设为值 10。由于该表已存在行，因此插入的下一行将使用 11 作为值，即为定义的新当前增量值列值加 1

USE AdventureWorks2012;
GO
DBCC CHECKIDENT ('Person.AddressType', RESEED, 10);
GO

回答by SQL

An easy metod may be to use the sp_MSforeachtable command, an undocumented, but relatively well know command that looks over your tables.

一个简单的方法可能是使用 sp_MSforeachtable 命令，这是一个未公开但相对广为人知的命令，用于查看您的表。

回答by 7ynk3r

To reseed ONLY tables with an identity column you can use the next script. It also makes use of sp_MSforeachtablebut taking into account the correct tables.

要重新设定具有标识列的 ONLY 表，您可以使用下一个脚本。它还使用sp_MSforeachtable但考虑到正确的表格。

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'

回答by Mian Ghous

Use the below code,

使用下面的代码，

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

回答by 010110110101

(I'm reposting my answer from this other SO page)

（我正在从另一个 SO 页面重新发布我的答案）

Perhaps the easiest way (as crazy as this sounds and as code-smelly as it looks) is to just run DBCC CHECKIDENTtwice like this:

也许最简单的方法（听起来很疯狂，看起来很臭）就是DBCC CHECKIDENT像这样运行两次：

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

Done.

完毕。

If you want, you can run it once more to see what all the seeds were set to:

如果需要，您可以再次运行它以查看所有种子的设置：

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

This is just a creative way to take advantage of the comment from the documentation:

这只是利用文档中的评论的一种创造性方式：

If the current identity value for a table is less than the maximum identity value stored in the identity column, it is reset using the maximum value in the identity column.

如果表的当前标识值小于标识列中存储的最大标识值，则使用标识列中的最大值对其进行重置。