How to find current maximum identity seed and check remaining values based on data type

How to find current Identity value and check remaining values based on data type

Few days back I came across situation when one of the tablein production was INT with identity defined on it. However as we know the limitof INT is 2147483647 and when maximum value crossed 2147483647 then followingerror started showing up

Msg 8115,Level 16, State 1, Line 1

Arithmeticoverflow error converting IDENTITY to data type int.

Arithmetic overflow occurred.

Now I went ahead and changed it to BIGINT (Limit 9223372036854775807)and everything was fine after that.

So I decided to create an alert which will tell me if any ofthe columns is reaching the limit of the assigned data type. So I wrotefollowing SP

This SP has input parameter of threshold at which we wantthe alert to trigger. It will also log the entries in a logging table. Thetable will log following details

DATABASE_NAME

TABLE_NAME

COLUMN_NAME

DATA_TYPE

MAX_VALUE

REMAINING_VALUES

PERCENTLEFT

STATUS

SERVER_NAME

LOGDATE

1. SP will run on all the databases on a server
2. It will first find all tables with IDENTITY column
3. Find data type of the column
4. Find current maximum value
5. Compare the current maximum value with the limit of the data type of that column
6. Based on the threshold find out which tables are coming close to the upper limit
7. Load the data in a logging table
8. Email details of tables which are above the threshold

Here is the code for the SP

USE MYadminDB

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ES_IDENTITY_VALUE_CHECK]') AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].ES_IDENTITY_VALUE_CHECK

GO

create PROCEDUREES_IDENTITY_VALUE_CHECK @threshold decimal(3,2) = NULL

AS

SET NOCOUNT ON

--EXEC MYadminDB.DBO.ES_IDENTITY_VALUE_CHECK 0.90

Create Table #identityStatus

(

DATABASE_NAME varchar(256)

,TABLE_NAME varchar(256)

,COLUMN_NAME varchar(256)

,DATA_TYPE varchar(256)

,LAST_VALUE bigint

,MAX_VALUE bigint

);

Execute sp_msforeachdb '

Use[?];

INSERTINTO #identityStatus

select''?'' AS [DATABASE_NAME], T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,IDENT_CURRENT(T.TABLE_NAME) as LAST_VALUE

,Case

WhenC.DATA_TYPE = ''tinyint'' Then 255

WhenC.DATA_TYPE = ''smallint'' Then 32767

WhenC.DATA_TYPE = ''int'' Then 2147483647

WhenC.DATA_TYPE = ''bigint'' Then 9223372036854775807

EndAs [MAX_VALUE]

fromINFORMATION_SCHEMA.COLUMNS C

JOININFORMATION_SCHEMA.TABLES T

onT.TABLE_TYPE = ''BASE TABLE''

andT.TABLE_NAME = C.TABLE_NAME

ANDCOLUMNPROPERTY(object_id(T.TABLE_NAME), C.COLUMN_NAME, N''IsIdentity'') = 1

WHERET.TABLE_NAME NOT LIKE ''%_STG''

'

--DEFAULT THRESHOLD TO 85%

IF @thresholdIS NULL

SET@threshold = 0.90;

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IDENTITY_VALUE_WARNINGS]') AND type in (N'U'))

CREATE TABLE [dbo].[IDENTITY_VALUE_WARNINGS](

[DATABASE_NAME] [varchar](256) NULL,

[TABLE_NAME] [varchar](256) NULL,

[COLUMN_NAME] [varchar](256) NULL,

[DATA_TYPE] [varchar](256) NULL,

[MAX_VALUE] [bigint] NULL,

[REMAINING_VALUES] [bigint] NULL,

[PERCENTLEFT] [real] NULL,

[STATUS] [varchar](30) NOT NULL,

[SERVER_NAME] [nvarchar](128) NULL,

[LOGDATE] [datetime] NOT NULL

) ON [PRIMARY]

ELSE

DELETE FROM IDENTITY_VALUE_WARNINGS

WHERE CONVERT(VARCHAR(12),LOGDATE,112) = CONVERT(VARCHAR(12),GETDATE(),112)

INSERT INTO IDENTITY_VALUE_WARNINGS

SELECTDATABASE_NAME,TABLE_NAME,COLUMN_NAME,DATA_TYPE,MAX_VALUE,(MAX_VALUE-LAST_VALUE) AS REMAINING_VALUES

,Case

WhenLAST_VALUE < 0 Then100

Else (1 - Cast(LAST_VALUE As float(4)) / MAX_VALUE) * 100

End As [PERCENTLEFT]

, Case

When Cast(LAST_VALUE As float(4)) / MAX_VALUE >=@threshold

Then'WARNING: APPROACHING MAX LIMIT'

Else 'VALUE IS OK'

End As [STATUS]

,@@SERVERNAME AS SERVER_NAME

,GETDATE() ASLOGDATE

FROM#identityStatus

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IDENTITY_VALUE_WARNINGS_RESULTS]') AND type in (N'U'))

DROP TABLE [dbo].IDENTITY_VALUE_WARNINGS_RESULTS

SELECT

CONVERT(VARCHAR(10),DATABASE_NAME) AS DATABASE_NAME,

CONVERT(VARCHAR(25),TABLE_NAME) AS TABLE_NAME,

CONVERT(VARCHAR(25),COLUMN_NAME) AS COLUMN_NAME,

CONVERT(VARCHAR(10),DATA_TYPE) AS DATA_TYPE,

MAX_VALUE,

REMAINING_VALUES,

PERCENTLEFT,

CONVERT(VARCHAR(30),[STATUS] ) AS STATUS

INTOIDENTITY_VALUE_WARNINGS_RESULTS

FROMIDENTITY_VALUE_WARNINGS

WHEREPERCENTLEFT < @threshold

ORDER BY DATABASE_NAME,TABLE_NAME

Declare @sub varchar(100)

Declare @qry varchar(1000)

Declare @msg varchar(250)

Select @sub = @@SERVERNAME + ' : DATA TYPE LIMIT CHECK ' +Convert(Varchar(10), GetDate(), 101)

Select @msg = 'RUN FOLLOWING QUERY TO GETRESULTS' + CHAR(10) + CHAR(10) +

'SELECT* FROM MYadminDB.DBO.IDENTITY_VALUE_WARNINGS_RESULTS ORDER BYDATABASE_NAME,TABLE_NAME' + CHAR(10) + CHAR(10)

Select @qry = 'SET NOCOUNT ON

SELECT* FROM MYadminDB.DBO.IDENTITY_VALUE_WARNINGS_RESULTS ORDER BYDATABASE_NAME,TABLE_NAME'

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'DBMail',

@recipients ='admin@company.com',

@body =@msg,

@subject =@sub ,

@query =@qry ;