ejemplo - restart identity sql server 2008

Restablecimiento de SQL Server Incremento de identidad para todas las tablas

Question

Básicamente, necesito restablecer el Incremento de identidad para todas las tablas a su original. Aquí probé un código, pero falla.

http://pastebin.com/KSyvtK5b

Código actual del enlace:

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

Mensaje de error:

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

¿Cómo puedo corregir ese SQL o restablecer la identidad de todas las tablas a su original?

Answer 1

¿Tienes muchas tablas que no tienen una semilla y un incremento de 1?

Si no es así (por defecto, todas las tablas tienen eso), use este código:

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

MSforeachtable es un proceso almacenado no documentado pero extremadamente útil que ejecuta un comando determinado contra todas las tablas de su base de datos.

Si necesita ser absolutamente exacto, use esta declaración: generará una lista de sentencias SQL para reiniciar todas las tablas a su valor original de 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''

¡Agarre esa última columna en la salida, y ejecute esas declaraciones y listo! :-)

(inspirado en una entrada de blog de Pinal Dave)

Answer 2

Otra forma de usar sp_MSForEachTable y verificar si la Tabla tiene o no un valor de identidad antes de restablecerla:

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

NOTA: Si desea que el valor de identidad comience en 1, el comando DBCC debe usar CHECKIDENT (''''?'''', RESEED, 0) no CHECKIDENT (''''?'''', RESEED, 1) como se indica en algunas de las respuestas . Cita de la documentación de MS SQL Server :

El siguiente ejemplo fuerza el valor de identidad actual en la columna AddressTypeID en la tabla AddressType a un valor de 10. Como la tabla tiene filas existentes, la siguiente fila insertada usará 11 como valor, es decir, el nuevo valor de incremento actual definido para el valor de la columna más 1

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

Answer 3

Para reiniciar SOLO las tablas con una columna de identidad, puede utilizar el siguiente script. También hace uso de sp_MSforeachtable pero teniendo en cuenta las tablas correctas.

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

Answer 4

Tenga cuidado al usar este comando si su tabla contiene datos, todas sus nuevas inserciones resultarán en un error duplicado

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

para resolver el problema necesita ejecutar esto después de eso

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

esto restablecerá la semilla a la identidad de la última columna si existen los datos

Answer 5

Un ligero pellizco en la respuesta de marc_s.

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

Esas comillas simples alrededor del? Los personajes son importantes. Esa declaración hará que SQL Server vuelva a calcular automáticamente el siguiente valor de identidad para cada tabla.

Answer 6

Un método fácil puede ser usar el comando sp_MSforeachtable, un comando no documentado pero relativamente bien conocido que revisa sus tablas.

Answer 7

Utilice el siguiente código,

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

Answer 8

Variación leve que maneja los esquemas un poco mejor ...

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   

Answer 9

(Estoy reenviando mi respuesta de esta otra página SO)

Quizás la forma más fácil (por más loco que parezca y por el código que huele mal) es ejecutar DBCC CHECKIDENT dos veces así:

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

Hecho.

Si lo deseas, puedes ejecutarlo una vez más para ver a qué se configuraron todas las semillas:

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

Esta es solo una forma creativa de aprovechar el comentario de la documentación:

Si el valor de identidad actual para una tabla es menor que el valor de identidad máximo almacenado en la columna de identidad, se restablece utilizando el valor máximo en la columna de identidad.