It is often required to RESEED the database tables with new range of identifiers especially when the table’s Primary Key column is designed with data types such as BIGINT/INT.
The following script allows us to RESEED the database tables with the new SEED value for all the user tables with identity column.
-- Declaration statement to capture the new SEED value
DECLARE @DesiredSeed VARCHAR(20)
SET @DesiredSeed = '5000000000'
DECLARE @TableName VARCHAR(256), @SQLStatement VARCHAR(1000)
-- Cursor to get the list of all user tables which have identity columns
DECLARE curIdentityTables CURSOR
FOR
SELECT b.TABLE_SCHEMA +'.'+ OBJECT_NAME (a.[id])
FROM sysobjects a INNER JOIN INFORMATION_SCHEMA.COLUMNS b
ON OBJECT_NAME(a.id) = b.TABLE_NAME and b.ORDINAL_POSITION = 1
WHERE OBJECTPROPERTY (a.[id], 'IsUserTable') = 1
and OBJECT_NAME (a.[id]) <> 'dtproperties'
and OBJECTPROPERTY (a.[id], 'TableHasIdentity') = 1
ORDER BY a.[name]
--Open the cursor to loop through the table list and reseed it
OPEN curIdentityTables
FETCH NEXT FROM curIdentityTables INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLSTatement = 'DBCC CHECKIDENT (''' + @TableName + ''', RESEED, ' + @DesiredSeed + ')'
EXEC (@SQLSTatement)
FETCH NEXT FROM curIdentityTables INTO @TableName
END
CLOSE curIdentityTables
DEALLOCATE curIdentityTables
2 comments:
north face, hogan, coach outlet, air force, hollister pas cher, michael kors, coach outlet, lululemon, burberry, sac guess, nike roshe, nike free, true religion jeans, air jordan pas cher, lacoste pas cher, ralph lauren pas cher, michael kors, kate spade outlet, ralph lauren uk, michael kors, new balance pas cher, true religion jeans, ray ban uk, nike roshe run, converse pas cher, michael kors, hermes, mulberry, vans pas cher, louboutin pas cher, true religion outlet, ray ban pas cher, nike air max, oakley pas cher, air max, timberland, nike free run uk, kate spade handbags, nike air max, hollister, tn pas cher, replica handbags, nike air max, vanessa bruno, abercrombie and fitch, sac longchamp, true religion jeans, nike blazer, coach purses, longchamp pas cher, north face
ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03
Post a Comment