Count All Tables Rows In SQL Server

In this blog, I am going to show how we can count the total number of rows in every SQL Server table.

I have the following 2 Tables in my database:



Now select Count from these tables:



Now by using the following SQL Statement you can find All Tables with their rows count:

SELECT TableName = o.name,
Rows=max(i.rows)
FROMsysobjects o
INNERJOINsysindexes i
ON o.id = i.id
WHERE xtype ='u'
ANDOBJECTPROPERTY(o.id,N'IsUserTable')= 1
GROUPBY o.name
ORDERBYRowsDESC
GO

We can use the following SQL Statement also to get this type of result:

DECLARE @QueryString NVARCHAR(MAX);
SELECT @QueryString =COALESCE(@QueryString +' UNION ALL ','')
+'SELECT '
+''''+QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+'.'+QUOTENAME(sOBJ.name)+''''+' AS [TableName]
, COUNT(*) AS [RowCount] FROM '
+QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
+'.'+QUOTENAME(sOBJ.name)+' WITH (NOLOCK) '
FROMsys.objectsAS sOBJ
WHERE
sOBJ.type='U'
AND sOBJ.is_ms_shipped = 0x0
ORDERBYSCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXECsp_executesql@QueryString
GO