USP_SIMPLEDATALIST_IDSETOWNERSFROMDATABASESPACEUSED

create procedure dbo.[USP_SIMPLEDATALIST_IDSETOWNERSFROMDATABASESPACEUSED]
as begin
  set nocount on;

  select distinct
    coalesce([ADHOCQUERY].[OWNERID], [SMARTQUERYINSTANCE].[OWNERID], [IDSETREGISTER].[OWNERID]) as [VALUE],
    coalesce(case when [ADHOCQUERYOWNER].[DISPLAYNAME] = '' then [ADHOCQUERYOWNER].[USERNAME] else [ADHOCQUERYOWNER].[DISPLAYNAME] end,
             case when [SMARTQUERYOWNER].[DISPLAYNAME] = '' then [SMARTQUERYOWNER].[USERNAME] else [SMARTQUERYOWNER].[DISPLAYNAME] end,
             case when [IDSETREGISTEROWNER].[DISPLAYNAME] = '' then [IDSETREGISTEROWNER].[USERNAME] else [IDSETREGISTEROWNER].[DISPLAYNAME] end) as [LABEL]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[ID] = [DATABASESPACEUSED].[IDSETREGISTERID]
  left outer join dbo.[APPUSER] as [IDSETREGISTEROWNER] on [IDSETREGISTEROWNER].[ID] = [IDSETREGISTER].[OWNERID]
  left outer join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID] = [DATABASESPACEUSED].[IDSETREGISTERID]
  left outer join dbo.[ADHOCQUERY] on [ADHOCQUERY].[ID] = [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID]
  left outer join dbo.[APPUSER] as [ADHOCQUERYOWNER] on [ADHOCQUERYOWNER].[ID] = [ADHOCQUERY].[OWNERID]
  left outer join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID] = [DATABASESPACEUSED].[IDSETREGISTERID]
  left outer join dbo.[SMARTQUERYINSTANCE] on [SMARTQUERYINSTANCE].[ID] = [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  left outer join dbo.[APPUSER] as [SMARTQUERYOWNER] on [SMARTQUERYOWNER].[ID] = [SMARTQUERYINSTANCE].[OWNERID]
  where [DATABASESPACEUSED].[TYPECODE] = 2
  and coalesce([ADHOCQUERY].[OWNERID], [SMARTQUERYINSTANCE].[OWNERID], [IDSETREGISTER].[OWNERID]) is not null
  order by coalesce(case when [ADHOCQUERYOWNER].[DISPLAYNAME] = '' then [ADHOCQUERYOWNER].[USERNAME] else [ADHOCQUERYOWNER].[DISPLAYNAME] end,
                    case when [SMARTQUERYOWNER].[DISPLAYNAME] = '' then [SMARTQUERYOWNER].[USERNAME] else [SMARTQUERYOWNER].[DISPLAYNAME] end,
                    case when [IDSETREGISTEROWNER].[DISPLAYNAME] = '' then [IDSETREGISTEROWNER].[USERNAME] else [IDSETREGISTEROWNER].[DISPLAYNAME] end);

  return 0;
end