SQL Server Performance Forum – Threads Archive

To list out all the indexes for a table.

Hi friends,
Is there any way like SQL query or script to list out all the indexes to a given table in the database?
Thanks..!

exec sp_helpindex TABLENAME

To get more information about the indexes than what sp_helpindex can give, you may use the following query: select * from sysindexes where id = object_id(‘TABLENAME’)

When MS supplied enough stored procedures and other tools to get information from System tables why to use and query system tables directly, better not to and its not recommended. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

True, if there are system procedures available for the task, reading from system tables should be avoided. This is helpful in maintaining the compatibility with new versions of SQL as well. Most of the DOCUMENTED system procedures are compatible in newer versions of SQL Server. But if the underlying tables change, your scripts won’t work the same again. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

See ifhttp://www.devx.com/codemag/Article/11516 is of any use to you. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by satya When MS supplied enough stored procedures and other tools to get information from System tables why to use and query system tables directly, better not to and its not recommended. _________ Satya SKJ Moderator SQL-Server-Performance.Com <hr height="1" noshade id="quote"></blockquote id="quote"> I agree with you Satya, But when you don’t get enough information with the system SPs, you have to query the system tables and as long as you are not modifying them, it is ok to query them now and then. For example, in the above case if you want to programmatically retrive the Fill factor and number of data pages being used by an index, I don’t know of any easier way than to a quick query on sysindexes. I don’t know what is the problem with me, but I can’t get my day over without atleast querying one system table <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Then I suggest you to refer to books online and study system stored procedures, it helps.
Feel free to comeback to the forum if any information in doubt. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

You can get the fill factor using indexproperty select indexproperty(object_id(‘table_name’),’index_name’, ‘IndexFillFactor’) Bambola.

Also, sometimes you need to use the results of a system stored procedure in a JOIN. Rather than use the system tables to achieve this (where clearly the sp would be inconvenient), you should first refer to the INFORMATION_SCHEMA views, as these often contain useful information in a form which is abstracted away from the underlying system tables.

quote:Originally posted by satya Then I suggest you to refer to books online and study system stored procedures, it helps.
Feel free to comeback to the forum if any information in doubt. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Please take a look at stats_date function in BOL. The example here queries on sysobjects and sysindexes tables. What does this suggest ?

Hey guys just cool it!!! [:0] The suggesttions provided in the forum are generic and can be modified for specific cases.[B)]
Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Also, here is an excerpt from the ‘System tables’ overview in BOL. System tables should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers. Reference of documented columns in system tables is permissible.

My apologies to everyone (especially to the thread starter <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> for diverting the topic here. Have a nice weekend.

I see no reason for you to appolgize, ykchakri, you have done nothing wrong. You
expressed your opinion trying to help, and your help is being appreciated. This is what these forums are all about. Besides, I believe that vvkp recieved an answer to his post,
so no harm was done if we diverted from the topic. As for the system tables issue. Many times there are system stored procedure or functions
that will return the information you are looking for. Other times there aren’t. In this
case I will use system tables though I’m will probably not to use them in my applications. Recently I read an article written by Kalen Delaney explainning that she do not hesitate
using undocumented columns when analyzing or troubleshooting, as they contain a lot of useful information that cannot be accessed with other tools. Of course, everyone if free to do as they please. Bambola.
If we are to achieve results never before accomplished, we must expect to employ methods never before attempted. – Francis Bacon

I too totally agree with Bambala…no need of saying apology by ykchakri. Thanks for you all, for the help and support to a beginner like me.
vvkp

I use this script
select SO.Name as Tabla, SI.name as ‘Index’, SFG.groupname as ‘Filegroup’
from sysobjects as SO
join sysindexes as SI
on SO.Id = SI.id
join sysfilegroups as SFG
on SI.GroupId = SFG.GroupId order by SFG.GroupName ,
SO.Name, SI.name
I see this post may be to late, apology. Luis Martin

http://www.csvreader.com/posts/index_list.php or http://www.sqldatadictionary.com/ if someone finds this and wants the new tables.

Bob: Welcome to the forums.
This thread is 11 years old.

]]>