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

DECLARE @DBObjectID AS NVARCHAR(128)
DECLARE @NumRows AS INT
DECLARE @SQL AS NVARCHAR(MAX)

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

WHILE EXISTS (SELECT * FROM #tables WHERE NumRows IS NULL)
BEGIN
      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
END

SELECT * FROM #tables ORDER BY name

DROP TABLE #tables

 

Out put of above is as follow

image

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>

snap

Then will have output in following way

image

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

About these ads
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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,120 other followers

%d bloggers like this: