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