Schema: How do I list all the indexes in a database?

SQL Server 2000

Yes, you can use the following for a specific table:

EXEC sp_helpindex 'tablename'

This returns index_name, index_description, and index_keys. The index_description column tells whether or not the index is clustered, and which filegroup it resides on. The index_keys column tells you the column names that participate in the index, and from what I can tell, these are always in the order they are created (a negative symbol (-) denotes that the column is in DESC order).

This is great, but does not provide all of the information I'm often looking for.

In order to return everything I wanted to know about the indexes in my database, I needed to create a couple of extra helper functions. (Unfortunately, indexes are not covered in the INFORMATION_SCHEMA views, so we need to rely on system tables like sysindexes and sysfilegroups, and system functions like INDEXPROPERTY() and INDEX_COL().) The first function is not required, but makes the second function quite tidier, IMHO:

-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@object_id INT,
@index_id TINYINT,
@column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @r NVARCHAR(5)
SELECT @r = CASE INDEXKEY_PROPERTY
(
@object_id,
@index_id,
@column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @r
END
GO

-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@table_name SYSNAME,
@object_id INT,
@index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@colnames NVARCHAR(4000),
@thisColID INT,
@thisColName SYSNAME

SET @colnames = INDEX_COL(@table_name, @index_id, 1)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, 1)

SET @thisColID = 2
SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)

WHILE (@thisColName IS NOT NULL)
BEGIN
SET @thisColID = @thisColID + 1
SET @colnames = @colnames + ', ' + @thisColName

SET @thisColName = INDEX_COL(@table_name, @index_id, @thisColID)
+ dbo.GetIndexColumnOrder(@object_id, @index_id, @thisColID)
END
RETURN @colNames
END
GO

These functions are based largely on sp_helpindex, and while they avoid cursors, they are still not likely to be very efficient as the functions will need to be called multiple times.

Now that we have these functions, we can create this view:

CREATE VIEW dbo.vAllIndexes
AS
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id, i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
GO

This will give you a handy resultset, but does not specify whether the index is a PRIMARY KEY CONSTRAINT. You can do that by joining against INFORMATION_SCHEMA.TABLE_CONSTRAINTS:

SELECT
v.*,
[PrimaryKey?] = CASE
WHEN T.TABLE_NAME IS NOT NULL THEN 1
ELSE 0
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'

This doesn't take into account same-named tables owned by different users, but if you look at the alternative (see the source code for sp_pkeys), it is probably a valid solution for most of us, where dbo is the de facto owner of all objects.

With that limitation in mind, we can take it one step further by generating the CREATE INDEX / ADD CONSTRAINT statements:

SELECT
CASE WHEN T.TABLE_NAME IS NULL THEN
'CREATE '
+ CASE IS_UNIQUE WHEN 1 THEN ' UNIQUE' ELSE '' END
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' INDEX [' + INDEX_NAME + '] ON [' + v.TABLE_NAME + ']'
+ ' (' + COLUMN_LIST + ') ON ' + FILE_GROUP
ELSE
'ALTER TABLE ['+T.TABLE_NAME+']'
+' ADD CONSTRAINT ['+INDEX_NAME+']'
+' PRIMARY KEY '
+ CASE IS_CLUSTERED WHEN 1 THEN ' CLUSTERED' ELSE '' END
+ ' (' + COLUMN_LIST + ')'
END
FROM
dbo.vAllIndexes v
LEFT OUTER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
ORDER BY
v.TABLE_NAME,
IS_CLUSTERED DESC

This is what I have to offer for now, and I realize it is pretty quick and dirty. I'll be working on a similar script using the SQL Server 2005 catalog views, but I'll save that for another day.

MS Access

Since Access stopped storing object names in its MSys* tables, it is nearly impossible to perform administrative tasks within the database itself, unless you want to point and click through a GUI. So I developed the following script with ADOX:

<%
Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("db.mdb")

Set adox = CreateObject("ADOX.Catalog")
Set adox.ActiveConnection = conn

Response.Write "<table border=1 cellspacing=0 cellpadding=5>" & _
"<tr valign=top bgcolor=#EDEDED>" & _
"<th>Table Name" & _
"<th>Index Name" & _
"<th>Unique?" & _
"<th>Clustered?" & _
"<th>Primary Key?" & _
"<th>Column Name(s)" & _
"<th>Sort Order"

For Each table In adox.Tables
For Each index In table.Indexes
Response.Write "<tr valign=top>" & _
"<td>" & table.Name & _
"<td>" & index.Name & _
"<td>" & index.Unique & _
"<td>" & index.Clustered & _
"<td>" & index.PrimaryKey

colNames = ""
sortOrders = ""

For Each col In index.Columns
colNames = colNames & col.Name & "<br>"
so = "ASC"
If col.SortOrder = 2 Then so = "DESC"
sortOrders = sortOrders & so & "<br>"
Next

Response.Write "<td>" & colNames & _
"<td>" & sortOrders
Next
Next

Response.Write "</table>"

Set adox = Nothing
conn.Close()
Set conn = Nothing
%>



// home // advanced search // news // categories // sql build chart // downloads // statistics
ASP FAQ

Schema: How do I list all the indexes in a database?

Related Articles