Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Current Identity Value Report for All Tables


By:   |   Updated: 2018-06-28   |   Comments (2)   |   Related: More > 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):

sql server identity value report
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.


Last Updated: 2018-06-28


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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 Back To Top

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

Learn more about SQL Server tools