Home > Query, SQL > BISQL # 42 : How to find count of all the records in all the table or for specific table

BISQL # 42 : How to find count of all the records in all the table or for specific table


Hi Friends,

We know we can find the row count just by having COUNT() function but still if we wand to show or during DW building we will always have to fire count script.

So why don’t we have all row count at on shot
Here we go !!!
Today i just got interesting script where i can find all row count at the one shot in all table ,we just have to fire the query


    name, CAST(NULL AS INT) AS NumRows
    INTO #tables
    FROM sys.sysobjects
    WHERE  xtype = 'U'
           --  AND
           --name LIKE '<Expected Table names>%'      

      SELECT TOP 1 @DBObjectID = name
      FROM #tables
      WHERE NumRows IS NULL

SET @SQL = 'SELECT @NumRows = COUNT(*) FROM ' + @DBObjectID
EXEC sp_executesql @SQL, N'@NumRows INT OUTPUT', @NumRows = @NumRows OUTPUT

        UPDATE #tables
        SET NumRows = @NumRows
        WHERE name = @DBObjectID

SELECT * FROM #tables ORDER BY name

DROP TABLE #tables


Out put of above is as follow


Even i do some customization further where we can select particular tables name as well for row count.

But here we have to go further add your required table @ place of <Expected Table names>


Then will have output in following way


Hope you use the script wherever required  !!

Happy Learning !!!


For more interesting information on SQL we can also look into similar topics such as

· BISQL # 43 : Introduction to DAC – Data-Tier Application {DAC Part – I}

· BISQL # 44 : DAC-Database Project Overview {DAC Part – II}

· BISQL # 45 : How to Rename .MDF File and .LDF file ? (i.e – .mdf , .ldf)

· BISQL # 46 : Information of SQL Server Database Services and Startup account

· BISQL # 47 : How to find Details of Any Column in Entire Database in One Query


Hope you will like this post on count of all records in all the tables.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog : Link Resource Website

                 For More information related to BI World visit my Mentalist Blog

                             Link Resource Blog >> Daily Interesting links

                             SQL Server Mentalist >> SQL Learning Blog

                             Business Intelligence Mentalist >> BI World

                                      Connect With me on

             | FaceBook |Twitter | LinkedIn| Google+ | WordPress | RSS |

                                     Copyright © 2011 – 2012 Vishal Pawar

Categories: Query, SQL
  1. No comments yet.
  1. August 22, 2011 at 1:55 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: