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

Question

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

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语句或将所有表的标识列重置为其原始值？

- DanSpd

Answer 1

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

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

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

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

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

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'

获取输出中的最后一列，执行这些语句，你就完成了！ :-)

(灵感来自 Pinal Dave 的博客文章)

- marc_s

---

我有大约80个默认种子为101的表，还有50个种子为10001和另外50个种子为1的表。所以我尝试在SQL中使用循环，但会尝试使用PHP循环，这可能更容易些。 - DanSpd

@DanSpd：我更新了我的答案，使用你的终极和绝对精确的方法来完成这个！ :-) 享受吧。 - marc_s

非常感谢。这让我的工作变得更容易了 :) 爱你们 - DanSpd

如果您的所有表格中并非都有标识列，您可能需要修改MSforeachtable调用：“'exec sp_MSforeachtable @command1 ='DBCC CHECKIDENT(''?'', RESEED, 1)', @whereand ='AND EXISTS（SELECT 1 FROM sys.columns WHERE object_id = o.id AND is_identity = 1）'“ - KevD

这是一个很好的答案，快速简便，但最有可能将标识重置为从2开始，如果表已经删除了行（通常情况），则需要使用0作为重新生成种子值，您可以在此处找到更多关于此问题的信息 https://dev59.com/uHRB5IYBdhLWcg3w6LR2。 - Hamid Heydarian

非常感谢，这非常有用。 - Mohammad Sadegh Mazaheri

Answer 2

对marc_s的答案进行了轻微修改。

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

那个问号周围的单引号很重要。该语句将使SQL Server自动重新计算每个表的下一个标识值。

- AgileJon

---

这对我来说没有将IDENTITY重置为原始起始值。它似乎根本没有改变。当仅使用表名作为参数运行CHECKIDENT时，它报告与之前相同的“当前标识”数字。指定第三个参数就可以解决问题了。我正在运行SQL Server 2012。 - bitoolean

Answer 3

稍微改动一下，更好地处理模式...

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   

- Dog Ears

Answer 4

使用该命令时要小心，如果您的表中包含数据，则您的所有新插入将导致重复错误。

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

为了解决这个问题，你需要在此之后运行这个。

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

如果数据存在，这将重置种子到最后一列的标识符。

- Alireza Rinan

Answer 5

要重新生成仅带有标识列的表，您可以使用下一个脚本。 它还利用了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'

- 7ynk3r

Answer 6

使用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''
'

注意: 如果想让标识列的值从1开始，则DBCC命令应使用 CHECKIDENT (''?'', RESEED, 0) 而不是一些答案中提到的 CHECKIDENT (''?'', RESEED, 1)。引用自MS SQL Server文档:

以下示例将AddressType表中AddressTypeID列的当前标识值强制更改为10。由于该表具有现有行，因此下一行插入将使用11作为值，即定义为列值加1的新当前增量值。

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

- JasonMcF

---

非常好，我只是在打印语句中加入了一个 ? ，这样我就知道哪个表没有标识。 - Edwin Stoteler

Answer 7

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

- SQL

Answer 8

declare @sqlcommand varchar(max) = '';

SELECT 
    @sqlcommand =@sqlcommand 
    + 'select @max=isnull(max('+i.name+'),0) from '+t.name+';'
    + 'DBCC CHECKIDENT ('''+t.name+''', RESEED, @max);' 
FROM sys.tables AS t
inner join sys.identity_columns as i on i.[object_id] = t.[object_id]

set @sqlcommand = 'declare @max int;'+@sqlcommand;
EXEC(@sqlcommand)

- Eduardo Santa

Answer 9

我们的工作场所有一个定期的停电，出于某种原因，我的一些表格的标识列跳了1000。 我修改了这段代码，制作了一个选择字符串列表，以显示列的当前值与当前标识的比较。 然后，我将行复制并粘贴到另一个选择中。

    SELECT 
        Replace(CurrentValue,'FROM',','+ CAST(IDENT_CURRENT(TABLE_SCHEMA+'.'+TABLE_NAME)AS VARCHAR) + ' AS Current_Identity FROM')
    FROM 
        INFORMATION_SCHEMA.TABLES T left join (select 'UNION SELECT ''DBCC CHECKIDENT(''''['+TABLE_SCHEMA+'].'+TABLE_NAME+''''', RESEED, ''+CAST( MAX('+COLUMN_NAME+') as VARCHAR)+'')'' as DBCC_String,MAX('+COLUMN_NAME+') as CurrentValue FROM [' + TABLE_SCHEMA+'].'+TABLE_NAME as CurrentValue,TABLE_SCHEMA+'.'+TABLE_NAME as TTABLE
        from INFORMATION_SCHEMA.COLUMNS
        where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1 
        ) as TT on T.TABLE_SCHEMA+'.'+T.TABLE_NAME = TT.TTABLE
    WHERE 
        OBJECTPROPERTY(OBJECT_ID(TABLE_SCHEMA+'.'+TABLE_NAME), 'TableHasIdentity') = 1
    AND TABLE_TYPE = 'BASE TABLE'
    ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME  

然后将所选列复制到内部。

    select DBCC_String,CurrentValue,Current_Identity from
    (
     *Paste Here and delete the first "UNION"*
    ) as T where CurrentValue < Current_Identity

然后，您可以从表格中复制DBC_String列以修改所需的表格。

- Hack.Sign

Answer 10

请使用以下代码：

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

- Mian Ghous