Q9.11: sp_dos
<![CDATA[
/*>>>>>>>>>>>>>>>>>>>>>>>>>>> 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
]]>