hx_IndexInformation

DROP PROCEDURE hx_IndexInformation
go

/* Robert Vallee 08/01/2001
rvallee@hybridx.com
input:None
output:Table format
Desc:Displays Index Info for whatever database it is run against.
Warnings: None.
*/
CREATE PROCEDURE hx_IndexInformation AS

SET NOCOUNT ON

SELECT 
'ObjectType' = case    
WHEN sysobjects.xtype = 'P' THEN 'Stored Procedure'
WHEN sysobjects.xtype = 'U' THEN 'User Table'
WHEN sysobjects.xtype = 'V' THEN 'View'
WHEN sysobjects.xtype = 'S' THEN 'System Table'
WHEN sysobjects.xtype = 'fn' THEN 'Scalar function'
WHEN sysobjects.xtype = 'TF' THEN 'Table function'
WHEN sysobjects.xtype = 'if' THEN 'Inlined table-function'
END,
'Internal_system_status_information' = case
WHEN sysindexes.status = 1 THEN 'Cancel command if attempt to insert duplicate key'
WHEN sysindexes.status = 2 THEN 'Unique index'
WHEN sysindexes.status = 4 THEN 'Cancel command if attempt to insert duplicate row'
WHEN sysindexes.status = 16 THEN 'Clustered index'
WHEN sysindexes.status = 64 THEN 'Index allows duplicate rows'
WHEN sysindexes.status = 2048 THEN 'Index used to enforce PRIMARY KEY constraint'
WHEN sysindexes.status = 4096 THEN 'Index used to enforce UNIQUE constraint'
ELSE 'Definition not in list or does not exists'
END,

SUBSTRING(sysobjects.name, 1, 45) AS Table_Name, 
    SUBSTRING(sysindexes.name, 1, 50) AS Index_Name, 
      sysindexes.rows AS Number_of_Rows, 
      sysobjects.crdate AS Object_Creation_Date,
      sysindexes.indid as ID_of_Index, 

        'Type_of_Index' = case 
WHEN sysindexes.indid = 0 THEN 'For the data page level of a table without a clustered index.' 
WHEN sysindexes.indid = 1 THEN 'Clustered index'
WHEN sysindexes.indid >= 2 THEN 'Nonclustered index'
WHEN sysindexes.indid >= 255 THEN 'For the chain of data pages containing TEXT or IMAGE data. ' 
END,
sysindexes.minlen as Minimum_size_of_a_row,
sysindexes.xmaxlen as Maximum_size_of_a_row,
sysindexes.maxirow as Maximum_size_of_a_nonleaf_index_row


FROM sysobjects JOIN
     sysindexes ON sysobjects.id = sysindexes.id
GO