A quick post that proved helpful to me today. I needed to see the number of rows for all tables in the database. I found this query:
SELECT ‘[‘ + SCHEMA_NAME(t.schema_id) + ‘].[‘ + t.name + ‘]’ AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
on Brian Knights blog: Quickly Retrieving the Row Count for All Tables. One thing to note is that the numbers could be a little out of date. DBCC UPDATEUSAGE (DBNAME) should resolve that, the problem is that this only works when you are admin. If you want to be 100% accurate you might need to do a little more investigation.
Thanks Kasper.
Another approach will be querying the sys.objects and sys.partitions tables:
SELECT O.Name AS TableName, P.rows AS NumberOfRows
FROM sys.objects O
INNER JOIN sys.partitions P ON (O.object_id = P.object_id)
WHERE O.type = ‘U’
We use a view for this. Can be very handy;
SELECT TOP 100 PERCENT O.name AS TableName,
s.name AS SchemaName,
I.rowcnt AS NrOfRecords
FROM sysobjects AS O
INNER JOIN sysindexes AS I ON O.id = I.id
INNER JOIN sys.schemas AS S ON O.uid = S.schema_id
WHERE I.indid IN (0, 1)
AND O.xtype = ‘u’
AND O.[name] NOT IN (‘sysdiagrams’, ‘dtproperties’)
ORDER BY O.[name]