Indexes

  • How do i get a list of all indexes (names and columns it indexes) in a database?

    sp_indexes doesn't work. it returns the following error:

    Server: Msg 7411, Level 16, State 1, Procedure sp_indexes, Line 9

    Server 'My_Server' is not configured for DATA ACCESS.

  • Is this for a linked server? If so just open up the linked server and on the last tab you can turn data access on.

  • hi

    try this out and run the result set as a script.

    set nocount on

    declare @tblname varchar(60)

    declare indx cursor for

    select table_name from information_schema.tables where table_type='base table'

    open indx

    fetch next from indx into @tblname

    select 'use pubs'

    while(@@fetch_status=0)

    begin

    select 'sp_helpindex'+' '+''''+@tblname+'''' + char(13) + 'go'

    fetch next from indx into @tblname

    end

    close indx

    deallocate indx

    raghu


    cynosure

  • Try the script below - it will give you the index columns and their position in the index.

    Select Tables.Name As TableName,

    Indexes.Name As IndexName,Keys.KeyNo,Cols.Name As FieldName

    From sysindexes As Indexes

    Inner Join sysobjects As Tables On Indexes.Id = Tables.Id

    Inner Join sysindexkeys As Keys

    On Keys.Id = Indexes.Id And Keys.IndId = Indexes.IndId

    Inner Join dbo.syscolumns Cols On Indexes.Id = Cols.Id And Keys.ColId = Cols.ColId

    Where Tables.xtype = 'U' And

    Indexes.IndId Between 1 And 254 And

    (Indexes.Status & 0x0800) = 0 And (Indexes.Status < 0x10000)

    Order By Tables.Id,Indexes.IndId,Keys.KeyNo


    Stanislav Petkov

  • look at

    http://www.sqlservercentral.com/scripts/contributions/246.asp

    select statements 4, 5, 7 of the script will give you all primary keys, unique keys and other indexes alongside with their respective fields.

    best regards,

    chris.

Viewing 5 posts - 1 through 4 (of 4 total)

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