Find PeopleSoft Navigation

Create the following procedure and execute by passing the portal_objname to it.
This script is for MSS.

I know it is not very efficient but definately works.

USE [DBName]
GO
/****** Object: StoredProcedure [dbo].[ShowNavigation] Script Date: 01/28/2008 01:01:37 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[ShowNavigation]( @POBJNAME VARCHAR(30))AS
BEGIN
SET NOCOUNT ON
DECLARE @CNTR INT ,
@PLABEL1 VARCHAR (500),
@PRNTNAME VARCHAR (30),
@PLABEL varchar(90),
@PLABEL2 VARCHAR (50)

SELECT @CNTR = 1
SELECT @PLABEL1 = PORTAL_LABEL FROM dbo.PSPRSMDEFN WHERE
PORTAL_OBJNAME = @POBJNAME;
SELECT @PRNTNAME = PORTAL_PRNTOBJNAME FROM dbo.PSPRSMDEFN WHERE
PORTAL_OBJNAME = @POBJNAME;
SELECT @PLABEL2 = PORTAL_LABEL FROM dbo.PSPRSMDEFN WHERE
PORTAL_OBJNAME = @PRNTNAME;
PRINT (@PLABEL1 + '- '+@PLABEL2)
BEGIN
WHILE @PRNTNAME <> 'PORTAL_ROOT_OBJECT'
BEGIN EXEC ShowNavigation @PRNTNAME
IF @PRNTNAME = 'PORTAL_ROOT_OBJECT'
BREAK;
SELECT @PRNTNAME = PORTAL_PRNTOBJNAME FROM
dbo.PSPRSMDEFN WHERE PORTAL_OBJNAME = @PRNTNAME;
SELECT @PLABEL = PORTAL_LABEL FROM PSPRSMDEFN WHERE
PORTAL_OBJNAME = @PRNTNAME;

END
END
END