Record Count For All Tables In Database

In this post I’m actually just going to repeat what I answered to a question on Experts Exchange.

The OP was having an issue getting a record count of all tables in his database.  It was working through the Management Studio, using the undocumented but well-known sp_MSforeachtable stored procedure.

exec sp_MSforeachtable 'select ''?'' TableName, count(*) Cnt from ?';

However, the OP needed the list of record counts inside an Integration Services package.  And this didn’t work out as expected, because sp_MSforeachtable uses a temporary table, something that SSIS does not like.

So I proposed to use a script similar to the following, possibly in a stored procedure.

/*
DESCRIPTION: Returns a list of record counts for each table in the database
AUTHOR:         Valentino Vranken
CREATED:     2011/05/21
VERSION:     1.0
COPIED FROM: http://blog.hoegaerden.be
*/
declare @tempTable table
(
    TableSchema nvarchar(256),
    TableName nvarchar(256),
    Cnt bigint
);

declare @sql nvarchar(4000);
declare @tableSchema nvarchar(256);
declare @tableName nvarchar(256);
declare @columnName sysname;
declare @cnt bigint;

declare tableCursor cursor for
    select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
    where TABLE_TYPE = 'BASE TABLE';

open tableCursor;

fetch next from tableCursor into @tableSchema, @tableName;

while @@FETCH_STATUS = 0
begin
    set @sql = 'select @cnt = COUNT(*) from [' + @tableSchema + '].[' + @tableName + ']';

    exec sp_executesql @sql, N'@cnt bigint output', @cnt = @cnt output;

    insert into @tempTable select @tableSchema, @tableName, @cnt;

    fetch next from tableCursor into @tableSchema, @tableName;
end;

close tableCursor;
deallocate tableCursor;

select * from @tempTable;

Yeah, I know, cursors are bad… But hey, at least it works!  Just don’t schedule it for execution every five seconds.

I seem to be repeating myself.  I wrote something similar when I posted the script about finding empty columns many months ago.

The most important part of the script is the following:

select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = 'BASE TABLE';

This is using one of the information schema views to retrieve a list of tables in the database.  The INFORMATION_SCHEMA.TABLES view contains a list of tables and views, so the where clause is needed to filter out the views.  INFORMATION_SCHEMA.TABLES is just one of many interesting views available in SQL Server, and should always be used in favor of the sys tables.

What does the output look like?  Here’s part of the output when executed on the AdventureWorks2008R2 database:

Record count of all tables in the AdventureWorks2008R2 database (although not all are shown, you get the idea)

Have fun!

Valentino.

Share

Tags: , ,

  1. Dattatrey Sindol (Datta)’s avatar

    Hi Valentino,

    Good One :)
    Here is another approach without the use of cursors.

    http://dattatreysindol.blogspot.com/2010/02/get-record-count-for-all-tables-in.html

    Regards,
    Datta

    Reply

    1. Valentino Vranken’s avatar

      Hi Datta,

      Thanks for posting that, I like the way you’ve used the COALESCE function! That’ll teach me for being lazy and starting off from my earlier post.
      Certainly a more interesting approach than cursors!

      Best regards,
      Valentino.

      Reply

  2. Jeremiah’s avatar

    Hi,

    It does work for all the tables in the database. But what is I want to do it just for 1 table. Is it sufficient enough?

    Reply

  3. rakesh’s avatar

    this code doesn’t showing the count column why?????????

    Reply

    1. Valentino Vranken’s avatar

      What do you mean? (You need to use the whole script, not just that SELECT statement that I mentioned separately.)

      Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved