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

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Schema Tutorials :: Schema: How do I list all the indexes in a database?


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 
%>

Related Articles

Schema: How do I list the databases on my server?
Schema: How do I show all the primary keys in a database?
Schema: How do I show all the triggers in a database?
Schema: How do I show the columns for a table?
Schema: How do I show the description property of a column?
Schema: How do I show the parameters for a function or stored procedure?
Schema: How do I show the stored procedures in a database?
Schema: How do I show the tables in a database?
Schema: How do I show the user-defined functions (UDFs) in a database?
Schema: How do I show the views in a SQL Server database?

 

 


Created: 5/23/2005 | Last Updated: 5/25/2005 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (240)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...