SQL Server Current Identity Value Report for All Tables

By: Eli Leiba | Comments (4) | Related: > Identities

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.

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Saturday, February 20, 2021 - 10:40:57 PM - Eric E Isaacs	Back To Top (88273)
Great SQL Server tip! I found that if I order by the following I can see the tables that need attention at the top (assuming no issues with TINYINTs, etc which aren't an issue for me.) ORDER BY LEN(FORMAT(IDENT_CURRENT(A.TABLE_NAME) , 'N0')) DESC , FORMAT(IDENT_CURRENT(A.TABLE_NAME) , 'N0') DESC , A.TABLE_SCHEMA , A.TABLE_NAME;

Wednesday, September 23, 2020 - 6:13:05 PM - Cory Shanks	Back To Top (86527)
Why did you implement this as a stored procedure? Surely a view could be better. i.e. Select where type_limit - curr_value < 200,000.

Tuesday, August 13, 2019 - 12:34:45 PM - Slava

Hi, Eli!

Thank you for the script.

In order to use it with multiple schema names, it needs some correction: we should use "A.TABLE_SCHEMA + '.' + A.TABLE_NAME" insted of "A.TABLE_NAME" in all system function calls.

HTH.

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_SCHEMA + '.' + A.TABLE_NAME) AS Seed,
   IDENT_INCR (A.TABLE_SCHEMA + '.' + A.TABLE_NAME) AS Increment,
   IDENT_CURRENT (A.TABLE_SCHEMA + '.' + 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 (A.TABLE_SCHEMA + '.' + A.TABLE_NAME), B.COLUMN_NAME, 'IsIdentity') = 1 AND 
     OBJECTPROPERTY (OBJECT_ID (A.TABLE_SCHEMA + '.' + A.TABLE_NAME), 'TableHasIdentity') = 1 AND 
     A.TABLE_TYPE = 'BASE TABLE'
ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME

Thursday, June 28, 2018 - 7:34:33 AM - Marcelo Miorelli

Hi Eli,

thanks for putting this script together.

I have been using it, however, I need to do 2 changes:

1) needed to add the "not for replication" column because this is a very important information when dealing with identity columns (if you have replication)

2) reduced the output of the column "Type_limit" otherwise it would be 8000 and that is not good for my requirements.

The following is the script altered, you can add those features to your post and delete this comment, again thanks for the tip, hope to see more tips from you.

SELECT 
   A.TABLE_CATALOG AS CATALOG,
   A.TABLE_SCHEMA AS "SCHEMA",
   A.TABLE_NAME AS "TABLE",
   B.COLUMN_NAME AS "COLUMN",
   [Is Not for Replication] = CASE WHEN COLUMNPROPERTY( OBJECT_ID (B.TABLE_NAME),B.COLUMN_NAME,'IsIdNotForRepl') = 1 THEN 'Yes'
       ELSE 'No' END,
   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 = LEFT(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 CASE WHEN B.NUMERIC_PRECISION < 18 
         THEN REPLICATE ('9', B.NUMERIC_PRECISION)
         ELSE REPLICATE ('9', 18) 
         END 
      WHEN 'numeric'                             
         THEN CASE WHEN B.NUMERIC_PRECISION < 18 
         THEN REPLICATE ('9', B.NUMERIC_PRECISION)
         ELSE REPLICATE ('9', 18) 
         END 
      END,30)
FROM INFORMATION_SCHEMA.TABLES A
INNER JOIN INFORMATION_SCHEMA.COLUMNS B 
    ON 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