September 3, 2003 at 3:01 am
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.
September 3, 2003 at 4:39 am
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.
September 4, 2003 at 5:00 pm
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
September 5, 2003 at 11:43 am
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
September 8, 2003 at 4:31 am
look at
http://qa.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