USP_DATABASESPACEUSED_ANALYZE

CREATE procedure dbo.[USP_DATABASESPACEUSED_ANALYZE]
(
  @BUSINESSPROCESSSTATUSID uniqueidentifier
)
as begin
  set nocount on;

  if object_id('tempdb..#SPACEUSED') is null
    raiserror('BBERR_TEMPTABLEDOESNOTEXIST: The temporary table #SPACEUSED must be created and populated before this procedure can be called.', 13, 1);

  truncate table dbo.[DATABASESPACEUSED];

  insert into dbo.[DATABASESPACEUSED]
  (
    [ID],
    [SCHEMANAME],
    [NAME],
    [ROWS],
    [RESERVEDKB],
    [DATAKB],
    [INDEXKB],
    [UNUSEDKB]
  ) select
    newid(),
    [SCHEMANAME],
    [NAME],
    [ROWS],
    replace([RESERVED], ' KB', ''),
    replace([DATA], ' KB', ''),
    replace([INDEX], ' KB', ''),
    replace([UNUSED], ' KB', '')
  from #SPACEUSED;

  drop table #SPACEUSED;

  /***********************************************************
   * 1. populate useful DATABASESPACEUSED foreign keys       *
   ***********************************************************/

  update dbo.[DATABASESPACEUSED] set
    [TABLECATALOGID] = [TABLECATALOG].[ID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[TABLECATALOG] on [TABLECATALOG].[TABLENAME] = [DATABASESPACEUSED].[NAME]
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo';

  update dbo.[DATABASESPACEUSED] set
    [TABLECATALOGID] = [TABLECATALOG].[ID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[TABLECATALOG] on [TABLECATALOG].[TABLENAME] + 'AUDIT' = [DATABASESPACEUSED].[NAME]
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo';

  update dbo.[DATABASESPACEUSED] set
    [BUSINESSPROCESSOUTPUTID] = [BUSINESSPROCESSOUTPUT].[ID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[BUSINESSPROCESSOUTPUT] on [BUSINESSPROCESSOUTPUT].[TABLENAME] = [DATABASESPACEUSED].[NAME]
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo'
    and [DATABASESPACEUSED].[NAME] not like 'MKTSEGMENTATIONEXCLUSIONS\_%' escape '\' 
    and [DATABASESPACEUSED].[NAME] not like 'MKTMAILINGPREACTIVATION\_%' escape '\';

  update dbo.[DATABASESPACEUSED] set
    [IDSETREGISTERID] = [IDSETREGISTERADHOCQUERY].[IDSETREGISTERID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[IDSETREGISTERADHOCQUERY] on [IDSETREGISTERADHOCQUERY].[ADHOCQUERYID] = 
    convert(uniqueidentifier, replace(substring([DATABASESPACEUSED].[NAME], 24, 36), '_', '-'))
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo'
    and [DATABASESPACEUSED].[NAME] like 'ADHOCQUERY\_STATICIDSET\_%' escape '\';

  update dbo.[DATABASESPACEUSED] set
    [IDSETREGISTERID] = [IDSETREGISTERSMARTQUERYINSTANCE].[IDSETREGISTERID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[IDSETREGISTERSMARTQUERYINSTANCE] on [IDSETREGISTERSMARTQUERYINSTANCE].[SMARTQUERYINSTANCEID] = 
    convert(uniqueidentifier, replace(substring([DATABASESPACEUSED].[NAME], 32, 36), '_', '-'))
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo'
    and [DATABASESPACEUSED].[NAME] like 'SMARTQUERYINSTANCE\_STATICIDSET\_%' escape '\';

  update dbo.[DATABASESPACEUSED] set
    [IDSETREGISTERID] = [IMPORTSELECTIONPROCESS].[IDSETREGISTERID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[IMPORTSELECTIONPROCESS] on [IMPORTSELECTIONPROCESS].[ID] = 
    convert(uniqueidentifier, replace(substring([DATABASESPACEUSED].[NAME], 29, 36), '_', '-'))
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo'
    and [DATABASESPACEUSED].[NAME] like 'IMPORTSELECTION\_STATICIDSET\_%' escape '\';

  update dbo.[DATABASESPACEUSED] set
    [IDSETREGISTERID] = [IDSETREGISTER].[ID]
  from dbo.[DATABASESPACEUSED]
  inner join dbo.[IDSETREGISTER] on [IDSETREGISTER].[DBOBJECTNAME] = [DATABASESPACEUSED].[NAME]
  where [DATABASESPACEUSED].[SCHEMANAME] = 'dbo'
    and [DATABASESPACEUSED].[IDSETREGISTERID] is null;

  /***********************************************************
   * 2. assign types to DATABASESPACEUSED rows               *
   ***********************************************************/

  update dbo.[DATABASESPACEUSED] set
    [TYPECODE] = case when [NAME] like '%CATALOG' or [NAME] like 'REVISIONLOG%' or [NAME] like 'SYSTEMROLEPERM%' or 
                           [NAME] = 'QUERYVIEWRELATIONSHIP' or [NAME] like 'RECORDTYPE%' then 4 -- system

                      when [NAME] like '%AUDIT' then 6 -- audit

                      when [IDSETREGISTERID] is not null or [NAME] like 'IMPORTSELECTION\_STATICIDSET%' escape '\' then 2 -- ID set

                      when [BUSINESSPROCESSOUTPUTID] is not null then 3 -- output

                      else 1 end -- data

  where [SCHEMANAME] = 'dbo';

  -- identify customization tables as any tables whose author in the table spec catalog is not 'Blackbaud Product Development'


  with xmlnamespaces ('bb_appfx_table' as ns)
  update dbo.[DATABASESPACEUSED] set
    [TYPECODE] = 5 -- custom

  from dbo.[DATABASESPACEUSED]
  inner join dbo.[TABLECATALOG] on [TABLECATALOG].[ID] = [DATABASESPACEUSED].[TABLECATALOGID]
  where isnull([TABLECATALOG].[TABLESPECXML].value('/ns:TableSpec[1]/@Author', 'nvarchar(255)'), '') <> 'Blackbaud Product Development';

  -- when looking for customization tables, we also have to look for tables created for a customization during the spec loading process: for example, USR_SOMEBUSINESSPROCESS 

  -- generates USR_SOMEBUSINESSPROCESSSTATUS and USR_SOMEBUSINESSPROCESSEXPORTFORMAT; they get 'Blackbaud Product Development' as their author, but they're really part of the

  -- customization


  with [CUSTOMTABLES] as (
    select [NAME] from dbo.[DATABASESPACEUSED] where [TYPECODE] = 5
  )
  update dbo.[DATABASESPACEUSED] set
    [TYPECODE] = 5 -- custom

  from [CUSTOMTABLES]
  inner join dbo.[DATABASESPACEUSED] on [DATABASESPACEUSED].[NAME] like replace([CUSTOMTABLES].[NAME], '_', '\_') + '%' escape '\';

  -- "other" for now is any table whose schema is not dbo - this catches custom data warehouses


  update dbo.[DATABASESPACEUSED] set
    [TYPECODE] = 0 -- other

  where [SCHEMANAME] <> 'dbo';

  /***********************************************************
   * 3. assign subtypes to DATABASESPACEUSED rows            *
   ***********************************************************/

  update dbo.[DATABASESPACEUSED] set
    [SUBTYPECODE] = case when ([NAME] like 'ATTRIBUTE%' and len([NAME]) = 41) 
                           or ([NAME] like 'BATCHATTRIBUTE%' and len([NAME]) = 46)
                         then 2 -- attribute


                         when [NAME] like 'MKTSEGMENTATIONDATA\_%' escape '\' 
                           or [NAME] like 'MKTSEGMENTATIONEXCLUSIONS\_%' escape '\' 
                           or [NAME] like 'MKTMAILINGPREACTIVATION\_%' escape '\' 
                           or [NAME] like 'MKTSEGMENTATIONSEGMENTCACHEADDRESSES\_%' escape '\'
                           or [NAME] like 'MKTSEGMENTATIONSEGMENTCACHEEMAILADDRESSES\_%' escape '\'
                           or [NAME] like 'MKTSEGMENTLISTDATADONORS\_%' escape '\'
                           or [NAME] like 'MKTSOURCEANALYSISRULEDATA\_%' escape '\'
                         then 3 -- marketing effort data


                         when ([NAME] like 'SMARTFIELD%' and len([NAME]) = 42)
                         then 4 -- smart field


                         else 1 end -- application

  where [TYPECODE] = 1; -- data


  update dbo.[DATABASESPACEUSED] set
    [SUBTYPECODE] = case when [NAME] like 'IMPORTSELECTION\_STATICIDSET%' escape '\' then 6 -- imported

                         when [NAME] like 'IDSETGENERIC%' then 5
                         else 7 end -- query

  where [TYPECODE] = 2; -- ID set


  update dbo.[DATABASESPACEUSED] set
    [SUBTYPECODE] = case when [NAME] like 'BATCH%' then 9 -- batch

                         when [NAME] like 'MKT%' then 11 -- marketing

                         when [NAME] like 'EXPORT%' or [NAME] like '%EXPORT%' then 10 -- export

                         else 8 end -- other

  where [TYPECODE] = 3; -- output


  /***********************************************************
   * 4. save summary information to DATABASESPACEUSEDHISTORY *
   ***********************************************************/

  declare @ASOF datetimeoffset = sysdatetimeoffset();

  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 0, 0;  -- other data


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 1, 1;  -- application data, transactional data


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 1, 2;  -- application data, attribute data


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 1, 3;  -- application data, marketing effort data


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 1, 4;  -- application data, smart field calculations


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 2, 5;  -- selection data, generic selections


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 2, 6;  -- selection data, imported selections


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 2, 7;  -- selection data, selections from query


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 3, 8;  -- business process history, other output


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 3, 9;  -- business process history, batch process output


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 3, 10; -- business process history, export output


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 3, 11; -- business process history, marketing effort export output


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 4, 0;  -- system tables


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 5, 0;  -- customization tables


  exec dbo.[USP_DATABASESPACEUSEDHISTORY_SAVE] @BUSINESSPROCESSSTATUSID, @ASOF, 6, 0;  -- audit tables


  return 0;
end