Rowcount for all tables in SQL Server Database
Feb 2, 2010
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.
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 */
DROP TABLE dbo.RowCounts;
( [TableName] VARCHAR(150)
, [RowCount] INT
, [Reserved] NVARCHAR(50)
, [Data] NVARCHAR(50)
, [Index_Size] NVARCHAR(50)
, [UnUsed] NVARCHAR(50))
EXEC sp_MSforeachtable 'sp_spaceused "?"'
ORDER BY [TableName]
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 */
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...
Geeks who read this article also read…
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…