USP_QUERYVIEW_GETROOTTREE

CREATE procedure dbo.[USP_QUERYVIEW_GETROOTTREE]
(
  @OBJECTNAME nvarchar(128),
  @APPUSERID uniqueidentifier,
  @USEINREPORTMODELGENERATOR bit = null
)
as
  set nocount on;

  --Returns related nodes to the root query view

  select
    Q.RELATEDQUERYVIEWID, 
    RELATEDVIEW.OBJECTNAME as "RELATEDQUERYVIEW", 
    Q.ROOTCOLUMN, 
    Q.RELATEDCOLUMN, 
    Q.PATHALIAS, 
    Q.SEQUENCE, 
    RELATEDVIEW.SUBGROUP,
    (select count(*) from dbo.QUERYVIEWRELATIONSHIP where ROOTQUERYVIEWID = Q.RELATEDQUERYVIEWID) as [CHILDNODECOUNT],
    RECORDTYPE.ID RECORDTYPEID,
    RELATEDVIEW.USEINREPORTMODELGENERATOR,
    Q.CARDINALITYTYPE,
    RECORDTYPE.NAME RECORDTYPE,
    Q.DISPLAYALIAS,
    Q.DISPLAYALIASRESOURCEKEY,
    Q.RESOURCEFILEASSEMBLYNAME,
    Q.RESOURCEFILECLASSNAME,
    Q.OBSOLETE,
    Q.USEFOREXPORTDEFINITIONSONLY,
    Q.SUBGROUPRESOURCEKEY
  from dbo.QUERYVIEWRELATIONSHIP as Q
  inner join dbo.QUERYVIEWCATALOG as ROOTVIEW on Q.ROOTQUERYVIEWID = ROOTVIEW.ID
  inner join dbo.QUERYVIEWCATALOG as RELATEDVIEW on Q.RELATEDQUERYVIEWID = RELATEDVIEW.ID
  left join dbo.RECORDTYPE on RELATEDVIEW.RECORDTYPEID = RECORDTYPE.ID
  where ROOTVIEW.OBJECTNAME = @OBJECTNAME
  and (@USEINREPORTMODELGENERATOR is null or RELATEDVIEW.USEINREPORTMODELGENERATOR = @USEINREPORTMODELGENERATOR)
  order by Q.SEQUENCE;

  return 0;