tables Thursday, September 15, 2022

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]

Conclusion

The above SQL Query has returned 71 Tables with their Schema name and Row Count.

Sports