I need to write a sproc to capture which table is on the verge of having run
out of identity values depending on the data type.. Theres 2 issues here.

For eg: say if table has a col of identity property and a datatype of
smallint. So if we start wuth identity(1,1) and the value reaches around
30,000 we would like to be warned as the max value it would support is
32,767

Secondly, say we decided to reseed the identity once it reached 30,000 to
now start at -32,768 and prior to doing that the min value in that identity
col is 1, we would definitely like to be warned somewhere close to -5000
before it reaches 1 again and there is already a row with value 1.. This is
just examples...

But i would like maybe the outpit of the sproc to return values as below
based upon the example above. The column X indicates
CurrentMinValueFollowingCurrentIdentity.If there is none then I think value
X would be the same value as CurrentIdentValue .. Hope Im making sense

TableName IdentColName IdentColDataType MinValueforDataType
MaxValueforDataType CurrentIdentValue X
T1 C1
tinyint -32678
2767 -5000 1

Hmmm.. what about storage of numeric vs int ? Worth a thought.. but some of
our tables are already in the second portion of my initial message.. Had to
reseed and stuff..

[quoted text, click to view]

Seems like a lot of trouble to little benefit. Why not just go through and
select the appropriate datatype for each IDENTITY so that there's no chance
of running out of values during the lifetime of the system. NUMERIC can go
up to 10^38. Surely that's enough for you?

--
David Portas
SQL Server MVP
--

I just mentioned NUMERIC as an example. The max value of an INT is over 2
billion. For most applications that is more than adequate.

Monitoring the problem doesn't solve it. You still need to alter the column
at some point so why not get it right to start with?

--
David Portas
SQL Server MVP
--