Script for finding all indexes in a database
-
I wrote this script to give myself an easy to way to gather the information on all the indexes that have been created for a specified database. I would have preferred not to use a cursor, but I couldn't think of a way to do this without one. If anyone reading this can show me a way to write this script without using a cursor, I will be forever greatful.
USE [Database_Name]
DECLARE @TableName sysname
DECLARE findIndexCursor CURSOR
FOR
SELECT DISTINCT so.name
from sysobjects so, sysindexes si
where xtype = 'U'
/* 'U' indicates that the object is a User Table*/
and so.Id=si.id
/* This checks that the value of the "Id" column in the sysobjects table is found in the "id"
column of the sysindexes table */
and indid between 1 and 254
/* Clustered Indexes have an indid of "1", nonclustered indexes have an indid between 2 and 254*/
order by so.name
OPEN findIndexCursor
FETCH NEXT FROM findIndexCursor
INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
exec sp_helpindex @TableName
FETCH NEXT FROM findIndexCursor
INTO @TableName
END
CLOSE findIndexCursor
DEALLOCATE findIndexCursor
“If you're not outraged at the media, you haven't been paying attention.”
-
Hi hankreaden2002,
this is what I use
SELECT * FROM sysindexes i
WHERE i.indid BETWEEN 1 AND 254
AND objectproperty(id, 'IsUserTable') = 1
AND indexproperty (id, name, 'IsStatistics') = 0
ORDER BY object_name(id), i.indid DESCHTH
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
And what about this extension of Frank's query:
SELECTt.name as TableName,
i.name as IndexName
FROMsysindexes i join sysobjects t on t.id = i.id
WHEREi.indid BETWEEN 1 AND 254
ANDobjectproperty(t.id, 'IsUserTable') = 1
ANDindexproperty (i.id, i.name, 'IsStatistics') = 0
ORDER
BYt.name,
i.indid asc
Bye
Gabor
Bye
Gabor -
Try sp_statistics [Table_NAME]
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself. -
Frank and Gabor:
Thanks for the suggestions. My only issue with your scripts is that they either don't return the index keys (Gabor) or they return the index keys in hexadecimal format (Frank). I am going to work with these scripts to see if I can join the sysindexkeys table to add this information.
However, my first attempts at running the amended scripts had a longer execution time that running my script with the cursor. I'll keep working on this an keep everyone posted.
“If you're not outraged at the media, you haven't been paying attention.”
-
Please post the solution, when you got one!
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
hankrearden2002 & Frank
Try this script. I have modified Gabor's script to add the associated column that go with the index.
SELECT LEFT(t.name, 40) as TableName,
LEFT(i.name, 40) as IndexName,
LEFT(c.name, 40) as [Column]
FROM sysindexes i join sysobjects t on t.id = i.id
inner join sysindexkeys k on i.id = k.id and i.indid = k.indid
inner join syscolumns c on t.id = c.id and k.colid = c.colid
WHERE i.indid BETWEEN 1 AND 254
AND objectproperty(t.id, 'IsUserTable') = 1
AND indexproperty (i.id, i.name, 'IsStatistics') = 0
ORDER
BY t.name,
i.indid asc,
keyno
Dave
-
Version II, may need a bit more work!
Create Function Keys(@TableId Int,@IndexId Int) Returns Varchar(1000) as
Begin
Declare @Answ Varchar(1000),@i TinyInt
Select @Answ=',',@i=1
While Not (Index_Col(Object_Name(@TableId), @IndexId, @i) Is Null)
Begin
Set @Answ=@Answ+Cast(Index_Col(Object_Name(@TableId), @IndexId, @i) as Varchar(100))+
Case When IndexKey_Property(@TableId, @IndexId,@i,'isdescending') = 0
Then '' Else 'Desc' End+','
Set @i=@i+1
End
Return Right(Left(@Answ,DataLength(@Answ)-1),DataLength(@Answ)-2)
End
Go
SELECT LEFT(Object_Name(Id), 40) as TableName,
LEFT(i.name, 40) as IndexName,
dbo.Keys(i.id,i.indid) as Keys
FROM SysIndexes i
WHERE i.indid BETWEEN 1 AND 254
AND objectproperty(i.id, 'IsUserTable') = 1
AND indexproperty (i.id, i.name, 'IsStatistics') = 0
ORDER BY Object_Name(id),i.indid
Go
Drop function Keys
Go -
exec sp_MSforeachtable @command1 = "print '?'",@command2 = "sp_helpindex '?'"
Far away is close at hand in the images of elsewhere.
Anon. -
I have to say that I like this one the best. It gives me all the information I want and only took 1 second of execution time.
Thanks Dave.
quote:
hankrearden2002 & FrankTry this script. I have modified Gabor's script to add the associated column that go with the index.
SELECT LEFT(t.name, 40) as TableName,
LEFT(i.name, 40) as IndexName,
LEFT(c.name, 40) as [Column]
FROM sysindexes i join sysobjects t on t.id = i.id
inner join sysindexkeys k on i.id = k.id and i.indid = k.indid
inner join syscolumns c on t.id = c.id and k.colid = c.colid
WHERE i.indid BETWEEN 1 AND 254
AND objectproperty(t.id, 'IsUserTable') = 1
AND indexproperty (i.id, i.name, 'IsStatistics') = 0
ORDER
BY t.name,
i.indid asc,
keyno
Dave
“If you're not outraged at the media, you haven't been paying attention.”
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply