By: Eli Leiba | Updated: 2018-06-28 | Comments (2) | Identities
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.
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):
- 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
About the author
View all my tips