/*>>>>>>>>>>>>>>>>>>>>>>>>>>> sp_dos <<<<<<<<<<<<<<<<<<<<<<<<<<<<<*/
IF OBJECT_ID('dbo.sp_dos') IS NOT NULL
    DROP PROCEDURE sp_dos
go

CREATE PROCEDURE sp_dos
    @vcObjectName varchar(30) = NULL
AS
/***********************************************************************
* sp_dos - Display Object Scope
*       This procedure graphically displays the scope of a object in
*       the database.
*
* Copyright 1996, all rights reserved.
*
* Author:  David W. Pledger, Strategic Data Systems, Inc.
*
* Parameters
*       ----------------------------------------------------------------
*       Name            In/Out  Description
*       ----------------------------------------------------------------
*       @vcObjectName    In     Mandatory - The exact name of a single
*                               database object for which the call
*                               hierarchy is to be extracted.
*
* Selected Data
*       A sample report follows:
*       ----------------------------------------------------------------
*
*               SCOPE OF EFFECT FOR OBJECT:  ti_users
*       +------------------------------------------------------------------+
*       (T) ti_users (Trigger on table 'users')
*       |
*       +--(P) pUT_GetError
*       |  |
*       |  +--(U) ui_error
*       |
*       +--(U) BGRP
*       |
*       +--(U) user_information (See Triggers: tu_user_information)
*       |
*       +--(U) users (See Triggers: ti_users, tu_users, td_users)
*       |
*       +--(P) pUT_LUDVersion
*          |
*          +--(P) pUT_GetError
*          |  |
*          |  +--(U) ui_error
*          |
*          +--(U) BGRP_LUDVersion
*
*       <End of Sample>
*
* Return Values
*       ----------------------------------------------------------------
*       Value           Description
*       ----------------------------------------------------------------
*       < -99           Unexpected error - should never occur.
*
*       -99 to -1       Sybase **reserved** return status values.
*
*       0               Execution succeeded
*
*       1               Execution of this procedure failed.
*
*       > 1             Unexpected error - should never occur.
*
***********************************************************************/
BEGIN

    /*------------------- Local Declarations -------------------------*/
    DECLARE @iObjectID    int           /* System ID of object        */
    DECLARE @cObjectType  char(1)       /* System Object Type code    */
    DECLARE @vcName       varchar(30)   /* System Object name         */
    DECLARE @vcMsg        varchar(255)  /* Error Message if needed    */
    DECLARE @iInsTrigID   int           /* Insert Trigger ID          */
    DECLARE @iUpdTrigID   int           /* Update Trigger ID          */
    DECLARE @iDelTrigID   int           /* Delete Trigger ID          */
    DECLARE @vcErrMsg     varchar(255)  /* Error Message              */

    /* Local variables to facilitate descending the parent-child
    ** object hierarchy.
    */
    DECLARE @iCurrent     int           /* Current node in the tree   */
    DECLARE @iRoot        int           /* The root node in the tree  */
    DECLARE @iLevel       int           /* The current level          */

    /* Local variables that contain the fragments of the text to
    ** be displayed while descending the hierarchy.
    */
    DECLARE @iDotIndex    int           /* Index for locating periods */
    DECLARE @cConnector   char(3)       /* '+--'                      */
    DECLARE @cSibSpacer   char(3)       /* '|  '                      */
    DECLARE @cBar         char(1)       /* '|'                        */
    DECLARE @cSpacer      char(3)       /* '   '                      */
    DECLARE @cPrntStrng1  char(255)     /* The first string to print  */
    DECLARE @cPrntStrng2  char(255)     /* The second string to print */
    DECLARE @iLoop        int           /* Temp var used for loop     */
    DECLARE @vcDepends    varchar(255)  /* Dependency String          */
    DECLARE @iDependsItem int           /* Index to a string item     */

    /* Create a temporary table to handle the hierarchical
    ** decomposition of the task parent-child relationship.  The Stack
    ** table keeps track of where we are while the leaf table keeps
    ** track of the leaf tasks which need to be performed.
    */
    CREATE TABLE #Stack
        (iItem int,
         iLevel int)

    /*------------------- Validate Input Parameters --------------------*/
    /* Make sure the table is local to the current database. */
    IF (@vcObjectName LIKE "%.%.%") AND (SUBSTRING(@vcObjectName, 1,
            CHARINDEX(".", @vcObjectName) - 1) != DB_NAME())
        GOTO ErrorNotLocal

    /* Now check to see that the object is in sysobjects. */
    IF OBJECT_ID(@vcObjectName) IS NULL
        GOTO ErrorNotFound

    /* ---------------------- Initialization -------------------------*/

    /* Do print any rowcounts while this is in progress. */
    SET NOCOUNT ON

    /* Retrieve the object ID out of sysobjects */
    SELECT @iObjectID = O.id,
           @cObjectType = O.type
    FROM   sysobjects O
    WHERE  O.name = @vcObjectName

    /* Make sure a job exists. */
    IF NOT (@@rowcount = 1 and @@error = 0 and @iObjectID > 0)
        GOTO ErrorNotFound

    /* Initialize the print string pieces. */
    SELECT @cConnector  = "+--",
           @cSibSpacer  = "|..",
           @cBar        = "|",
           @cSpacer     = "...",
           @cPrntStrng1 = "",
           @cPrntStrng2 = ""

    /* Print a separator line. */
    PRINT " "
    PRINT "** Utility by David Pledger, Strategic Data Systems, Inc.  **"
    PRINT "**         PO Box 498, Springboro, OH  45066               **"
    PRINT " "
    PRINT "         SCOPE OF EFFECT FOR OBJECT:  %1!",@vcObjectName
    PRINT "+------------------------------------------------------------------+"

    /* -------------------- Show the Hierarchy -----------------------*/
    /* Find the root task for this job.  The root task is the only task
    ** that has a parent task ID of null.
    */
    SELECT @iRoot = @iObjectID

    /* Since there is a root task, we can assign the first
    ** stack value and assign it a level of one.
    */
    SELECT @iCurrent = @iRoot,
           @iLevel = 1

    /* Prime the stack with the root level. */
    INSERT INTO #Stack values (@iCurrent, 1)

    /* As long as there are nodes which have not been visited
    ** within the tree, the level will be > 0.  Continue until all
    ** nodes are visited.  This outer loop descends the tree through
    ** the parent-child relationship of the nodes.
    */
    WHILE (@iLevel > 0)
    BEGIN

        /* Do any nodes exist at the current level?  If yes, process them.
        ** If no, then back out to the previous level.
        */
        IF EXISTS
            (SELECT *
             FROM   #Stack S
             WHERE  S.iLevel = @iLevel)
        BEGIN

            /* Get the smallest numbered node at the current level. */
            SELECT @iCurrent = min(S.iItem)
            FROM   #Stack S
            WHERE  S.iLevel = @iLevel

            /* Get the name and type of this node.  */
            SELECT @cObjectType = O.type,
                   @vcName = O.name,
                   @iInsTrigID = ISNULL(O.instrig, 0),
                   @iUpdTrigID = ISNULL(O.updtrig, 0),
                   @iDelTrigID = ISNULL(O.deltrig, 0)
            FROM   sysobjects O
            WHERE  O.id = @iCurrent

           /*
            *    *=================================================*    *
            *    * Print out data for this node.  (Consider        *    *
            *    * making this a separate procedure.)              *    *
            *    *=================================================*    *
            */

            /* Initialize the print strings to empty (different from NULL).
            ** @cPrntStrng1 is used to 'double space' the output and
            ** contains the necessary column connectors, but no data.
            ** @cPrntStrng2 contains the actual data at the end of the
            ** string.
            */
            SELECT @cPrntStrng1 = ""
            SELECT @cPrntStrng2 = ""

            /* Level 1 is the root node level.  All Jobs have a single
            ** root task.  All other tasks are subordinate to this task.
            ** No job may have more than one root task.
            */
            IF @iLevel = 1
            BEGIN
                /* Print data for the root node. */
                SELECT @cPrntStrng1 = "",
                       @cPrntStrng2 = "(" + @cObjectType + ") " + @vcName
            END
            ELSE /* Else part of (IF @iLevel = 1) */
            BEGIN

                /* Initialize loop variable to 2 since level one has
                ** already been processed for printing.
                */
                SELECT @iLoop = 2

                /* Look at the values on the stack at each level to
                ** determine which symbol should be inserted into the
                ** print string.
                */
                WHILE @iLoop <= @iLevel
                BEGIN

                   /* While the loop variable is less than the current
                   ** level, add the appropriate spacer to line up
                   ** the printed output.
                   */
                   IF @iLoop < @iLevel
                   BEGIN

                       /* Is there a sibling (another node which exists
                       ** at the same level) on the stack?  If so, use
                       ** one type of separator; otherwise, use another
                       ** type of separator.
                       */
                       IF EXISTS(SELECT * FROM #Stack WHERE iLevel = @iLoop)
                       BEGIN
                           SELECT @cPrntStrng1 = rtrim(@cPrntStrng1) +
                                   @cSibSpacer
                           SELECT @cPrntStrng2 = rtrim(@cPrntStrng2) +
                                   @cSibSpacer
                       END
                       ELSE
                       BEGIN
                           SELECT @cPrntStrng1 = rtrim(@cPrntStrng1) + @cSpacer
                           SELECT @cPrntStrng2 = rtrim(@cPrntStrng2) + @cSpacer
                       END
                   END
                   ELSE /* Else part of (IF @iLoop < @iLevel) */
                   BEGIN
                       SELECT @cPrntStrng1 = rtrim(@cPrntStrng1) + @cBar
                       SELECT @cPrntStrng2 = rtrim(@cPrntStrng2) +
                               @cConnector + "(" + @cObjectType + ") " +
                               @vcName
                   END

                   /* Increment the loop variable */
                   SELECT @iLoop = @iLoop + 1

                END /* While @iLoop <= @iLevel */
            END /* IF @iLevel = 1 */

            /* Spaces are inserted into the string to separate the levels
            ** into columns in the printed output.  Spaces, however, caused
            ** a number of problems when attempting to concatenate the
            ** two strings together.  To perform the concatenation, the
            ** function rtrim was used to remove the end of the string.
            ** This also removed the spaces we just added.  To aleviate
            ** this problem, we used a period (.) wherever there was
            ** supposed to be a space.  Now that we are ready to print
            ** the line of text, we need to substitute real spaces
            ** wherever there is a period in the string.  To do this,
            ** we simply look for periods and substitute spaces.  This
            ** has to be done in a loop since there is no mechanism to
            ** make this substitution in the whole string at once.
            */

            /* Find the first period. */
            SELECT @iDotIndex = charindex (".", @cPrntStrng1)

            /* If a period exists, substitute a space for it and then
            ** find the next period.
            */
            WHILE @iDotIndex > 0
            BEGIN
                /* Substitute the space */
                SELECT @cPrntStrng1 = stuff(@cPrntStrng1, @iDotIndex, 1, " ")

                /* Find the next. */
                SELECT @iDotIndex = charindex (".", @cPrntStrng1)
            END

            /* Do the same thing for the second print string. */
            SELECT @iDotIndex = charindex (".", @cPrntStrng2)
            WHILE @iDotIndex > 0
            BEGIN
                SELECT @cPrntStrng2 = stuff(@cPrntStrng2, @iDotIndex, 1, " ")
                SELECT @iDotIndex = charindex (".", @cPrntStrng2)
            END

            SELECT @vcDepends = NULL

            IF @iInsTrigID > 0
                SELECT @vcDepends = OBJECT_NAME(@iInsTrigID) + " (Insert)"

            IF @iUpdTrigID > 0
                IF @vcDepends IS NULL
                    SELECT @vcDepends = OBJECT_NAME(@iUpdTrigID) + " (Update)"
                ELSE
                    SELECT @vcDepends = @vcDepends + ", " +
                           OBJECT_NAME(@iUpdTrigID) + " (Update)"

            IF @iDelTrigID > 0
                IF @vcDepends IS NULL
                    SELECT @vcDepends = OBJECT_NAME(@iDelTrigID) + " (Delete)"
                ELSE
                    SELECT @vcDepends = @vcDepends + ", " +
                           OBJECT_NAME(@iDelTrigID) + " (Delete)"

             IF @vcDepends IS NOT NULL
                 IF @cObjectType = "T"
                     SELECT @cPrntStrng2 = @cPrntStrng2 +
                               " (Trigger on table '" + @vcDepends + "')"
                 ELSE
                     SELECT @cPrntStrng2 = @cPrntStrng2 +
                                       " (See Triggers: " + @vcDepends + ")"

            /* Remove trailing blanks from the first print string.  */
            SELECT @cPrntStrng1 = rtrim(@cPrntStrng1)
            SELECT @cPrntStrng2 = rtrim(@cPrntStrng2)

            /* Print the two strings. */
            PRINT @cPrntStrng1
            PRINT @cPrntStrng2

            /* Remove the current entry from the stack (Pop) */
            DELETE #Stack
            WHERE  #Stack.iLevel = @iLevel
            AND    #Stack.iItem = @iCurrent

            /* Add (push) to the stack all the children of the current
            ** node.
            */
            INSERT INTO #Stack
            SELECT D.depid,
                   @iLevel + 1
            FROM   sysdepends D
            WHERE  D.id = @iCurrent

            /* If any were added, then we must descend another level. */
            IF @@rowcount > 0
            BEGIN
                SELECT @iLevel = @iLevel + 1
            END

        END
        ELSE
        BEGIN
            /* We have reached a leaf node.  Move back to the previous
            ** level and see what else is left to process.
            */
            SELECT @iLevel = @iLevel - 1
        END

    END /* While (@iLevel > 0) */

    PRINT " "

    RETURN (0)

/*------------------------ Error Handling --------------------------*/
ErrorNotLocal:
    /* 17460, Table must be in the current database. */
    EXEC sp_getmessage 17460, @vcErrMsg OUT
    PRINT @vcErrMsg
    RETURN (1)

ErrorNotFound:
    /* 17461, Table is not in this database. */
    EXEC sp_getmessage 17461, @vcErrMsg OUT
    PRINT @vcErrMsg
    PRINT " "

    PRINT "Local object types and objecs are:"

    SELECT "Object Type" = type,
           "Object Name" = name
    FROM   sysobjects
    WHERE  type IN ("U","TR","P","V")
    ORDER BY type, name

    RETURN (1)

END
go

grant execute on sp_dos to public
go

