Index list in Server Management studio

  • Hi,

    In SQL 2000, we could have a list of all indexes on a table at a glance.

    How can I provide this in Management Studio ? I don't like to have one by one.

    Is there any query to list all indexes on a table ?

    I expect a list like this :

    <index-name> < columns>

    e.g: <booking> <booking_id,booking_name,...>

    Not:<booking><booking_id>

    <booking><booking_name>

    ...

  • Open object explorer, connect to the server

    Expand out database->tables->the table you're interested in-> indexes

    You can write a query, the views that you need are sys.indexes and sys.index_columns. It'll take some fun manipulation to get it into the format that you want, but it is possible.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks Gail,

    As I said before, in database->tables-> indexes, I should check every index one by one

    What I want, having all indexes and columns in a window

  • check if below code works..it definitely NOT faster as it uses cursor approach, but it should give the result you expected..

    Declare @vchrFileName varchar(255)

    Declare @vchrSQLStr varchar(2000)

    set ansi_warnings off

    create table #IndexScan1 (ObjName varchar(100) DEFAULT ('@update@'),Index_Name varchar(255),Index_Desc varchar(1000),Index_keys varchar(100))

    DECLARE csrFileCursor CURSOR FOR

    SELECT SCHEMA_NAME(schema_id)+'.'+name FROM sys.objects where type='u'

    OPEN csrFileCursor

    FETCH NEXT FROM csrFileCursor Into @vchrFileName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @vchrSQLStr = 'insert into #IndexScan1 (index_name,index_desc,index_keys) EXEC sp_helpindex '''+@vchrFileName+''''

    exec(@vchrSQLStr)

    SET @vchrSQLStr ='update #IndexScan1 set ObjName ='+''''+@vchrFileName+''' where ObjName =''@update@'''

    exec(@vchrSQLStr)

    FETCH NEXT FROM csrFileCursor Into @vchrFileName

    END

    Close csrFileCursor

    Deallocate csrFileCursor

    SELECT

    Objname,

    Index_keys = REPLACE(

    (

    SELECT

    Index_keys AS [data()]

    FROM

    #IndexScan1 ixs

    WHERE

    ixs.Objname = c.Objname

    ORDER BY

    Index_keys

    FOR XML PATH ('')

    ), ' ', ' -- ')

    FROM

    #IndexScan1 c

    group by Objname

    ORDER BY Objname

    Drop Table #IndexScan1

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

  • This query will give the result

    select i.name as [Index],i.type_desc as [Index type],T.name as [Table name],C.name as [ColumnName]

    from sys.tables T inner join sys.columns C on C.object_id=T.object_id

    inner join sys.indexes i on i.object_id=T.object_id

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • malleswarareddy_m (6/2/2010)


    This query will give the result

    select i.name as [Index],i.type_desc as [Index type],T.name as [Table name],C.name as [ColumnName]

    from sys.tables T inner join sys.columns C on C.object_id=T.object_id

    inner join sys.indexes i on i.object_id=T.object_id

    No, that'll give you all the columns in the table, not all the columns in the index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • this may be worked.i checked for my database.Please suggest me if it is wrong.

    select

    case when index_id=1 then 'Clustered'

    else 'Non-Clustered' end as [Index Column],

    T.name as [Table name],C.name as [ColumnName]

    from sys.tables T inner join sys.columns C on C.object_id=T.object_id

    inner join sys.index_columns i on i.index_column_id=C.column_id

    and i.object_id=C.object_id

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • a.shahnazi (6/1/2010)


    Hi,

    In SQL 2000, we could have a list of all indexes on a table at a glance.

    How can I provide this in Management Studio ? I don't like to have one by one.

    Is there any query to list all indexes on a table ?

    I expect a list like this :

    <index-name> < columns>

    e.g: <booking> <booking_id,booking_name,...>

    Not:<booking><booking_id>

    <booking><booking_name>

    ...

    To me it sounds like you just want to use

    exec sp_helpindex 'tablename'

    The downside is that included columns are not referenced in this list ... to get those as well you would need to do a more complex query with dmv's.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Thanks All,

    What I want is "sp_helpindex" idea.

    NewBeeSQL's script is fantastic, just needs a small change in size of Index_keys column in temp table.

    Regards

  • Glad my script helped you...

    -----------------------------------------------------------------------
    For better assistance in answering your questions[/url]
    Perforamance Issues[/url]
    Cross Tabs and Pivots[/url]
    Cross Apply[/url]
    The Numbers or Tally Table- Jeff Moden[/url]

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply