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
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
Copyright © 2011 – 2012 Vishal Pawar
-
August 22, 2011 at 1:55 amLink Resource # 18 : Aug 19 – Aug 22 « Dactylonomy of Web Resource
SQL Server Mentalist 