USP_DATABASESPACEUSED_TOPNIDSET

CREATE procedure dbo.[USP_DATABASESPACEUSED_TOPNIDSET]
(
  @MAXROWS integer = 20,
  @ORDERBY tinyint = 0,
  @OWNERID uniqueidentifier = null
)
as begin
  set nocount on;

  select top (@MAXROWS)
    [DATABASESPACEUSED].[NAME],
    [IDSETREGISTER].[NAME] as [IDSETREGISTERNAME],
    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 [OWNER],
    case when [ADHOCQUERY].[ID] is not null then isnull([SITE].[NAME], '###ALLSITES###') else '' end as [SITE],
    [IDSETREGISTER].[DATECHANGED],
    [DATABASESPACEUSED].[ROWS],
    [DATABASESPACEUSED].[RESERVEDKB],
    [DATABASESPACEUSED].[DATAKB],
    [DATABASESPACEUSED].[INDEXKB],
    [DATABASESPACEUSED].[UNUSEDKB],
    [DATABASESPACEUSED].[IDSETREGISTERID],
    [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID],
    [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID]
  from dbo.[DATABASESPACEUSED]
  left outer 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.[SITE] on [SITE].[ID] = [ADHOCQUERY].[SITEID]
  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 (@OWNERID is null or coalesce([ADHOCQUERY].[OWNERID], [SMARTQUERYINSTANCE].[OWNERID], [IDSETREGISTER].[OWNERID]) = @OWNERID)
  order by case @ORDERBY when 0 then [RESERVEDKB]
                         when 1 then [DATAKB]
                         when 2 then [INDEXKB]
                         when 3 then [UNUSEDKB]
                         when 4 then datediff(day, [IDSETREGISTER].[DATECHANGED], getdate()) end desc;

  return 0;
end