Displays Index Info for whatever database it is run against
2001-08-22
728 reads
Displays Index Info for whatever database it is run against
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