Home How can I perform a row count of each table in my list of tables in SQL Server?

Questions

How can I perform a row count of each table in my list of tables in SQL Server?

September 20, 2024

I have my query here to find the list of tables that don’t have a primary key:

select schema_name(tab.schema_id) as [schema_name], 
    tab.[name] as table_name
from sys.tables tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
where pk.object_id is null
order by schema_name(tab.schema_id),
    tab.[name]

It comes back with a list of tables fine, but how do I do a row count of the number of records of each table in that list.

>Solution :

Row counts are kept in sys.partitions

select schema_name(tab.schema_id) as [schema_name], 
    tab.[name] as table_name, SUM(P.[rows]) as [number of records] 
from sys.tables tab
    left outer join sys.indexes pk
        on tab.object_id = pk.object_id 
        and pk.is_primary_key = 1
INNER JOIN 
    sys.partitions p ON tab.object_id = p.OBJECT_ID  AND p.index_id IN (0,1)
where pk.object_id is null
GROUP BY tab.schema_id,tab.[name]
order by schema_name(tab.schema_id),
    tab.[name]