August 13, 2004 at 5:46 am
I'm looking for a query to retrieve all indexes incl. all their column from a table.
I need to get something like:
Index;Unique;Column;ColPos
index1; 1; column1; 1
index1; 1; column2; 2
index2; 0; column2; 1
index3; 0; column2; 1
index3; 0; column3; 2
I already found the following query, but it gives me only the first column, no further ones:
Select keyno, tb.name as tbl_name,ix.name as ind_name,co.name as col_name,ik.colid as col_id,
ix.id as tbl_id, ix.indid as ind_id, ix.status
from sysObjects TB
inner join sysIndexes ix
on ix.id = tb.id
inner join sysIndexKeys IK
on ik.id = ix.id
and ik.indid = ix.indid
inner join sysColumns CO
on co.id = ik.id
and co.colid = ik.colid
Where tb.name like '
order by tb.name,ix.name,ik.colid
Any help kindly appreciated
Ralf
August 16, 2004 at 6:48 am
This will also retrieve the indexes of the indexed views and also tell you if the index is a primary key.
SELECT O.Name AS tbl_name, i.name AS ind_name, C.name AS col_name, USER_NAME(O.uid) AS Owner, c.colid, k.Keyno, CASE WHEN I.indid BETWEEN
1 AND 254 AND (I.status & 2048 = 2048 OR
I.Status = 16402 AND O.XType = 'V') THEN 1 ELSE 0 END AS IsPK
FROM dbo.sysobjects o INNER JOIN
dbo.sysindexes I ON o.id = i.id INNER JOIN
dbo.sysindexkeys K ON I.id = K.id AND I.Indid = K.Indid INNER JOIN
dbo.syscolumns c ON K.id = C.id AND K.colid = C.Colid
WHERE LEFT(i.name, 8) '_WA_Sys_' AND o.status >= 0
ORDER BY O.name, I.Name, K.keyno
August 17, 2004 at 1:41 am
Thanks Remi,
works well, now I only miss the information if the index is unique or not 😉
Ralf
August 17, 2004 at 2:30 am
Found it myself:
sysindexes.status & 2 tells if an index is unique
Ralf
August 17, 2004 at 5:22 am
Sorry... I missed that part of the post but I'm glad the query could help you.
August 17, 2004 at 11:47 am
drop table ##Indexes
go
create table ##Indexes (
TableName varchar(60),
IndexName varchar(60),
IndexDescription varchar(100),
IndexKeys varchar(125),
IndexType int
)
go
declare @Name varchar(500),
@Count int,
@SQL nvarchar(500)
set nocount on
DECLARE Process_cursor CURSOR
FOR
select name
from sysobjects
where type = 'u'
and name not like 'dt%'
order by name
OPEN Process_cursor
FETCH NEXT FROM Process_cursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
print @NAME
insert ##Indexes (IndexName,IndexDescription,IndexKeys)
exec sp_helpindex @Name
update ##Indexes
set TableName = @Name
where TableName is null
--print @SQL
FETCH NEXT FROM Process_cursor INTO @Name
END
close Process_cursor
deallocate Process_cursor
go
update ##Indexes
set IndexType = 1
where substring(IndexDescription, 1,10) = 'clustered,'
update ##Indexes
set IndexType = 0
where IndexType is null
select * From ##Indexes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply