How to Pull Record Counts for Tables

One way to familiarize one’s self with a very non-production database is to get a list of tables and record counts for each.

declare @tbl nvarchar(400); -- internally generated, table name
declare @sch nvarchar(400); -- internally generated, schema name
declare @cnt int; -- internally generated, count of records
declare @exc nvarchar(4000); -- variable to contain dynamic sql
declare @fetch_status int; -- gives loop status

declare FindAllTables cursor forward_only for select table_schema,table_name from information_schema.tables where table_type = 'BASE TABLE';

open FindAllTables;

set @fetch_status = 0

while @fetch_status = 0
begin
	fetch next from FindAllTables into @sch, @tbl
	set @fetch_status = @@fetch_status;
	if coalesce(@fetch_status,1) = 0
	begin
        set @exc = N'declare @cnt int;select @cnt = count(*) from [' + @sch + '].[' + @tbl + ']; print ''table ' + @sch + '.' + @tbl + ': ''' +
        + N' + cast(@cnt as nvarchar(100));';

        --print @exc;
        exec (@exc);
    end;

end;

close FindAllTables;
deallocate FindAllTables;

This will list out all the tables in the database currently selected. Feel free to insert the USE command at the top of this script to programmatically define the working database. Remember, this script should never be ran in a live production database.

You can also change the query for the cursor variable “FindAllTables” to find only the tables you wish to include in your query. You can also conditionally execute the dynamic sql variable “@exc”. Remember, you must have select privileges on all the tables you wish to iterate.

At eimagine, developers get to work on all kinds of technologies and have lots of fun together in a friendly and collaborative workplace. SQL Server is just one of many disciplines to learn and master. I hope you find this post useful.

Enjoy!