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.
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]