Rowcount for all tables in SQL Server Database

By: Editor
Feb 2, 2010

Page: 1/2

Introduction

In this article we will take a look at different options that are available to get rowcount for all the tables in a SQL Server Database. Options mentioned in this article will have least impact when the queries are executed as these queries leverage system tables to fetch the rowcount data. One should always avoid using the costliest options which is SELECT COUNT(*) to fetch the rowcount data.

Let us take a look at each of the below mentioned methods to get the total number of rows in each of the SQL Server Tables in a database.

1. Using sys.objects and sys.partitions Catalog views available in SQL Server 2005 and above.
2. Using sys.dm_db_partition_stats DMV which is available in SQL Server 2005 and above.
3. Using sysobjects and sysindexes system view available in SQL Server 2000.
4. Quickly Get Row Count of all Table in SQL Server Database Using In-built SQL Server Reports.
5. Using SP_SPACEUSED System Stored Procedure.
6. Quickly Get Record Count of a Single Table using SP_SPACEUSED System Stored Procedure.

Using sys.objects and sys.partitions Catalog Views

Execute the below mentioned TSQL code which leverages sys.objects and sys.partitions catalog views to List all Tables with Rowcount in SQL Server Database.

/* List all Tables with Rowcount in SQL Server Database Using sys.objects and sys.partitions catalog views */

Use AdventureWorks2008
GO

SELECT
       
SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
     
, (SPS.Rows) AS [Row Count]
     
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END

FROM

     
sys.objects AS SOS
     
INNER JOIN sys.partitions AS SPS
           
ON SOS.object_id = SPS.object_id

WHERE

     
SOS.type = 'U'
     
AND SPS.index_id < 2

ORDER
BY [Table Name]
GO




Using sys.dm_db_partition_stats Dynamic Management View

Execute the below mentioned TSQL code which leverages sys.dm_db_partition_stats Dynamic Management View (DMV) to get record count for all tables in a database.

/* List all Tables with Rowcount in SQL Server Database Using sys.objects and sys.partitions catalog views */

Use AdventureWorks2008
GO


SELECT

       
SCHEMA_NAME(SOS.SCHEMA_ID) + '.' + (SOS.NAME) AS [Table Name]
     
, (SPS.Rows) AS [Row Count]
     
, [Heap / Clustered Table] = CASE SPS.INDEX_ID WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END

FROM

     
sys.objects AS SOS
     
INNER JOIN sys.partitions AS SPS
           
ON SOS.object_id = SPS.object_id

WHERE

     
SOS.type = 'U'
     
AND SPS.index_id < 2

ORDER
BY [Table Name]
GO

Using sysobjects and sysindexes system views

Execute the below mentioned TSQL code which leverages sysobjects and sysindexes system views to retrieve the Row Count for all Tables in a Database. The below script works on SQL Server 2000.

/* Retrieve Row Count for all Tables in a Database - Backward Compatibility Script for SQL Server 2000 */

Use AdventureWorks2008
GO

SELECT
     
SOS.Name
   
, SIS.Rows
   
, [Fill Factor] = CASE SIS.OrigFillFactor WHEN 0 THEN '100' ELSE OrigFillFactor END
   
, [Heap / Clustered Table] = CASE SIS.indid WHEN 0 THEN 'HEAP' ELSE 'CLUSTERED' END

FROM

   
sysobjects SOS
                       
INNER JOIN sysindexes
                                   
SIS ON SOS.id = SIS.id

WHERE

   
type = 'U' AND SIS.IndId < 2

ORDER
BY SOS.Name
GO

   

Click on Next Page to continue reading rest of the article...

Next Page..


Share this Article



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."