Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Get a record count for all tables in a database

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

This entry was posted in Uncategorized. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

8 Responses to Get a record count for all tables in a database

  1. Michael says:

    Nice bit of code :)

  2. Jens says:

    made my day ….

  3. Jason says:

    This ROCKS!

    Thank you

  4. Darrell says:

    No, the above code would not work for Access. And I don’t know what would work since I haven’t done any Access development in 4 years.

    You may want to check out the Microsoft Access newsgroup:

    http://groups.google.com/groups?hl=en&lr=&group=microsoft.public.access

  5. RT says:

    Hi

    Im still a novice in ms access,

    can the codes above be use for in microsoft

    access if so where do i need to place the

    codes, thanks

  6. deva says:

    great script

  7. Darrell says:

    I use the sp_msForEachTable stored proc to run DDL commands on every table (say I need to update all the timestamps in every row in every table for some reason).

    The stored proc I show will be faster for large record counts or numbers of tables since I directly access the sysindexes table for record count info.

  8. William Bartholomew says:

    This is also a good trick, it uses an undocumented stored procedure:

    sp_msForEachTable @command1="PRINT ‘?’ SELECT COUNT(*) FROM ?"

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>