CODEWITHSUNDEEP
sql-servert-sql
GPGVM
GPGVM

Reputation: 5619

Double join resulting in duplicate records and incorrect row count

I know this my mistake with the joins but not sure why I am getting the multiple rows and erroneous row count?

My tsql:

DECLARE @PeopleIDTables TABLE ( Column_Name varchar(500) NULL, Table_Name varchar(500) NULL)

INSERT INTO @PeopleIDTables
    SELECT COLUMN_NAME + ',', TABLE_NAME 
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE COLUMN_NAME LIKE 'People_ID'
    ORDER BY TABLE_NAME

--SELECT * FROM @PeopleIDTables


SELECT PIT.Table_Name, I.rows NumberRows
FROM @PeopleIDTables PIT
JOIN sys.tables T ON PIT.Table_Name = T.name
JOIN sys.sysindexes I ON T.object_id = I.id

Here is an excerpt from my results:

PEOPLE  770438
PEOPLE  770438
PEOPLE  770438
PEOPLE  770438
PEOPLE  770438
PEOPLE  770438
PEOPLE  770438
PEOPLE  0

When in fact there is only one database on this dev system and it has only one people table. The first half of the query is accurate returning the 64 tables that have a column of People_Id.

Upvotes: 0

Views: 108

Answers (1)

xQbert
xQbert

Reputation: 35333

Maybe you mean...

SELECT PIT.Table_Name, Max(I.rows) NumberRows
FROM @PeopleIDTables PIT
JOIN sys.tables T ON PIT.Table_Name = T.name
JOIN sys.sysindexes I ON T.object_id = I.id
GROUP BY PIT.Table_Name

I assume you want max rows, as an index could be being built or need updating.. and the rowcount here is not always 100% accurate.

Upvotes: 1

Related Questions