Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2002
    Posts
    3

    Lightbulb Unanswered: [system tables] Index list

    Hi,

    I need to write a sp_helpIndex kind in SQL.
    I still miss the name of the column the index is refering to !

    SELECT i.name, s.name, i.status
    FROM mybasename.dbo.sysindexes i,
    mybasename.dbo.syssegments s,
    mybasename.dbo.sysobjects o "
    WHERE i.indid!=0 AND
    i.segment=s.segment AND
    i.id=o.id AND o.name=mytabename

    It lists the indexes with their segment and status for a given table.

    How can i introduce the column of the table ???


    thanx

  2. #2
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You need to tie the id from sysindexes (or sysobjects) into the id from syscolumns. syscolumns will have a row for each column in the index for a specfic ID.
    Thanks,

    Matt

  3. #3
    Join Date
    Mar 2002
    Posts
    3

    Talking



    In fact the only way I found to link an Index with the columns concerned is the function : index_col(object name, index id, nd of the index) (as there is no link in the system tables diagram to answer my need).

    I did this way. I works very well.

    Thanks for your answer.
    @+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •