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