删除一个表的所有行，检查表中是否有标识列，将被重新播种。

****警告:所有行将被删除。＊＊＊＊＊

Create PROCEDURE ClearTableData 
    @TableName varchar(100)
AS
    EXEC ('ALTER TABLE '+@TableName+'  NOCHECK CONSTRAINT ALL ') 
    BEGIN TRY  
        EXEC ('Truncate Table'+@TableName )
        PRINT @TableName +' rows have trancated '
    END TRY  
    BEGIN CATCH  
        EXEC ('Delete From '+@TableName )
        PRINT @TableName +' rows have deleted '
    END CATCH  
    
    IF (OBJECTPROPERTY(OBJECT_ID(@TableName), 'TableHasIdentity'))  = 1 
            DBCC CHECKIDENT (@TableName, RESEED, 0) 

    EXEC ('ALTER TABLE '+@TableName+'  CHECK CONSTRAINT ALL ') 

GO

DBCC CHECKIDENT ('TestTable', RESEED, 0)
GO

其中0是标识起始值

用新id重置标识列

DECLARE @MAX INT
SELECT @MAX=ISNULL(MAX(Id),0) FROM [TestTable]

DBCC CHECKIDENT ('[TestTable]', RESEED,@MAX)

这是一个常见的问题，答案总是一样的:不要这样做。身份值应该被视为任意的，因此，没有“正确”的顺序。

对所有表使用这个sp:

补播“youtable”

在删除一条记录后(在触发程序中)

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

as

-- ********************* CREAR ESTA FUNCIÓN PRIMERO *******************************
/*
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=max(' + @CampoIdentidad + ') From ' + @Tabla 
declare @ParamDefinition nvarchar(max)
SET @ParamDefinition = '@ValorOut int OUTPUT'

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

--select @OutPutNum
set @OutPutNum  = coalesce(@OutPutNum ,0)
DBCC CHECKIDENT (@Tabla, RESEED, @OutputNum)

虽然大多数答案都建议RESEED到0，但很多时候我们只需要重新播种到下一个可用的Id

declare @max int
select @max=max([Id]) from [TestTable]
if @max IS NULL   --check when max is returned as null
  SET @max = 0
DBCC CHECKIDENT ('[TestTable]', RESEED, @max)

这将检查表并重置到下一个ID。