Manage Learn to apply best practices and optimize your operations.

Utility routine: sp_CheckRowCounts

Contributor Brian Walker offers this routine to return a result set containing row counts along with IDENTITY value information for selected tables.

The sp_CheckRowCounts routine returns a result set containing row counts along with IDENTITY value information for selected tables.

The sp_CheckRowCounts stored procedure is useful for examining the current state of selected tables with regard to row counts and IDENTITY values used for surrogate keys. It returns a result set with six columns. The columns include the table name, the number of rows in the table, the smallest existing IDENTITY value, the largest existing IDENTITY value, the last assigned IDENTITY value and the next available IDENTITY value.

    CREATE PROCEDURE dbo.sp_CheckRowCounts
        @DBIntra varchar(8000) = NULL,
        @DBExtra varchar(8000) = NULL,
        @PCIntra varchar(100)  = NULL,
        @PCExtra varchar(100)  = NULL,
        @PCUltra bit = 0

The first four parameters are all optional and they work together to form a combination of table selection criteria using names.

The first and second parameters (@DBIntra/@DBExtra) are lists of table names separated by pipes (vertical bars). The @DBIntra parameter specifies table names to be included. The @DBExtra parameter specifies table names to be excluded.

The third and fourth parameters (@PCIntra/@PCExtra) offer table selection based on pattern matching names. The @PCIntra parameter includes tables by using a LIKE operator on the names. The @PCExtra parameter excludes tables by using a NOT LIKE operator.

The effects of these four parameters are combined with AND operators. Often only one of the parameters (or none) would be used for a given call, but it may be useful to provide @DBExtra and/or @PCExtra in combination with @PCIntra in order to work with the desired subset of tables. If the parameters are omitted, or if null values are provided, they are effectively ignored for selection purposes.

The method used to select tables in these routines is not necessarily the most efficient way of handling delimited strings. The CHARINDEX function was used for simplicity because performance is not a significant issue when referencing tables with very modest row counts in an administrative routine. If this kind of selection task were being done in a production routine that referenced tables with larger row counts, it would probably be better to parse the delimited string and use the result set for a join.

The fifth parameter (@PCUltra) is also optional and determines whether table name prefixes (if used and if defined) are included in the output. A value of zero (0) means they will be omitted and a value of one (1) means they will be included.

This example returns IDENTITY value information for selected tables in my example database.

EXECUTE sp_CheckRowCounts NULL,NULL,'Purchase%'

This example returns IDENTITY value information for the extra table in my example database.

EXECUTE sp_CheckRowCounts 'Example'

Utilities to maintain a surrogate key architecture

 Home: Introduction
 Part 1: What are surrogate key values
 Part 2: T-SQL code to create stored procedures
 Part 3: Utility routine: sp_CheckRowCounts
 Part 4: Utility routine: sp_CheckKeyValues
 Part 5: Utility routine: sp_ResetNextKeyValue
 Part 6: Utility routine: sp_OrganizeKeyValues
 Part 7: Utility routine: sp_FindDuplicateRows
 Part 8: Utility routine: sp_ChangeParentValue

More advice:
Surrogate key architecture to perform powerful database operations
Framework to support a surrogate key architecture

Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.