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

Question

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 1

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...