Keep a check on your IDENTITY columns in SQL Server

Found the helpful link here. Code is very helpful to have an eye on Identity columns that may be likely to encounter Arithmetic overflow error.

/* The SQL Server 2005 version of the stored procedure. It uses new catalog views */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.system_type_id
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) AS CurrentIdentityValue,
CASE c.system_type_id
WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM sys.columns AS c
INNER JOIN
sys.tables AS t
ON t.[object_id] = c.[object_id]
WHERE c.is_identity = 1
ORDER BY PercentageUsed DESC
END


If you try to create the above stored procedure in SQL Server 2000, you will get the following error:
Server: Msg 195, Level 15, State 10, Procedure a, Line 4
'SCHEMA_NAME' is not a recognized function name.
So, here are some SQL Server 2000 compatible versions.

/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(USER_NAME(t.uid))+ '.' + QUOTENAME(t.name) AS TableName,
c.name AS ColumnName,
CASE c.xtype
WHEN 127 THEN 'bigint'
WHEN 56 THEN 'int'
WHEN 52 THEN 'smallint'
WHEN 48 THEN 'tinyint'
END AS 'DataType',
IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) AS CurrentIdentityValue,
CASE c.xtype
WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 9223372036854775807
WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 2147483647
WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 32767
WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 255
END AS 'PercentageUsed'
FROM syscolumns AS c
INNER JOIN
sysobjects AS t
ON t.id = c.id
WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1
AND OBJECTPROPERTY(t.id, 'isTable') = 1
ORDER BY PercentageUsed DESC
END


/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */
CREATE PROC dbo.CheckIdentities
AS
BEGIN
SET NOCOUNT ON
SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS TableName,
c.COLUMN_NAME AS ColumnName,
c.DATA_TYPE AS 'DataType',
IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
CASE c.DATA_TYPE
WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 2147483647
WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 32767
WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 255
END AS 'PercentageUsed'
FROM INFORMATION_SCHEMA.COLUMNS AS c
INNER JOIN
INFORMATION_SCHEMA.TABLES AS t
ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1
AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint')
AND t.TABLE_TYPE = 'BASE TABLE'
ORDER BY PercentageUsed DESC
END / 2147483647 WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 32767 WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id) + '.' + t.name) * 100.) / 255 END AS 'PercentageUsed' FROM sys.columns AS c INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id] WHERE c.is_identity = 1 ORDER BY PercentageUsed DESC

END

If you try to create the above stored procedure in SQL Server 2000, you will get the following error:Server: Msg 195, Level 15, State 10, Procedure a, Line 4'SCHEMA_NAME' is not a recognized function name.So, here are some SQL Server 2000 compatible versions.

/* The SQL Server 2000 version of the stored procedure. Uses system tables. This should work in SQL Server 7.0 too */

CREATE PROC dbo.CheckIdentitiesASBEGIN SET NOCOUNT ON SELECT QUOTENAME(USER_NAME(t.uid))+ '.' + QUOTENAME(t.name) AS TableName, c.name AS ColumnName, CASE c.xtype WHEN 127 THEN 'bigint' WHEN 56 THEN 'int' WHEN 52 THEN 'smallint' WHEN 48 THEN 'tinyint' END AS 'DataType', IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) AS CurrentIdentityValue, CASE c.xtype WHEN 127 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 9223372036854775807 WHEN 56 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 2147483647 WHEN 52 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 32767 WHEN 48 THEN (IDENT_CURRENT(USER_NAME(t.uid) + '.' + t.name) * 100.) / 255 END AS 'PercentageUsed' FROM syscolumns AS c INNER JOIN sysobjects AS t ON t.id = c.id WHERE COLUMNPROPERTY(t.id, c.name, 'isIdentity') = 1 AND OBJECTPROPERTY(t.id, 'isTable') = 1 ORDER BY PercentageUsed DESCEND/* The SQL Server 2000 version of the stored procedure. Uses INFORMATION_SCHEMA views. */

CREATE PROC dbo.CheckIdentities

AS

BEGIN SET

NOCOUNT ON

SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) AS TableName, c.COLUMN_NAME AS ColumnName, c.DATA_TYPE AS 'DataType', IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) AS CurrentIdentityValue, CASE c.DATA_TYPE WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807 WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 2147483647 WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 32767 WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA + '.' + t.TABLE_NAME) * 100.) / 255 END AS 'PercentageUsed' FROM INFORMATION_SCHEMA.COLUMNS AS c INNER JOIN INFORMATION_SCHEMA.TABLES AS t ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1 AND c.DATA_TYPE IN ('bigint', 'int', 'smallint', 'tinyint') AND t.TABLE_TYPE = 'BASE TABLE' ORDER BY PercentageUsed DESC

END