Sometimes you just need to do it. Reset a load of a identity columns in a database.
So here is how to do it. First you need to find the identity columns
This a case of using one of the information schema views contained in INFORMATION_SCHEMA. The piece of SQL you need is
SELECT COLUMN_NAME,TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME
This will give you back a list of all tables that have identity columns in the database. Now this can bring some unwanted tables as well, so it usually good to qualify the statement with a WHERE clause just to reduce the number of tables coming back. You can use the TABLE_SCHEMA(*) to sort out which schemas you want to adjust.
* Just be careful, sys.objects is the only real way to verify what schema the object belongs to.
So to reset the identity column you use the DBCC CHECKIDENT command in T-SQL and use the RESEED parameter. So to reset an identity column to 0 its
DBCC CHECKIDENT (‘TABLE_NAME’, RESEED, 0);
Now to put this all together and use a cursor to loop through the results from the first piece of code. Note we do not need the COLUMN_NAME in to update the identity column.
DECLARE @strSQL VARCHAR(1000)
DECLARE @Table VARCHAR(200)
DECLARE RowCounter CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME
OPEN RowCounter
FETCH NEXT FROM RowCounter
INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @strSQL = 'DBCC CHECKIDENT(''' + @Table + ''',RESEED,0)'
PRINT @strSQL
EXEC(@STRSQL)
FETCH NEXT FROM RowCounter
INTO @Table
END
CLOSE RowCounter
DEALLOCATE RowCounter
This will reset all your identity columns to 0. Just be aware this is not something you want to do unless your table is empty. In some cases it may be applicable to reset the seed to a higher value than the highest value in the column for maintenance purposes.
No comments:
Post a Comment