CODEWITHSUNDEEP
sql-servert-sql
mohan111
mohan111

Reputation: 8865

reseeding Identity Values for selected Tables

how to reseed for selected basing on their last count.I have written a query to to reseed basing on last count.But for how to do 10 tables at a time .

declare @last int
select @last=max(empid) from Table_1
DBCC CHECKIDENT (Table_1, RESEED, @last)

but how to do for more than 10 tables or more tables...reseeding at one go basing on last count

Upvotes: 2

Views: 1248

Answers (3)

reticentKoala
reticentKoala

Reputation: 235

Thanks for a great answer that got me out of a hole. Here's my fuller version

DECLARE @idcol nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @sname nvarchar(max)
DECLARE @tname nvarchar(max)

DECLARE idtables CURSOR FOR 
SELECT t.name, s.name 
FROM sys.columns C
    INNER JOIN sys.tables T ON C.object_id = T.object_id
    INNER JOIN sys.schemas s ON S.schema_id = T.schema_id
WHERE is_identity = 1;

OPEN idtables

-- Fetch first table and schema name into the corresponding variables
FETCH NEXT FROM idtables INTO @tname, @sname
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Ensure no dirty values if table has no identity column
    SET @idcol = NULL
    -- Build 1st statement.
    -- Objective: get identity column name, if any.
    SET @sql = 'SELECT @idcolname = name
        FROM sys.columns
        WHERE object_id = OBJECT_ID(''' + @sname + '.' + @tname + ''')
            AND is_identity = 1'
    -- Run the statement and store the result into @idcol
    EXEC sp_executesql @sql,
                       N'@idcolname sysname OUTPUT',
                       @idcolname = @idcol OUTPUT
    IF @idcol IS NOT NULL
    BEGIN
        -- Time for the 2nd statement.
        -- Objective: find the maximum identity value and reseed the table.
        SET @sql = 'DECLARE @lastid int;
            SELECT @lastid = MAX(' + @idcol + ')
                FROM [' + @sname + '].[' + @tname + '];
            IF @lastid IS NOT NULL
                DBCC CHECKIDENT (''' + @sname + '.' + @tname + ''', RESEED, @lastid)'
        EXEC SP_EXECUTESQL @sql
    END
    FETCH NEXT FROM idtables INTO @tname, @sname
END

CLOSE idtables
DEALLOCATE idtables

Upvotes: 0

live-love
live-love

Reputation: 52366

You don't need select @last=max(empid) from Table_1

DBCC CHECKIDENT (Table_1, RESEED) will get the last id (if empid is an identity column).

This code will reseed all tables:

SET NOCOUNT ON
DECLARE @lcl_name VARCHAR(100)
DECLARE cur_name CURSOR FOR

SELECT TABLE_NAME 
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE' 

OPEN cur_name
FETCH NEXT FROM cur_name INTO @lcl_name
WHILE @@Fetch_status = 0
BEGIN

DBCC CHECKIDENT (@lcl_name, RESEED);

PRINT @lcl_name
FETCH NEXT FROM cur_name INTO @lcl_name
END
CLOSE cur_name
DEALLOCATE cur_name
SET NOCOUNT OFF

Upvotes: 0

ssarabando
ssarabando

Reputation: 3517

Iterate through the tables you want and then run the above commands per table.

You'll have to build the SQL statements dynamically.

For example, and for SQL Server 2008 R2 (you don't specify what you are using), this would do the trick:

DECLARE @tname SYSNAME, -- Will hold each table name
    @sname SYSNAME,     -- Will hold each table's schema name
    @idcol SYSNAME,     -- Will hold the name of identity column of each table
    @sql NVARCHAR(4000) -- To build each dynamic SQL statement

-- Declare a cursor to iterate through all table and schema names
-- of current database.
-- Add a WHERE clause here if needed.
DECLARE idtables CURSOR FOR
    SELECT name, SCHEMA_NAME(schema_id)
        FROM sys.tables

OPEN idtables

-- Fetch first table and schema name into the corresponding variables
FETCH NEXT FROM idtables INTO @tname, @sname
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Ensure no dirty values if table has no identity column
    SET @idcol = NULL
    -- Build 1st statement.
    -- Objective: get identity column name, if any.
    SET @sql = 'SELECT @idcolname = name
        FROM sys.columns
        WHERE object_id = OBJECT_ID(''' + @sname + '.' + @tname + ''')
            AND is_identity = 1'
    -- Run the statement and store the result into @idcol
    EXEC sp_executesql @sql,
                       N'@idcolname sysname OUTPUT',
                       @idcolname = @idcol OUTPUT
    IF @idcol IS NOT NULL
    BEGIN
        -- Time for the 2nd statement.
        -- Objective: find the maximum identity value and reseed the table.
        SET @sql = 'DECLARE @lastid int;
            SELECT @lastid = MAX(' + @idcol + ')
                FROM [' + @sname + '].[' + @tname + '];
            IF @lastid IS NOT NULL
                DBCC CHECKIDENT (''' + @sname + '.' + @tname + ''', RESEED, @lastid)'
        EXEC SP_EXECUTESQL @sql
    END
    FETCH NEXT FROM idtables INTO @tname, @sname
END

CLOSE idtables
DEALLOCATE idtables

Upvotes: 6

Related Questions