CODEWITHSUNDEEP
sqlsql-server
salvationishere
salvationishere

Reputation: 3501

How to count rows in table join?

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

Answers (3)

Aaron Bertrand
Aaron Bertrand

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

djangojazz
djangojazz

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

Aaron Kurtzhals
Aaron Kurtzhals

Reputation: 2036

Use count (distinct <columnname>) on a unique column for each table that you need to count.

Upvotes: 1

Related Questions