package paths

WITH package(PCMID,
        CMID,
        ObjectId,
        ObjectName,
        ObjectPath,
        done,
        RootNode,
        ObjectType) AS (
  SELECT o.PCMID
  , n.CMID
  , n.CMID
  , n.NAME
  , CAST(n.NAME AS VARCHAR(MAX))
  , 0
  , CAST(n.NAME AS VARCHAR(MAX))
  , c.NAME
  FROM CMOBJECTS o
    INNER JOIN CMOBJNAMES n ON n.CMID = o.CMID
    INNER JOIN CMCLASSES c ON c.CLASSID = o.CLASSID
  WHERE n.ISDEFAULT = 1
    AND c.name IN ('package', 'module', 'uploadedFile', 'dataSet2')

  UNION ALL
  SELECT o.PCMID
  , n.CMID
  , p.ObjectId
  , p.ObjectName
  , CAST(n.NAME + '/' + p.ObjectPath AS VARCHAR(MAX))
  , CASE WHEN LEFT(n.NAME, 8) = '<directory namespace goes here>:' THEN 1 ELSE 0 END
  , CAST(n.NAME AS VARCHAR(MAX))
  , p.ObjectType
  FROM CMOBJECTS o
    INNER JOIN CMOBJNAMES n ON n.CMID = o.CMID
    INNER JOIN package p ON p.PCMID = n.CMID
  WHERE n.ISDEFAULT = 1
    AND n.CMID != 0
    AND p.done = 0
)


SELECT p.ObjectId
     , p.ObjectName
     , p.ObjectType
     , REPLACE(p.ObjectPath, p.RootNode, isnull(u.Name, p.RootNode)) AS ObjectPath

FROM package p
  LEFT OUTER JOIN (
  SELECT a.OBJID
  , b.NAME

  FROM CMOBJPROPS1 a
    INNER JOIN CMOBJPROPS33 b ON b.CMID = a.CMID
  ) u ON u.OBJID = p.RootNode
WHERE (p.ObjectPath LIKE 'Team Content%'
  OR   p.ObjectPath LIKE '<directory namespace goes here>:%')
ORDER BY ObjectPath