Finding Indexes

Hi,
Does anyone have a script for or know the easiest way to list the indexes in
all tables (for all user databases) in sql server 2000?
Thanks,
AndrewThat's easy. The easiest way to list all indexes for all tables in a DB is:
select * from MyDB.dbo.sysindexes
Of course, it's not very user friendly - you might want to run it from
the actual DB and throw in some functions like object_name([id]). For
example,
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
order by object_name([id]), indid
For more info see the BOL entries on the sysindexes system table. For
instance, indid 0 for a table is the data (ie. there is no clustered
index for that table - it's a heap), index 1 is the clustered index, 2
to 254 are nonclustered indexes, 255 is for text/image data, etc.
Anyway, check out BOL.
Cheers,
Mike.
Andrew wrote:
> Hi,
> Does anyone have a script for or know the easiest way to list the indexes in
> all tables (for all user databases) in sql server 2000?
> Thanks,
> Andrew
>|||Also, thinks like statistics and hypothetical indexes need to be filtered out. Check out the
INDEXPROPERTY function.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:%23zIgFhy5EHA.3648@.TK2MSFTNGP11.phx.gbl...
> That's easy. The easiest way to list all indexes for all tables in a DB is:
> select * from MyDB.dbo.sysindexes
> Of course, it's not very user friendly - you might want to run it from the actual DB and throw in
> some functions like object_name([id]). For example,
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> order by object_name([id]), indid
> For more info see the BOL entries on the sysindexes system table. For instance, indid 0 for a
> table is the data (ie. there is no clustered index for that table - it's a heap), index 1 is the
> clustered index, 2 to 254 are nonclustered indexes, 255 is for text/image data, etc. Anyway, check
> out BOL.
> Cheers,
> Mike.
> Andrew wrote:
>> Hi,
>> Does anyone have a script for or know the easiest way to list the indexes in
>> all tables (for all user databases) in sql server 2000?
>> Thanks,
>> Andrew|||OK then, how about (not tested):
use MyDB;
select object_name([id]), indid, [name] from dbo.sysindexes
where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
order by object_name([id]), indid
If you want it for all user DBs you can wrap it in a cursor like:
declare @.cmd nvarchar(4000)
declare @.DBName sysname
declare AllUserDBs cursor for
select [name] from master.dbo.sysdatabases
where [name] not in ('master', 'msdb', 'model', 'tempdb',
'distribution', 'pubs', 'Northwind')
order by [name]
open AllUserDBs
fetch next from AllUserDBs into @.DBName
while (@.@.FETCH_STATUS != -1)
begin
if (@.@.FETCH_STATUS != -2)
begin
select @.cmd = 'use [@.DB]; ' +
' select @.DB as [DB], object_name([id]), indid, [name] from
dbo.sysindexes ' +
' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
' order by object_name([id]), indid'
exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
end
fetch next from AllUserDBs into @.DBName
end
close AllUserDBs
deallocate AllUserDBs
(Please pardon any typos - this is untested and off the top of my head.)
Cheers,
Mike.
Tibor Karaszi wrote:
> Also, thinks like statistics and hypothetical indexes need to be
> filtered out. Check out the INDEXPROPERTY function.
>|||Sorry, I found a typo - the WHERE clause should be:
where INDEXPROPERTY([id], [name], 'IsStatistics') = 0
Mike Hodgson wrote:
> OK then, how about (not tested):
> use MyDB;
> select object_name([id]), indid, [name] from dbo.sysindexes
> where INDEXPROPERTY([id], [name], 'IsStatistic') = 0
> order by object_name([id]), indid
> If you want it for all user DBs you can wrap it in a cursor like:
> declare @.cmd nvarchar(4000)
> declare @.DBName sysname
> declare AllUserDBs cursor for
> select [name] from master.dbo.sysdatabases
> where [name] not in ('master', 'msdb', 'model', 'tempdb',
> 'distribution', 'pubs', 'Northwind')
> order by [name]
> open AllUserDBs
> fetch next from AllUserDBs into @.DBName
> while (@.@.FETCH_STATUS != -1)
> begin
> if (@.@.FETCH_STATUS != -2)
> begin
> select @.cmd => 'use [@.DB]; ' +
> ' select @.DB as [DB], object_name([id]), indid, [name]
> from dbo.sysindexes ' +
> ' where INDEXPROPERTY([id], [name], 'IsStatistic') = 0 ' +
> ' order by object_name([id]), indid'
> exec sp_execute @.cmd, '@.DB nvarchar(128)', @.DB = @.DBName
> end
> fetch next from AllUserDBs into @.DBName
> end
> close AllUserDBs
> deallocate AllUserDBs
> (Please pardon any typos - this is untested and off the top of my head.)
> Cheers,
> Mike.
> Tibor Karaszi wrote:
>> Also, thinks like statistics and hypothetical indexes need to be
>> filtered out. Check out the INDEXPROPERTY function.