List Indexes in SQL Server

Useful Usenet post I found while looking up a problem.

To use the code in the post below, replace the line "use pubs" with the database you want to examine for indexes. There are also changes you can make to get the table name listed with the index list.

Before the Usenet post, I've listed my modified SQL query. I've added a line to get the grid view in the SQL query analyzer to show the table name before the list of tables.


use pubs

DECLARE GetTables CURSOR READ_ONLY
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

DECLARE @TableName sysname
OPEN GetTables

FETCH NEXT FROM GetTables INTO @TableName
WHILE (@@fetch_status = 0)
BEGIN
select @TableName as table_name
EXEC sp_helpindex @TableName
print 'Table Name =' + @TableName
FETCH NEXT FROM GetTables INTO @TableName
END

CLOSE GetTables
DEALLOCATE GetTables
GO


--------------------------

From: BP Margolin (bpmargo@attglobal.net)
Subject: Re: List Indexes
View: Complete Thread (4 articles)
Original Format
Newsgroups: comp.databases.ms-sqlserver
Date: 2001-07-21 13:37:41 PST

Joshua,

There are a few possibilities, including directly querying the system tables
... sysindexes, for example.

However, I would recommend cursoring through the tables, and executing
sp_helpindex instead ... something like:

use pubs

DECLARE GetTables CURSOR READ_ONLY
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY (OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

DECLARE @TableName sysname
OPEN GetTables

FETCH NEXT FROM GetTables INTO @TableName
WHILE (@@fetch_status = 0)
BEGIN
EXEC sp_helpindex @TableName
FETCH NEXT FROM GetTables INTO @TableName
END

CLOSE GetTables
DEALLOCATE GetTables
GO

Note that the sp_helpindex in SQL Server 7.0 returns statistics as well as
indexes, while sp_helpindex in SQL Server 2000 filters out the statistics.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"Joshua Goodstein" wrote in message
news:924a90f8.0106190436.7511c24b@posting.google.com...
> How can i list all particular indexes for a single database. I just
> can't nail it. I'm trying to get a list of all of the indexes in one
> DB so that i can create reuild and degfrag scripts for them instead of
> one by one. Any help would be greatly appreciated
>
> Joshua Goodstein

--------------------------