Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Nonclustered index count Expand / Collapse
Author
Message
Posted Wednesday, July 22, 2009 12:34 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:57 PM
Points: 310, Visits: 650
I want to find all the tables in the DB where I have clustered index and more than two non clustered indexes.

I can use following command:

SELECT object_name(id), count(1) as 'nonclustered index' FROM SYSINDEXES
WHERE INDID > 1
GROUP BY id
having count(1) > 1

But this doesn't give me the correct value. There are many NULL columns in the sysindex for all the tables. Need help.
Post #757165
Posted Wednesday, July 22, 2009 1:18 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
Since you're on SQL 2005, use sys.indexes rather than the old sysindexes. sys.indexes contains only indexes, sysindexes can contain the column statistics as well.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #757184
Posted Wednesday, July 22, 2009 4:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, July 19, 2014 8:57 PM
Points: 310, Visits: 650
Thanks Gila. Is column statistics also needful? MS should remove sysindexes otherwise.

-Lucky
Post #757280
Posted Wednesday, July 22, 2009 6:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 5:18 PM
Points: 42,437, Visits: 35,492
luckysql.kinda (7/22/2009)
Thanks Gila. Is column statistics also needful?

What do you mean by 'needful'?

They're not a deprecated feature, they're essential for the optimal performance of the query engine.
Check Books Online for more details on statistics

MS should remove sysindexes otherwise.

The sysindexes view is deprecated and will be removed in a future version of the product. It's only included for backward compatibility with SQL 2000









Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #757355
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse