July 22, 2009 at 12:34 am
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.
July 22, 2009 at 1:18 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2009 at 4:10 am
Thanks Gila. Is column statistics also needful? MS should remove sysindexes otherwise.
-Lucky
July 22, 2009 at 6:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply