Skip to Content
author's profile photo Former Member
Former Member

List of indexes and columns for a database.


Hi

Do you know the SQL command to get the list of indexes and associated columns for all tables for a given database ?

The following only shows me the table and index name but I would also like to get the colums for each index

SELECT o.name, i.name FROM sysobjects o JOIN sysindexes i ON (o.id = i.id)

Can you pls help

Thanks

H.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Posted on Jul 22, 2014 at 12:00 PM

    There isn't a single command that will do that.

    There is the sp_helpindex stored procedure which will give you the information on indexes one table at a time, you could call it in a loop, but there is other information in there as well, so the output would be messy.

    You can look at the source code for sp_helpindex to find out how it decodes the key column names.

    use sybsystemprocs

    go

    sp_helptext sp_helpindex

    go

    The core of it is this loop, which builds up a list of the column names in @keys, a varchar(1024) declared earlier.

    /*
    ** First we'll figure out what the keys are.
    */
    declare @i int
    declare @thiskey varchar(255)
    declare @sorder char(4)
    declare @lastindid int
    declare @indname varchar(255)

    select @keys = "", @i = 1

    set nocount on

    while @i <= 31
    begin
    select @thiskey = index_col(@objname, @indid , @i)

    if (@thiskey is NULL)
    begin
    goto keysdone
    end

    if @i > 1
    begin
    select @keys = @keys + ", "
    end

    /*select @keys = @keys + index_col(@objname, @indid, @i)*/
    select @keys = @keys + @thiskey

    /*
    ** Get the sort order of the column using index_colorder()
    ** This support is added for handling descending keys.
    */
    select @sorder = index_colorder(@objname, @indid, @i)
    if (@sorder = "DESC")
    select @keys = @keys + " " + @sorder

    /*
    ** Increment @i so it will check for the next key.
    */
    select @i = @i + 1

    end


    /*
    ** When we get here we now have all the keys.
    */
    keysdone:
    set nocount off

    -bret

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.