duplicates on sysindexes join sysobjects

when i run the below query, i get multiple rows for each table- often with a valid row count and one with zero. why is this? all i needed was the table name and the number of rows it has.
select  object_name(b.id),rows from sysindexes a, sysobjects b where a.id = b.id and b.id >99

Open in new window

LVL 6
anushahannaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

htowntechyCommented:
It sounds like you have multiple occurrences of the id in your tables.  are there more fields you can create another join criteria in your tables?  It would help if you posted some sample data.
anushahannaAuthor Commented:
I am getting something like this for the output:

do I need another join in the query?
(No column name)	rows
ConfigurationInfo	19
ConfigurationInfo	19
ConfigurationInfo	0
Catalog	1
Catalog	1
Catalog	1
Catalog	1
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
Catalog	0
UpgradeInfo	0
UpgradeInfo	0
ModelDrill	0
ModelDrill	0
ModelDrill	0
ModelDrill	0
ModelDrill	0
ModelPerspective	0
ModelPerspective	0
ModelPerspective	0
ModelPerspective	0
ModelPerspective	0
CachePolicy	0
CachePolicy	0
CachePolicy	0
CachePolicy	0
Users	3
Users	3
Users	0
Users	0
Users	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
DataSource	0
Policies	2
Policies	0
ModelItemPolicy	0
ModelItemPolicy	0
ModelItemPolicy	0
ModelItemPolicy	0
SecData	2
SecData	2
SecData	0
SecData	0
SecData	0
Roles	8
Roles	8
Roles	0
Roles	0
Roles	0
PolicyUserRole	4
PolicyUserRole	4
PolicyUserRole	0
PolicyUserRole	0
Event	0
Event	0
Event	0
Event	0
Event	0
Event	0
Event	0
Event	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
ExecutionLog	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
Subscriptions	0
ActiveSubscriptions	0
ActiveSubscriptions	0
ActiveSubscriptions	0
ActiveSubscriptions	0
ActiveSubscriptions	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
SnapshotData	0
ChunkData	0
ChunkData	0
ChunkData	0
ChunkData	0
ChunkData	0
ChunkData	0
ChunkData	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Notifications	0
Batch	0
Batch	0
Batch	0
Batch	0
Batch	0
Batch	0
Batch	0
Batch	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
Schedule	0
ReportSchedule	0
ReportSchedule	0
ReportSchedule	0
ReportSchedule	0
ReportSchedule	0
BranchArea	2
BranchArea	0
BranchArea	0
BranchLocHQ	8
BranchLocHQ	0
BranchLocHQ	0
BranchLocHQ	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
RunningJobs	0
ServerParametersInstance	0
ServerParametersInstance	0
ServerParametersInstance	0
ServerParametersInstance	0
ServerParametersInstance	0
ServerParametersInstance	0
ServerParametersInstance	0
queue_messages_1977058079	0
queue_messages_1977058079	0
queue_messages_2009058193	0
queue_messages_2009058193	0
queue_messages_2041058307	0
queue_messages_2041058307	0
Keys	2
Keys	0
Keys	0
Keys	0
Keys	0
History	0
History	0
History	0
History	0

Open in new window

FemSteenkampIT managerCommented:
try this

select  object_name(b.id),rows from sysindexes a, sysobjects b where a.id = b.id and b.id >99
AND indid < 2

you probably got duplicates count due to additionla indexes on tables. (inid field)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anushahannaAuthor Commented:
Yes, that fixed it. Thanks FemSteenkamp.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.