USE sybsystemprocs
GO
CREATE PROCEDURE
--------------------------------------------------------------------------------
--
--     Copyright (c) 1993-2009 Software Gems Pty Ltd
--
--     Derek Asirvadem  V3.7  22 Apr 09
--
--------------------------------------------------------------------------------
--
--  Stripped down version of the commercial sp_HelpIndex_sg V3.7, for public
--  consumption
--
--------------------------------------------------------------------------------

    sp_HelpIndex_sg

AS

    DECLARE	@WhoAmI	VARCHAR(30)
    SET @WhoAmI = OBJECT_NAME(@@PROCID, DB_ID('sybsystemprocs'))

    -- Licensing, space usage & derived stats removed

    IF CHARINDEX("15.",@@version) = 0
        BEGIN
        -- 20400, need 15.0 or greater
        -- RAISERROR 20400, @WhoAmI
        PRINT "This proc requires ASE 15.0 or greater"
        -- otherwise simply change the ROW_COUNT() function syntax
        GOTO EXIT_ERR
        END

    IF @@TRANCOUNT > 0
        BEGIN
        -- 17260, Can't run %1! from within a transaction.
        RAISERROR 17260, @WhoAmI
        GOTO EXIT_ERR
        END

EXEC_UTE:

    -- lights, music, action

    SELECT  -- List the Logical DataStructures (sysindexes) for each table,
            -- showing their rows; type; name; etc
            [Table]   = CASE
                WHEN indid < 2 THEN OBJECT_NAME(id)
                ELSE ""
                END,
            [Lck]     = CASE
                WHEN indid < 2 THEN CASE LOCKSCHEME(id)
                    WHEN "allpages"  THEN "APL"
                    WHEN "datapages" THEN "DPL"
                    WHEN "datarows"  THEN "DRL"
                    ELSE "???"
                    END
                ELSE ""
                END,
            [Rows]    = CASE
                WHEN indid < 2 THEN STR( ROW_COUNT( DB_ID(), id ), 10 )
                        -- for ASE 12.x, use ROWCNT(doampg)
                ELSE ""
                END,
            [Idx]     = indid,                          -- sysindexes.indid
            [DS_Type] = CASE
                WHEN indid =   0 THEN "Heap"
                WHEN indid =   1 THEN "Clst"
                WHEN indid = 255 THEN "Blob"
                ELSE "NC" + CONVERT(VARCHAR, indid - 1)
                END,
            [Plac] = CASE
                WHEN status2 & 512 = 512 THEN "Plac"
                ELSE ""
                END,
            [DS_Name]  = CASE indid
                WHEN   0 THEN ""                        -- Heaps do not have a name
                WHEN 255 THEN ""                        -- Blobs do not have a name
                ELSE SI.name
                END
        FROM  sysindexes SI                             -- Logical DataStructures
              -- use syspartitions for Physical DataStructures
        WHERE id > 1000                                 -- exclude sys tables
        ORDER BY OBJECT_NAME(id), SI.indid

EXIT_OK:
    RETURN 0

EXIT_ERR:
    RETURN 1

GO
