Answered by:
Aviod Identity Key column issues

-
Hi experts,
I want to develop mechanism which will give analysis of identity columns and fields whihc about to reach max, say tolernace of .1K records, then it would be enough around for user to check this pro-actively.
is there standard mechanism in SQL server 2008 whihc will tell about IDENTITY columsn.
Shivraj Patil.
Question
Answers
-
Hi Shivraj,
We can use @@IDENTITY to get the current value of the identity column, and then compare the current value with its max value. I made an example, you can refer to it:
DECLARE @IDENT table ( ID int identity (1,1), Name varchar(10) ) insert into @IDENT values ('a'); SELECT @@IDENTITY; select 2147483647-@@IDENTITY as NumLeft;
Allen Li
TechNet Community Support- Marked as answer by Allen Li - MSFTModerator Wednesday, July 24, 2013 7:57 AM
All replies
-
Read
http://msdn.microsoft.com/en-us/library/aa933196%28v=sql.80%29.aspx
Many Thanks & Best Regards, Hua Min
-
You can use the below script to get the details of identity column for the tables in the database. From the last value and datatype size you can calculate the column that will reach the max based on the tolerance...try this.
SELECT TableName = OBJECT_NAME(ic.OBJECT_ID) , ColumnName = c.name , OriginalSeed = seed_value , Step = increment_value , LastValue = last_value, DataType = t.name, SizeInBytes = t.max_length, t.precision, t.scale FROM sys.identity_columns ic INNER JOIN sys.columns c ON c.name = ic.name AND c.object_id = ic.OBJECT_ID INNER JOIN sys.types t ON t.system_type_id = c.system_type_id WHERE c.is_identity = 1
Krishnakumar S
-
-
Hi Shivraj,
We can use @@IDENTITY to get the current value of the identity column, and then compare the current value with its max value. I made an example, you can refer to it:
DECLARE @IDENT table ( ID int identity (1,1), Name varchar(10) ) insert into @IDENT values ('a'); SELECT @@IDENTITY; select 2147483647-@@IDENTITY as NumLeft;
Allen Li
TechNet Community Support- Marked as answer by Allen Li - MSFTModerator Wednesday, July 24, 2013 7:57 AM
-