Link to home
Start Free TrialLog in
Avatar of anushahanna
anushahannaFlag for United States of America

asked on

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

Avatar of htowntechy
htowntechy
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.
Avatar of anushahanna
anushahanna
Flag of United States of America image

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of FemSteenkamp
FemSteenkamp
Flag of South Africa image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of anushahanna
anushahanna
Flag of United States of America image

ASKER

Yes, that fixed it. Thanks FemSteenkamp.