SQL Server Current Identity Value Report for All Tables

Problem

We recently had an issue where one of our identity values reached the maximum value.  In order to not have this issue occur in the future, I decided to write a stored procedure to let me know the current values for all tables that have an identity value.

Solution

My solution involves creating a T-SQL stored procedure that contains an identity column report for the whole database by performing a query that joins to the INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS system views. These two views are connected by the catalog, schema and table names columns.

This procedure (usp_DetailedIdentityColumnsReport) will display the identity column name, its type, the identity columns current value and the maximum allowed identity value that can be reached according to the column type.

The identity can be one of these data types:

• Integer (4 bytes)
• Big integer (8 bytes)
• Small integer (2 bytes)
• Tiny integer (1 byte)
• Decimal or numeric with a scale of zero (0)

The maximum values are 2^31 -1 for an integer, 2^63-1 for a big integer, 2^15-1 for a small integer and 255 for a tiny integer. 

For decimal or numeric with scale 0 the maximum value is 10^ (numeric precision)-1.  For example, for an identity column that is defined as decimal (3, 0) the maximum value is 10^3-1 = 999.  The maximum value is evaluated by using a CASE statement by the query.

SQL Stored Procedure for Identity Value Report

-- ================================================================
-- Author:      Eli Leiba
-- Create date: 06-2018
-- Description:
--   Produce a Detailed Identity columns report for the whole database
--   That includes the database, schema, table, column, type, 
--   Seed, increment and the data type value limit
-- ================================================================
CREATE PROCEDURE dbo.usp_DetailedIdentityColumnsReport
AS
SELECT A.TABLE_CATALOG AS CATALOG,
   A.TABLE_SCHEMA AS "SCHEMA",
   A.TABLE_NAME AS "TABLE",
   B.COLUMN_NAME AS "COLUMN",
   IDENT_SEED (A.TABLE_NAME) AS Seed,
   IDENT_INCR (A.TABLE_NAME) AS Increment,
   IDENT_CURRENT (A.TABLE_NAME) AS Curr_Value,
   B.DATA_TYPE as "Type",
   Type_Limit = CASE lower (B.DATA_TYPE)
      WHEN 'bigint'
         THEN '9,223,372,036,854,775,807'
      WHEN 'int'
         THEN '2,147,483,647'
      WHEN 'smallint'
         THEN '32,767'
      WHEN 'tinyint'
         THEN '255'
      WHEN 'decimal'  
         THEN REPLICATE ('9', B.NUMERIC_PRECISION)
      WHEN 'numeric'  
         THEN REPLICATE ('9', B.NUMERIC_PRECISION)
      END
FROM INFORMATION_SCHEMA.TABLES A, INFORMATION_SCHEMA.COLUMNS B
WHERE A.TABLE_CATALOG = B.TABLE_CATALOG AND 
      A.TABLE_SCHEMA = B.TABLE_SCHEMA AND 
     A.TABLE_NAME = B.TABLE_NAME AND 
     COLUMNPROPERTY (OBJECT_ID (B.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') = 1 AND 
     OBJECTPROPERTY (OBJECT_ID (A.TABLE_NAME), 'TableHasIdentity') = 1 AND 
     A.TABLE_TYPE = 'BASE TABLE'
ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME
GO

Example using the procedure

Let’s produce an identity detailed report on the Northwind database:

USE Northwind
GO
exec dbo.usp_IdentityColumnsReport
GO

And the results are (on my server):

Next Steps

• You can create and compile this simple procedure in your application database and use it as a simple T-SQL tool for producing this report.
• The procedure was tested for SQL Server version: SQL Server 2014, 2016 and 2017, but should work for other versions as well.

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and a senior database consultant. With 19 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science. He can be reached at: iecdba@hotmail.com.

• MSSQLTips Awards: Rising Star (50+ tips) – 2019 | Author of the Year Contender – 2016-2019