As we prepare to move a database to a new environment, I needed a way to see what shape the database is in.
One of the things I wanted to check are the tables that have Identity Columns.
I basically wanted to see their uptake.
Here is a code that gets the last used Identity value for each table that has an identity Column.
/* IDENT_CURRENT (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms175098.aspx DBCC Check Ident http://msdn.microsoft.com/en-us/library/ms176057.aspx */ SELECT objectName = quotename(schema_name(tblO.schema_id)) + '.' + quotename(object_name(tblO.object_id)) , columName = tblC.[name] , [identityCurrent] = IDENT_CURRENT ( quotename(schema_name(tblO.schema_id)) + '.' + quotename ( object_name(tblO.object_id) ) ) , [lastIdentityValueAssigned] = tblIC.last_value FROM sys.objects tblO inner join sys.columns tblC ON tblO.object_id = tblC.object_id and tblC.is_identity=1 inner join sys.identity_columns tblIC on tblO.object_id = tblIC.object_id AND tblO.[type] in (N'U') order by [identityCurrent] desc
One always wishes Life was awash with easy pickings. But, even a code like this betrays a few simple assumptions.
Let us see if we can identify a few:
- If we issue truncate table requests, the system automatically resets this value
- Not so when we issue deletes. Even when all records were removed due to an unfiltered deletes, the code branch for deletes does not go through the problem of checking that the table is now empty and have the the identity value re-calibrated
Here is a code from SQLServerPlanet.com ( http://sqlserverplanet.com/dba/using-dbcc-checkident-to-reseed-a-table-after-delete ) that gets the current max value from a table and resets the identity value:
delete from dbo.tblIdentity; DECLARE @max_seed BIGINT set @max_seed = ISNULL( ( SELECT MAX(ID) FROM [dbo].[tblIdentity] ) ,0) -- use the current max as the seed DBCC CHECKIDENT('[dbo].[tblIdentity]', RESEED, @max_seed)
The code above merely tracks the last Assigned identity value and not quite the value that will be assigned when a new entry is created.
The SQL Server instance possibly considers other things when assigning a new value, but is easier to determine and expose last used values.