Recently I created a script using T-SQL, as requested by my colleagues who are working on MSSQL 2008. This script supports two search mode:
- Search by Component Name. Assigning a component name to @COMP_NAME, the script will list out all navigation paths for that component. For example
declare @COMP_NAME as nvarchar(18) = 'USERMAINT';
Output will be
Component: USERMAINT
Menu Path: PeopleTools > Security > User Profiles > User Profiles
- Search by Component Label. Assigning a component navigation label to@COMP_LABEL, the script will list our all components with the particular lable. For example
Output will be
Component: USERMAINT
Menu Path: PeopleTools > Security > User Profiles > User Profiles
Component: DSUSRPROF2
Component: DSUSRPROF2
Menu Path: Enterprise Components > Directory Interface > Mappings > User Profiles
If 'Y' is set for @IGNORE_CASE you can even search for label without worrying about case sensitivity.
The script is as follows:
/****************************************************************************** Filename : FIND_MENUPATHS.SQL Version : 1.0 Description: This script query PeopleSoft table PSPRSMDEFN to find out menu paths for a component Author : devwfb@gmail.com Date : 08 NOV 2012 ******************************************************************************/ -- -- Parameters -- --============================================================================= -- QUERY MODE 1 - By Component declare @COMP_NAME as nvarchar(18) = ''; --============================================================================= -- QUERY MODE 2 - By Label declare @COMP_LABEL as nvarchar(30) = ''; declare @IGNORE_CASE as nvarchar(1) = 'Y'; -- -- Constants & Variables -- declare @PORTAL_NAME as nvarchar(30) = 'EMPLOYEE'; declare @SEP as nvarchar(3) = '>'; declare @component_name as nvarchar(30); declare @prev_component as nvarchar(30) = ''; declare @portal_label as nvarchar(30); declare @menu_path as nvarchar(max); declare @parent_obj as nvarchar(30); -- -- Verification for parameters -- set @COMP_NAME = ltrim(rtrim(@COMP_NAME)); set @COMP_LABEL = ltrim(rtrim(@COMP_LABEL)); if @COMP_NAME = '' and @COMP_LABEL = '' begin print 'Alert: Please provide parameter Component Name or Component Label'; return end else if @COMP_NAME <> '' and @COMP_LABEL <> '' begin print 'Alert: Component Name and Component Label are mutually exclusive. Please provide only one parameter'; return end -- -- Search menu paths -- if @COMP_NAME <> '' -- Mode 1 declare cur_navi cursor for select PORTAL_PRNTOBJNAME, PORTAL_LABEL, PORTAL_URI_SEG2 from PSPRSMDEFN where PORTAL_NAME = @PORTAL_NAME and PORTAL_CREF_URLT = 'UPGE' and (PORTAL_URI_SEG2 = @COMP_NAME or PORTAL_URI_SEG2 like @COMP_NAME); else if @IGNORE_CASE = 'Y' declare cur_navi cursor for select PORTAL_PRNTOBJNAME, PORTAL_LABEL, PORTAL_URI_SEG2 from PSPRSMDEFN where PORTAL_NAME = @PORTAL_NAME and PORTAL_CREF_URLT = 'UPGE' and (upper(PORTAL_LABEL) = upper(@COMP_LABEL) or upper(PORTAL_LABEL) like upper(@COMP_LABEL)); else declare cur_navi cursor for select PORTAL_PRNTOBJNAME, PORTAL_LABEL, PORTAL_URI_SEG2 from PSPRSMDEFN where PORTAL_NAME = @PORTAL_NAME and PORTAL_CREF_URLT = 'UPGE' and (PORTAL_LABEL = @COMP_LABEL or PORTAL_LABEL like @COMP_LABEL); open cur_navi fetch next from cur_navi into @parent_obj, @portal_label, @component_name if @@fetch_status <> 0 begin close cur_navi deallocate cur_navi print 'No menu paths found matching given parameter.' return end while @@fetch_status = 0 begin set @menu_path = @portal_label; while 1=1 begin select @parent_obj = PORTAL_PRNTOBJNAME, @portal_label = PORTAL_LABEL from PSPRSMDEFN where PORTAL_NAME = @PORTAL_NAME and PORTAL_OBJNAME <> PORTAL_PRNTOBJNAME and PORTAL_OBJNAME = @parent_obj; if @parent_obj is null or @portal_label = 'Root' break; set @menu_path = @portal_label + ' ' + @SEP + ' ' + @menu_path end if @component_name <> @prev_component begin print '' print 'Component: ' + @component_name; set @prev_component = @component_name; end print 'Menu Path: ' + @menu_path; fetch next from cur_navi into @parent_obj, @portal_label, @component_name end close cur_navi deallocate cur_navi