-- Various reports using INDEXPROPERTY
USE Northwind
GO
-- Get indexes for all tables
SELECT 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 ,
       i.id AS ObjectID , i.indid AS IndexID
FROM sysindexes AS i
WHERE 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,
                 INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
                 INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) ) And
      OBJECTPROPERTY( i.id , 'IsMSShipped' ) = 0 And
      -- To filter out the text/ntext/image columns in sysindexes
      INDEXPROPERTY( i.id , i.name , 'IndexDepth' ) > 0
ORDER BY TableName , IndexID
-- Sample Output:
/*
TableName            IndexName               IsClustered IsUnique LastUpdatedDate          ObjectID    IndexID 
-------------------- ----------------------- ----------- -------- ------------------------ ----------- ------- 
Employees            PK_Employees            Yes         Yes      NULL                       117575457       1 
Employees            LastName                No          No       NULL                       117575457       2 
Employees            PostalCode              No          No       NULL                       117575457       3 
EmployeeTerritories  PK_EmployeeTerritories  No          Yes      1998-11-13 03:12:16.720   1253579504       2 
Order Details        PK_Order_Details        Yes         Yes      2000-04-30 02:06:08.793    661577395       1 
*/
GO
-- Get only automatically created statistics for all tables
SELECT OBJECT_NAME( i.id ) AS TableName , i.name AS AutoStatsName ,
       STATS_DATE( i.id , i.indid ) AS LastUpdatedDate ,
       i.id AS ObjectID , i.indid AS IndexID
FROM sysindexes AS i
WHERE INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) = 1 And
      OBJECTPROPERTY( i.id , 'IsMSShipped' ) = 0
ORDER BY TableName , AutoStatsName
-- Sample Output
/*
TableName             StatsName                        StatsType LastUpdatedDate          ObjectID    IndexID 
--------------------- -------------------------------- --------- ------------------------ ----------- ------- 
Orders                _WA_Sys_ShipCountry_15502E78     Auto      2000-10-18 20:59:27.990    357576312       2 
Products              _WA_Sys_Discontinued_1B0907CE    Auto      2000-10-18 23:27:31.250    453576654       3 
*/
GO

-- Get user-defined statistics & auto-create statistics for all tables
SELECT OBJECT_NAME( i.id ) AS TableName , i.name AS StatsName ,
       CASE INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' )
                WHEN 1 THEN 'Auto'
                ELSE 'Manual'
       END AS StatsType ,
       STATS_DATE( i.id , i.indid ) AS LastUpdatedDate ,
       i.id AS ObjectID , i.indid AS IndexID
FROM sysindexes AS i
WHERE INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) = 1 And
      OBJECTPROPERTY( i.id , 'IsMSShipped' ) = 0
ORDER BY TableName , StatsName
-- Sample Output:
/*
TableName             StatsName                        StatsType LastUpdatedDate          ObjectID    IndexID 
--------------------- -------------------------------- --------- ------------------------ ----------- ------- 
CustomerCustomerDemo  _WA_Sys_CustomerTypeID_46E78A0C  Auto      NULL                      1189579276       3 
Orders                _WA_Sys_ShipCountry_15502E78     Auto      2000-10-18 20:59:27.990    357576312       2 
Products              _WA_Sys_Discontinued_1B0907CE    Auto      2000-10-18 23:27:31.250    453576654       3 
Products              _WA_Sys_UnitsOnOrder_1B0907CE    Auto      2000-11-08 19:59:47.257    453576654       5 
Shippers              Shippers_CompanyName             Manual    2000-11-16 22:02:29.107    293576084       2 
*/
GO

-- Get Hypothetical indexes for all tables
SELECT OBJECT_NAME( i.id ) AS TableName , i.name AS HypoIndexName ,
       STATS_DATE( i.id , i.indid ) AS LastUpdatedDate ,
       i.id AS ObjectID , i.indid AS IndexID
FROM sysindexes AS i
WHERE INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) = 1 And
      OBJECTPROPERTY( i.id , 'IsMSShipped' ) = 0
ORDER BY TableName , HypoIndexName
GO

This page was last updated on May 01, 2006 04:28 PM.