SQL Server Reset Идентичность для всех таблиц

В принципе мне нужно reset Increment 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 select count statement to our sql variable
SET @SQL = @SQL + ' ( SELECT '''[email protected]+''' AS Table_Name,COUNT(*) AS Count FROM '[email protected]+' ) UNION';

-- Delete info
EXEC('DBCC CHECKIDENT ('[email protected]+',RESEED,(SELECT IDENT_SEED('[email protected]+')))');

-- 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 или reset для всех таблиц в его оригинале?

Спасибо

Ответы

Ответ 1

У вас есть много таблиц, у которых нет семени и приращения 1?

Если нет (по умолчанию все таблицы имеют это), используйте этот код:

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

MSforeachtable - недокументированный, но чрезвычайно удобный хранимый процесс, который выполняет заданную команду против всех таблиц в вашей базе данных.

Если вам нужно быть абсолютно точным, используйте этот оператор - он сгенерирует список операторов 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'

Возьмите последний столбец на выходе и выполните эти инструкции, и все готово!: -)

(вдохновил сообщение в блоге от Pinal Dave)

Ответ 2

Незначительная настройка ответа marc_s.

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

Те одиночные кавычки вокруг? характер важны. Это утверждение заставит SQL Server автоматически пересчитывать следующее значение идентификации для каждой таблицы.

Ответ 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

Ответ 4

Будьте осторожны при использовании этой команды, если ваша таблица содержит данные, все ваши новые вставки приведут к дублированию ошибки.

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

чтобы решить проблему, необходимую для запуска этого после этого

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

это будет reset семя до последнего идентификатора столбца, если данные существуют

Ответ 5

Легким методом может быть использование команды sp_MSforeachtable, недокументированной, но относительно хорошо известной команды, которая просматривает ваши таблицы.

Ответ 6

Чтобы повторно загружать ТОЛЬКО таблицы с столбцом идентификации, вы можете использовать следующий script. Он также использует 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'

Ответ 7

Другой способ использования 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) not CHECKIDENT (''?'', RESEED, 1), как указано в некоторых ответах. Цитата из Документация MS SQL Server:

Следующий пример заставляет текущее значение идентификатора в AddressTypeID в таблице AddressType до значения 10. Поскольку таблица имеет существующие строки, в следующей вставленной строке будет использоваться 11 как значение, то есть новое значение текущего инкремента, определенное для значение столбца плюс 1

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

Ответ 8

Используйте приведенный ниже код,

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

Ответ 9

(я переписываю свой ответ с этой другой страницы SO)

Возможно, самый простой способ (как сумасшедший, как это звучит и как вонючий, как он выглядит) - просто запустить 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 (''?'')'

Готово.

Если вы хотите, вы можете запустить его еще раз, чтобы увидеть, на что были установлены все семплы:

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

Это просто творческий способ воспользоваться комментарием из документации:

Если текущее значение идентификатора для таблицы меньше максимального значения значение, хранящееся в столбце идентификации, reset, используя максимальное значение в столбце идентификации.