Sunday, 14 September 2014

How to List All Tables and Their Rowcount in SQL Server

Hi friends,in this article I will explain about How to List All Tables and Their Rowcount in SQL Server.
I already explained in the previous articles about How to Backup All Databases in SQL ServerHow to get Distinct Count across Multiple Tables in SQL Server and How to Remove/Delete duplicate records/Rows from a table in SQL Server

Execute the below mentioned Transact SQL code which sys.objects ,sys.partitions and sys.schemas  catalog views to List all Tables with Rowcount in SQL Server Database.
SELECT
  SC.name + '.' + TBLS.name TableName,
  SUM(PRTNS.rows) AS TableRowsCount
FROM sys.tables TBLS
INNER JOIN sys.partitions PRTNS
  ON PRTNS.OBJECT_ID = TBLS.OBJECT_ID
INNER JOIN sys.schemas SC
  ON TBLS.schema_id = SC.schema_id
WHERE TBLS.is_ms_shipped = 0
AND PRTNS.index_id IN (1, 0)
GROUP BY SC.name,
         TBLS.name
ORDER BY SUM(PRTNS.rows) DESC


 
  The output of the above code is list all tables and their row count 


How to List All Tables and Their Rowcount in SQL Server

No comments:

Post a Comment

© 2012-2014 Aspdotnet-Kishore.blogspot.com. All Rights Reserved.
The content is copyrighted to Kishore and may not be reproduced on other websites without permission from the owner.