Rowcount for all tables in SQL Server Database

By: Editor
Feb 2, 2010

Page: 2/2

Quickly Get Row Count of all Table in SQL Server Database Using In built SQL Server Reports

You can Quickly Get Row Count of all Table in SQL Server Database Using In built SQL Server Reports.

1. Click View -> Object Explorer Details or Press F7 to open up Object Explorer Details.
2. Expand Database Node -> Expand Database -> Click Tables on the left side panel.
3. Right click any column in the right side panel and then choose Row Count as shown in the below snippet to display row count for all the tables within the choosen SQL Server Database.

Row Count for Tables in SQL Server Database

Using SP_SPACEUSED System Stored Procedure

Execute the below mentioned TSQL code which leverages SP_SPACEUSED System Stored Procedure to get rowcount of all tables in SQL Server Database.

/* Get Rowcount of all tables in a SQL Server Database */ 

Use AdventureWorks2008
GO 

IF OBJECT_ID (N'dbo.RowCounts', N'U') IS NOT NULL
   
DROP TABLE dbo.RowCounts;

GO 

CREATE TABLE RowCounts
(
[TableName]            VARCHAR(150)
, [RowCount]               INT
,
[Reserved]                 NVARCHAR(50)
,
[Data]                        NVARCHAR(50)
,
[Index_Size]               NVARCHAR(50)
,
[UnUsed]                   NVARCHAR(50))
GO 

INSERT INTO RowCounts([TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed])
EXEC
sp_MSforeachtable 'sp_spaceused "?"'
GO

SELECT [TableName], [RowCount],[Reserved],[Data],[Index_Size],[UnUsed]
FROM
RowCounts
ORDER
BY [TableName]
GO





Quickly Get Total number of records in a Table using sp_spaceused system stored procedure

In case if you would like to know the total number of rows available in a table then execute the below mentioned TSQL code as this has least impact on the SQL Server Performance.

/* Total Row count and Space Used by a tables in SQL Server Database */

Use AdventureWorks2008
GO
 

sp_spaceused [Person.Address]  
GO



Conclusion

In this article you have seen how one can utilize in built system catalogs, dynamic management views, system stored procedures to know the total number of rows available in a table without impacting the overall SQL Server Performance. It is advisable not to use SELECT COUNT(*) FROM TABLENAME query as it will impact the overall performance of SQL Server especially when you are queries aganist a very large table.


Click on Previous Page to read this article from the beginning...

Previous Page..


Share this Article


Geeks who read this article also read…



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."