You can create a table function like this:
============================================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION {databaseOwner}{objectQualifier}.[SubPages]
(@tabid INT
,@PortalID int)
RETURNS TABLE
AS
RETURN WITH subtabs (distance, tabid, parentid, TabName)
AS (SELECT 0
,h.tabid
,h.parentid
,h.TabName
FROM tabs h
WHERE h.tabid = @tabid
AND h.PortalID = @PortalID
UNION ALL
SELECT i.distance + 1
,h.tabid
,h.parentid
,h.TabName
FROM tabs h
INNER JOIN subtabs i ON i.tabid = h.parentid)
SELECT distance, tabid, parentid, TabName FROM subtabs
====================================================
usage like this:
SELECT distance, tabid, parentid, TabName
FROM dbo.SubPages(62,0)
Where the first integer is a tabid and the second a portalid
The returned values are self-explanatory but for the first, possibly, "distance"
The page with tabid 62 is returned with a distance of 0
all child pages have a distance of 1
all child pages of child pages have a distance of 2
etc
It doesn't get you all you are after but perhaps it would with some modification.
Join to a few other tables to get some more results, such as:
SELECT a.distance
,a.tabid
,a.parentid
,a.TabName
,c.DisplayName Created_By
,d.DisplayName Last_Modified_By
FROM dbo.SubPages(62,0) a
JOIN tabs b ON b.TabID = a.tabid
JOIN users c ON c.UserID = b.CreatedByUserID
JOIN users d ON d.UserID = b.LastModifiedByUserID