How do I easily find IDENTITY columns in danger of overflowing?

sql-server overflow maintenance identity-column information-schema
Keith Walton picture Keith Walton · Jan 5, 2012 · Viewed 13.3k times · Source

My database is getting old, and one of my biggest INT IDENTITY columns has a value around 1.3 billion. This will overflow around 2.1 billion. I plan on increasing it's size, but I don't want to do it too soon because of the number of records in the database. I may replace my database hardware before I increase the column size, which could offset any performance problems this could cause. I also want to keep an eye on all the other columns in my databases that are more than 50% full. It's a lot of tables, and checking each one manually is not practical.

This is how I am getting the value now (I know the value returned may be slightly out of date, but it's good enough for my purposes):

PRINT IDENT_CURRENT('MyDatabase.dbo.MyTable')

Can I use the INFORMATION_SCHEMA to get this information?

Answer

marc_s picture marc_s · Jan 5, 2012

You can consult the sys.identity_columns system catalog view:

SELECT     
    name,
    seed_value, increment_value, last_value
FROM sys.identity_columns

This gives you the name, seed, increment and last value for each column. The view also contains the data type, so you can easily figure out which identity columns might be running out of numbers soonish...

Related questions

SQL COUNT overflow

Here is my query: SELECT COUNT(*) FROM Similarities WHERE T1Similarity = 0 OR T2Similarity = 0 Here is the result: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int. The table has 4 billion rows. I don't except this …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More