display all indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

display all indexes

Author

Topic

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-01-13 : 07:47:01

hello,
is it possible to display all indexes on each table in a database using T-SQL or is there an already created sp ?

thanks for any info.
Jamie

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-01-13 : 07:58:26

declare @name varchar(100)
declare @table table (tabname varchar(100))
insert into @table
select name from sysobjects where xtype='U'
while exists (select * from @table)
begin
select top 1 @name =tabname from @table
exec sp_helpindex @name
delete from @table where tabname =@name
end

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-01-13 : 08:04:23

how do I use this ?
I have tried running but get an error incorrect syntax near table.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-01-13 : 08:29:50

v7?
try
create table #table (tabname varchar(100))
declare @name varchar(100)
insert into #table
select name from sysobjects where xtype='U'
while exists (select * from #table)
begin
select top 1 @name =tabname from #table
exec sp_helpindex @name
delete from #table where tabname =@name
end

What info do you want?
Just the index names, fields used?
You can probably get a more usable format if you need it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jamie
Aged Yak Warrior

542 Posts

Posted - 2004-01-13 : 08:37:18

yes, unfortunately we're still on sql7.
this query works, thanks.
the info. displayed from this query is exactly what I need.
cheers !
Jamie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources