This post originated from an RSS feed registered with .NET Buzz by Darrell Norton. Original Post: Get a record count for all tables in a database Feed Title: Darrell Norton's Blog Feed URL: /error.htm?aspxerrorpath=/blogs/darrell.norton/Rss.aspx Feed Description: Agile Software Development: Scrum, XP, et al with .NET Latest .NET Buzz Posts Latest .NET Buzz Posts by Darrell Norton Latest Posts From Darrell Norton's Blog

I developed this stored proc after a testing group said that I had not included required data (data in the database for the application to work properly) in a deployment. It also comes in handy to make sure you’ve copied all records from one database to another.

To use, just type GetRecordCountsForAllTables in Query Analyzer and you will get a record set returned with a row for every table in the current database with the owner, table name, and row count. Perfect for copying and pasting to Excel.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetRecordCountsForAllTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetRecordCountsForAllTables]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE  Procedure dbo.GetRecordCountsForAllTables AS

select  'Owner'=convert(char(10),t.TABLE_SCHEMA),
      'Table Name'=convert(char(25),t.TABLE_NAME),
      'Record Count'=max(i.rows)
from sysindexes i, INFORMATION_SCHEMA.TABLES t
where t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
group by t.TABLE_SCHEMA, t.TABLE_NAME
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO