CODEWITHSUNDEEP
sql-server-2008
user194076
user194076

Reputation: 9017

reseed sql server identity columns

I reseed identity columns like this: 

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Query throws me an error if there's no identity in the table. But I still need to reseed identity for all tables in the database that have an identity. (And do not throw an error if there's no identity)

Upvotes: 6

Views: 1488

Answers (4)

John Son
John Son

Reputation: 31

I would just truncate the table if there are no Record in the table. This will reset the primary key.

TRUNCATE TABLE '?'

Upvotes: 0

R.Alonso
R.Alonso

Reputation: 1065

For any table:

ALTER PROCEDURE [dbo].[RESEED](@Tabla nvarchar(100))

as


/* 
create function  dbo.FN_EsIdentidad(@Tabla nvarchar(100), @Campo nvarchar(100))
    returns int
    as
    begin
        return columnproperty ( object_id ( @Tabla  ), @Campo , 'IsIdentity')
    end 
GO

*/


declare @CampoIdentidad nvarchar(100)

    SELECT @CampoIdentidad  = Column_Name
        --,dbo.FN_EsIdentidad(Table_Name, Column_name) as EsIdentidad,
        --table_name, column_name 
        FROM information_schema.columns
        where dbo.FN_EsIdentidad(Table_Name, Column_name) = 1 
        and Table_Name=@Tabla


declare @Sql nvarchar(max)
declare @OutPutNum int
set @Sql = 'Select MAX(' + @CampoIdentidad + ') From ' + @Tabla
--select @CampoIdentidad


set @sql = 'Select @ValorOut=COALESCE(max(' + @CampoIdentidad + '),0) From ' + @Tabla 
declare @ParamDefinition nvarchar(max)
SET @ParamDefinition = '@ValorOut int OUTPUT'

EXECUTE sp_executesql  
    @SQL 
    ,@ParamDefinition  
    ,@ValorOut = @OutPutNum OUTPUT;  

--select @SQL


DBCC CHECKIDENT (@Tabla, RESEED, @OutputNum)

> Blockquote

Upvotes: 0

Alex Hinton
Alex Hinton

Reputation: 1608

To reseed all tables to 0 that have an identity column in one line:

exec sp_MSforeachtable 'dbcc checkident(''?'', reseed, 0)', @whereand='and exists(select 1 from sys.columns c where c.object_id = o.id and is_identity = 1)'

Checking identity information: current identity value '33798', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Checking identity information: current identity value '3359', current column value '0'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280272

Are all the tables really empty?

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql = @sql + N'DBCC CHECKIDENT(''' 
    + QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))
    + '.' + QUOTENAME(OBJECT_NAME([object_id])) 
    + ''', RESEED, 0);' + CHAR(13) + CHAR(10)
    FROM sys.columns
    WHERE is_identity = 1;

PRINT @sql;
-- EXEC sp_executesql @sql;

Upvotes: 7

Related Questions