SQLServer 重置表的自增值

1 简单语句

1.1 查询自增值

SELECT IDENT_CURRENT('TABLE_NAME')

1.2 重置自增值

DBCC CHECKIDENT('TABLE_NAME', reseed, 10086)  // 将表 TABLE_NAME 的自增值重置为 10086

DBCC CHECKIDENT('TABLE_NAME', reseed)   // 不指定第三个参数的话，则自动重置为当前自增列的最大值

1.3 查询当前数据库的所有表（不包括系统表）

select name from sysobjects 
where
    OBJECTPROPERTY(id, N'IsTable')=1 and
    OBJECTPROPERTY(id, N'IsMSShipped')=0

1.4 判断表是否存在自增列

SELECT OBJECTPROPERTY(OBJECT_ID('TABLE_NAME'), 'TableHasIdentity')

存在返回1，不存在返回0，如果 TABLE_NAME 表不存在则返回 NULL。

1.5 查询存在自增列的表以及其自增列名

（不过下述语句未过滤掉系统表，所以可能出现名字为 MSxxx 的表）

select COLUMN_NAME, TABLE_NAME
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_SCHEMA+'.'+TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

 

2 完整脚本

查询当前数据库中，所有存在自增列的表（排除系统表），并重置其自增值。

declare @sql varchar(1000)
declare @tablename varchar(100)

DECLARE tablenames CURSOR FOR 
    select name from sysobjects where
        OBJECTPROPERTY(id, N'IsTable')=1 and
        OBJECTPROPERTY(id, N'IsMSShipped')=0

OPEN tablenames 

FETCH NEXT FROM tablenames INTO @tablename

WHILE @@FETCH_STATUS=0
BEGIN

    IF ((SELECT OBJECTPROPERTY( OBJECT_ID(@tablename), 'TableHasIdentity')) = 1)
        BEGIN
        PRINT 'YES, 表存在自增字段 ' + @tablename
        
        Declare @id_val int
        Select @id_val=IDENT_CURRENT(@tablename)
        PRINT '当前自增值: ' + CAST(@id_val AS varchar)

        DBCC CHECKIDENT(@tablename, reseed)

        Select @id_val=IDENT_CURRENT(@tablename)
        PRINT '重置后自增值: ' + CAST(@id_val AS varchar)
        
        END
    ELSE
        PRINT 'NO, 表不存在自增字段 ' + @tablename

    PRINT ''

FETCH NEXT FROM tablenames INTO @tablename
END

CLOSE tablenames

DEALLOCATE tablenames