September 29, 2010 at 1:18 pm
Is "sp_helpindex" considered to be the definitive method in determining if a TABLE has ANY kind of an INDEX?
...thanks in advance
September 29, 2010 at 1:27 pm
I would just look in management studio.
September 30, 2010 at 5:44 am
From the SQL Server documentation, Books Online:
sp_helpindex exposes only orderable index columns; therefore, it does not expose information about XML indexes or spatial indexes.
So the short answer is, no.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 6:49 am
Grant Fritchey (9/30/2010)
From the SQL Server documentation, Books Online:sp_helpindex exposes only orderable index columns; therefore, it does not expose information about XML indexes or spatial indexes.
So the short answer is, no.
Will DBCC UPDATEUSAGE play here any role ? just a question 🙂
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 30, 2010 at 6:55 am
Bhuvnesh (9/30/2010)
Grant Fritchey (9/30/2010)
From the SQL Server documentation, Books Online:sp_helpindex exposes only orderable index columns; therefore, it does not expose information about XML indexes or spatial indexes.
So the short answer is, no.
Will DBCC UPDATEUSAGE play here any role ? just a question 🙂
To whether or not sp_helpindex will show spatial indexes? Nope. Not at all.
DBCC UPDATEUSAGE is for updating page & row counts for the catalog views. I'm pretty sure it has no affect on which indexes are listed.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 8:04 am
OK, So sp_helpindex is not the only sql tool in determining ALL the indexes/keys a TABLE contains.
Would SSMS be your one-stop shopping or is there more than one SP or whatever, that together tells you what ALL the indexes/keys exists?
September 30, 2010 at 8:20 am
You can do a select on sys.indexes to get all the indexes in your DB.
You can do a select on sys.spatial_indexes to get all the spacial indexes (which you'll most likely never use).
You can do a select on sys.xml_indexes to get all the xml indexes in your DB.
If you need to look at only one table you can join it with sys.tables.
September 30, 2010 at 8:30 am
Oliiii (9/30/2010)
You can do a select on sys.spatial_indexes to get all the spacial indexes (which you'll most likely never use).
Never is a big word. Where I work is already making a huge investment in spatial data and we're using the indexes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 30, 2010 at 8:36 am
Oliiii (9/30/2010)
You can do a select on sys.indexes to get all the indexes in your DB.You can do a select on sys.spatial_indexes to get all the spacial indexes (which you'll most likely never use).
You can do a select on sys.xml_indexes to get all the xml indexes in your DB.
If you need to look at only one table you can join it with sys.tables.
Thank you. I will try to come up with a sql statement for this.
September 30, 2010 at 9:20 am
Correction, "sys.spatial_indexes" won't be part of my research since it doesn't exist in SQL2005. I only found it in SQL2008.
🙂
September 30, 2010 at 1:33 pm
FYI: I've found that using SYS.INDEXES and SYS.XML_INDEXES should work in my environment.
...thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply