Retrieving the list of URL links from the portal. - TSR

Here is a script to retrieve the list of URL links from the portal contained in Portal Text Editor islands. Run this script on the Portal Framework database (PortalFx or Passageways)

/****** Script for SelectTopNRows command from SSMS ******/
Declare @PageID as uniqueidentifier
Declare @IslandInstanceID as uniqueidentifier
Declare @IslandName as varchar(250)
Declare @PrefString as varchar(max)
Declare @Loc as integer
Declare @URL as varchar(500)

CREATE TABLE #TEMP
(
PageID uniqueidentifier,
IslandInstanceID uniqueidentifier,
IslandName varchar(250),
URL varchar(500)
)
Declare PrefStringCursor Cursor for
SELECT [PageID]
,[PrefString]
,[IslandInstanceID]
,[DisplayText]
FROM [PWPageIslands] where prefstring like '%href%'

Open PrefStringCursor
Set @Loc = 0
Fetch Next from PrefStringCursor INTO @PageID, @Prefstring, @IslandInstanceID, @IslandName
WHILE @@FETCH_STATUS = 0
BEGIN
Set @Loc = CHARINDEX('HREF=',@Prefstring,@Loc+1)
While @Loc > 1
BEGIN
Set @URL = SUBSTRING(@Prefstring,@Loc+6,CHARINDEX('"',@Prefstring, @Loc+7)-@Loc-6)
INSERT INTO #temp VALUES (@PageID, @IslandInstanceID, @IslandName, @URL)
Set @Loc = CHARINDEX ('HREF=',@PrefString, @Loc+7)
END
Fetch Next from PrefStringCursor INTO @PageID, @Prefstring, @IslandInstanceID, @IslandName
Set @Loc = 0
END
Close PrefStringCursor
Deallocate PrefStringCursor

Select PWPages.DisplayText as PageName, IslandName, IslandInstanceID, URL from #TEMP INNER JOIN PWPages on PWPages.PageID = #Temp.PageID
Drop Table #TEMP