:::: MENU ::::

Analyze all identity column values in SQL Server database

  • Jul 20 / 2009
  • 0
Data Modeling and Database Design, Data Types, dbDigger, T-SQL Scripts

Analyze all identity column values in SQL Server database

Identity columns are created to auto generate keys for records. These keys are best suitable for primary keys. If any value crosses the max value for identity column data type then following over flow error may occur, as here in case of tiny int

Msg 8115, Level 16, State 1, Line 5
Arithmetic overflow error converting IDENTITY to data type tinyint.

Here is a script to generate list of all identity columns in database along with their data types and maximum value consumed

 
Use AdventureWorks  
 GO  
 SELECT  
 SCHEMA_NAME( OBJECTPROPERTY( c.OBJECT_ID, 'SCHEMAID' )) AS [SCHEMA NAME],  
 OBJECT_NAME( c.OBJECT_ID ) AS [TABLE NAME], c.NAME AS [COLUMN NAME],  
 t.name as [Column data Type],seed_value,  
 increment_value,last_value as CurrentMaxValue  
 FROM SYS.identity_COLUMNS c inner join sys.types t on c.user_type_id = t.user_type_id  
 WHERE COLUMNPROPERTY(OBJECT_ID, c.NAME, 'IsIdentity') = 1  
 AND last_value IS NOT NULL  
 order by last_Value Desc  
 GO  

Analyze identity columns in SQL Server database
Now you may analyze the list of identity columns. And any column that is near to fully consume its maximum value may easily be tracked here.

Consult us to explore the Databases. Contact us