Reputation: 3501
I am developing a SQL sproc and I want to return the number of rows for each table. How could I rewrite this statement so that it will list number of rows from each table below?
SELECT COUNT(*)
FROM [test_setup_details_form_view] [tsdf]
JOIN [test_setup_header_form_view]
ON [test_setup_header_form_view].[test_setup_header_id]
= [tsdf].[test_setup_header_id]
JOIN [test_header_rv] [th] with(nolock)
ON [th].[test_setup_header_id]
= [test_setup_header_form_view].[test_setup_header_id]
JOIN [test_details_answers_expanded_view] [tdae]
ON [tdae].[test_setup_details_id] = [tsdf].[test_setup_details_id]
AND [th].[test_header_id] = [tdae].[test_header_id]
JOIN [event_log_rv] [e]
ON [e].[event_log_id] = [tdae].[event_log_id]
When I execute this statement, it just gives me the total rows after all of the joins.
Upvotes: 0
Views: 4875
Reputation: 280644
If you are trying to just get counts for each of these tables irrespective of the joins:
SELECT
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id]),
c
FROM
(
SELECT [object_id],
c = SUM(row_count)
FROM
sys.dm_db_partition_stats -- no NOLOCK necessary
WHERE
index_id IN (0,1)
AND OBJECT_NAME([object_id]) IN
(
N'test_setup_details_from_view',
N'test_setup_header_from_view',
... etc etc. ...
)
GROUP BY [object_id]
) AS x;
Upvotes: 3
Reputation: 13173
From each table? Why not use the metadata tables then?
You are trying to do something in code that already exists in the metadata tables:
Select
schema_name(schema_id) + '.' + t.name as TableName
, i.rows
from sys.tables t (nolock)
join sys.sysindexes i (nolock) on t.object_id = i.id
and i.indid < 2
Upvotes: -2
Reputation: 2036
Use count (distinct <columnname>)
on a unique column for each table that you need to count.
Upvotes: 1