Querying the Sysindexes System Table

Congratulations to Brian Andrews, systems consultant at Watson Wyatt Worldwide in Washington, D.C., and Pete Spencer, technical consultant for business intelligence at London, U.K.-based Conchango. Brian won first prize of $100 for the best solution to the September Reader Challenge, "Querying the Sysindexes System Table." Pete won second prize of $50. Here’s a recap of the problem and the solution to the September Reader Challenge.

Problem

As the DBA for his company’s SQL Server 2000 and 7.0 installations, Bob often needs to troubleshoot query-performance problems. Because SQL Server doesn’t have a documented system procedure that provides details for all of a database’s indexes, Bob decides to create his own procedure that queries the sysindexes system table for this information. Help Bob write a query that returns the following information:

names of user tables and their indexes
type of index
uniqueness of the index
date of the last statistics update

Solution

The sysindexes system table that Bob wants to query contains a row for every index, column statistics, and text and image column in every table. Bob doesn’t need all these details, so he has to build a query that retrieves the data he wants while filtering out extraneous information. First, Bob creates a SELECT statement that uses the OBJECT_NAME() function and index name to list table names from the sysindexes system table:

SELECT OBJECT_NAME( i.id ) AS TableName,
           i.name AS IndexName
    FROM sysindexes AS i

This two-line query retrieves all table names, so Bob’s next step is to limit the table list to user tables. He trims the list by adding a WHERE clause that uses the OBJECTPROPERTY() metadata function (which returns information about specific database objects such as tables, constraints, and triggers) and its IsMSShipped property (which registers false for all user tables and therefore is an easy way to filter system tables). Then he can use the USER_NAME() function and the OwnerID property to fetch the name of the user who owns the table. The following expanded query retrieves the table names and owner names that Bob wants:

SELECT USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) )
                AS OwnerName,
       OBJECT_NAME( i.id ) AS TableName, 
       i.name AS IndexName
  FROM sysindexes AS i
WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0

However, Bob still needs to narrow the scope of his data considerably, so he uses the INDEXPROPERTY() metadata function to retrieve only the index rows and filter out the column statistics and hypothetical indexes. He can filter the text and image column entries by looking at the indid column value. For indexes, the value of indid ranges from 1 to 150. Bob again modifies the query to produce the following code:

SELECT USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) )
                AS OwnerName,
       OBJECT_NAME( i.id ) AS TableName,
       i.name AS IndexName
   FROM sysindexes AS i
   WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 And
       1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics'     ) ,
              INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
              INDEXPROPERTY( i.id , i.name , 'IsHypothetical'   ) ) And
         i.indid BETWEEN 1 And 250

Now Bob’s query is close to extracting the specific data he wants. He only needs to add the INDEXPROPERTY() function’s IsClustered property, which determines the index type, and IsUnique property, which determines whether the index is unique. And to finish his task, he uses the STATS_DATE() system function, which provides the date of the last statistics update. Here’s the complete query:

SELECT USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) ) 
                AS OwnerName,
     OBJECT_NAME( i.id ) AS TableName,
	 i.name AS IndexName
      CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
             WHEN 1 THEN 'YES'
             ELSE 'NO'
      END AS IsClustered,
      CASE INDEXPROPERTY( i.id , i.name , 'IsUnique'    )
	        WHEN 1 THEN 'YES'
            ELSE 'NO'
      END AS IsUnique,
      STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
  FROM sysindexes AS i
WHERE OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 And
      1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics'   ) ,
          INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
          INDEXPROPERTY( i.id , i.name , 'IsHypothetical'   ) ) And
      i.indid BETWEEN 1 And 250
ORDER BY OwnerName, TableName, IndexName

Comments

Plain text