SQL Server Performance

To list out all the indexes for a table.

Discussion in 'T-SQL Performance Tuning for Developers' started by vvkp, Aug 13, 2003.

  1. vvkp New Member

    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..!
  2. Chappy New Member

    exec sp_helpindex TABLENAME
  3. ykchakri New Member

    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')

  4. satya Moderator

    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
  5. gaurav_bindlish New Member

    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
  6. gaurav_bindlish New Member

  7. ykchakri New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by satya</i><br /><br />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.<br /><br />_________<br />Satya SKJ<br />Moderator<br />SQL-Server-Performance.Com<br /><br /><hr height="1" noshade id="quote"></blockquote id="quote"></font id="quote"><br /><br />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. <br />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. <br />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=':)' />
  8. satya Moderator

    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
  9. bambola New Member

    You can get the fill factor using indexproperty

    select indexproperty(object_id('table_name'),'index_name', 'IndexFillFactor')

    Bambola.
  10. Chappy New Member

    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.
  11. ykchakri New Member

    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 ?
  12. gaurav_bindlish New Member

    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
  13. ykchakri New Member

    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.
  14. ykchakri New Member

    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.
  15. bambola New Member

    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
  16. vvkp New Member

    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
  17. Luis Martin Moderator

    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

Share This Page