-- OfferHistory Insert Forensics (Aggregate, to be run vs. your InteractionAudit_xxx database)

SELECT 
min([StartDate]) as [StartDate], 
max([StartDate]) as [StartDate], 
count(*) as [count],
sum(CONVERT(INT,[Result])) as 'RowsSUM',
sum([Duration])/60/60 as 'DurationsSUM_Hours', 
avg([Duration])/60 as 'DurationAVG_Minutes'
FROM [dbo].[SQLTraceLog]
where [QueryString] like ('INSERT INTO _dbo_._OfferHistory_ (%')
-- where [QueryString] like ('INSERT INTO _dbo_._OfferHistory[_]Sandbox_ (%')
and datediff(month,StartDate,getdate()) <= 1

-- OfferHistory Insert Forensics (Granular > 5 minutes, to be run vs. your InteractionAudit_xxx database)

SELECT * FROM [dbo].[SQLTraceLog]
where [QueryString] like ('INSERT INTO _dbo_._OfferHistory_ (%')
-- where [QueryString] like ('INSERT INTO _dbo_._OfferHistory[_]Sandbox_ (%')
and Duration > 300 and datediff(month,StartDate,getdate()) <= 1
order by Duration desc

-- Rows within OfferHistory Forensics (to be run vs. your client data warehouse)

SELECT SUM(p.rows) AS 'RowCount'
FROM sys.partitions AS p
LEFT JOIN sys.allocation_units AS a
ON p.partition_id = a.container_id
WHERE
p.[object_id] = OBJECT_ID('dbo.OfferHistory');

-- Rows within OfferHistory Forensics (to be run vs. your client data warehouse)

select schema_name(t.schema_id) + '.' + t.[name] as table_view,
    case when t.[type] = 'U' then 'Table'
        when t.[type] = 'V' then 'View'
    end as [object_type],
    i.index_id,
    case when i.is_primary_key = 1 then 'Primary key'
        when i.is_unique = 1 then 'Unique'
        else 'Not unique' end as [type],
    i.[name] as index_name,
    substring(column_names, 1, len(column_names)-1) as [columns],
    case when i.[type] = 1 then 'Clustered index'
        when i.[type] = 2 then 'Nonclustered unique index'
        when i.[type] = 3 then 'XML index'
        when i.[type] = 4 then 'Spatial index'
        when i.[type] = 5 then 'Clustered columnstore index'
        when i.[type] = 6 then 'Nonclustered columnstore index'
        when i.[type] = 7 then 'Nonclustered hash index'
    end as index_type
from sys.objects as t
    inner join sys.indexes as i
        on t.object_id = i.object_id
    cross apply (select col.[name] + ', '
                from sys.index_columns as ic
                    inner join sys.columns as col
                    on ic.object_id = col.object_id
                    and ic.column_id = col.column_id
                where ic.object_id = t.object_id
                    and ic.index_id = i.index_id
                order by col.column_id
                for xml path ('') ) as D (column_names)
where t.is_ms_shipped <> 1
and index_id > 0 and schema_name(t.schema_id) + '.' + t.[name] = 'dbo.OfferHistory'
-- and index_id > 0 and schema_name(t.schema_id) + '.' + t.[name] = 'dbo.OfferHistory_States'
order by schema_name(t.schema_id) + '.' + t.[name], i.index_id