Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Use T-SQL to determine if a column has an index

    Hello all,
    I'm in the process of writing a script to document a db. So far I have the tables and fields along with their datatypes and sizes. This is all using the sys* tables in sql2K. For now my last step is to mark which columns have an index.

    I'm finding plenty on how to get all the indexes on a tables and the columns of the index, but I'm not finding anything as far as the reverse which is to start with a column and get any index on it.

    The only way I see to do it is to loop through each table and get the indexed columns and put this into a separate table that I can link to by column name. I don't want to create another table because I'd like to run this on any server I want. I suppose I could use a temp table. I was a little worried about size, but thinking it over there would have to be a ton of indexes for that to be a problem. Regardless I'd like to know if there is an easier way I'm missing.

    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Jan 2004
    Posts
    145
    I think I found the answer which is the sysindexkeys table. I can link the table and column to that table and then to sysindexes to get the index info. However I think I have to do something with the index status column to get rid of some fluff.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Purely an observation, but SQL Spec will document SQL Server and much more. I don't often tout packages, but SQL Spec is written and supported by DBForum's own Jezemine and it is a package that I frequently recommend to DBAs that work for my clients.

    As I said above I don't often tout packages, but download SQL Spec and try it... I'd be shocked if you can justify writing your own package after you spend any significant amount of time using SQL Spec.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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