T-SQL Script to get row count of all tables in the database

Posted by on Aug 18, 2013 in SQL Server, T-SQL | No Comments
Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

Today I’ve wrote a simple script to get number of records for all tables in a database and fetch those tables that contains at least one record.

This picture shows an example of result set:

Here is the script:

declare @tempTable
table(tablename
varchar(max),

rowscount int,processed bit)

insert into @tempTable

select TABLE_NAME,0,0 from INFORMATION_SCHEMA.TABLES

where TABLE_CATALOG=‘DEMO’

declare
@currentTable varchar(max)

set
@currentTable=(select top 1 tablename from
@tempTable where processed=0)

declare
@sqlCommand nvarchar(500)

declare @rowsnum int

declare @param nvarchar(500)

set @param= N’@cnt int OUTPUT’;

while (select COUNT(*) from @tempTable where
processed=0)<>0

begin

set
@sqlCommand=N’select
@cnt=count(*) from ‘
+@currentTable

EXECUTE sp_executesql @sqlCommand, @param, @cnt=@rowsnum OUTPUT;

update @tempTable

set processed=1,rowscount=@rowsnum

where tablename=@currentTable

set
@currentTable=(select top 1 tablename from
@tempTable where processed=0)

end

select tablename,rowscount from
@tempTable

where rowscount>0

order by rowscount desc

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Reza Rad
Reza Rad is an Author, Trainer, Speaker and DW/BI Consultant. He has a BSc in Computer engineering; he has more than 15 years’ experience in databases, programming and development mostly on Microsoft technologies. He is a Microsoft MVP in Data Platform for seven continues years (from 2011 till now) for his dedication in Microsoft BI. He is author of some SQL Server and BI books, and also Power BI online book; from Rookie to Rock Star.

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 class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">