For those still interested, I whipped up a script to get all of the maximum values for any columns within a database that are defined as integers. (It should be easy to modify if you're interested in, say, smallints or tinyints).
I'm not normally fond of cursors, but I figured it would be alright as this is a "toolbox" type of script, and not necessarily something that you would want to incorporate into a stored proc, for instance.
If anyone sees improvements that can be made, have at it.
I use the following script to get the tables having identity values....
SELECT table_name ,column_name ,IDENT_CURRENT(table_name) as Identity_value ,data_type FROM information_schema.columns WHERE COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity')=1ORDER BY table_name
Why bother with system tables, when you can get the same with schema view and functions?
I agree -- I don't like to query the sytem tables directly unless absolutely necessary. This is a slick solution -- obviously it didn't occur to me to check the system functions.
There's no need to "cheat" and use the syscolumns and sysobjects tables. While INFORMATION_SCHEMA doesn't have the information on identity columns, they OBJECTPROPERTY(<objectid>, 'TableHasIdentity') function tells you which tables have an identity column and you can use it in the WHERE clause to filter the tables from INFORMATION_SCHEMA. You can also use the COLUMNPROPERTY function to test each column.
I see that a previous post identified that also. Instead of keeping around a script, I try and code scripts into User-Defined Fucntions (UDF)s so that they're always in the database. Mine follows. You'll also need the function udf_SQL_DataTypeString from here:
AND (@Table_Name_Pattern IS NULL OR T.TABLE_NAME LIKE @Table_Name_Pattern) AND 1= OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME), 'TableHasIdentity') AND NOT 1=OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME) , 'IsMsShipped')ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME