tables
Thursday, September 15, 2022
SQL Query to get Row Count For all Tables in SQL Server
SQL Query to get Row Count For all Tables in SQL Server
Overview
I am using the AdventureWorks as default database and will open Query Analyzer after selecting this database
Here is the download link for AdventureWorks for almost all available MS SQL Server versions
https://docs.microsoft.com/en-us/sql/samples/adventureworks-install-configure?view=sql-server-ver16&tabs=ssms
Open Query Analyzer
Write / Copy the following Query as below
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
GO
Conclusion
The above SQL Query has returned 71 Tables with their Schema name and Row Count.
Hello everyone, My name is Muhammad Kamran . I'm always keen to share my best of knowledge with all concerned audience with best wishes and prayers for their career groom.