if OBJECT_ID('fpscdb001_system.v_DB_Index_Fragmentation_Table','V') is not null
begin
drop view fpscdb001_system.v_DB_Index_Fragmentation_Table
end
go

create view fpscdb001_system.v_DB_Index_Fragmentation_Table
as
SELECT
dbstats.database_name,
dbschemas.name as SchemaName,
 dbtables.name as TableName,
 dbindexes.name as IndexName,
 case when dbtables.name in 
  ('audit_log',
 'app_user',
 'user_prof',
'audit_prop_chg',
'audit_txn',
'cal_field',
'cal_item',
'cfg_file_repository',
'cfg_setting',
'cfg_setting_val',
'defn',
'defn_reln',
'defn_ver',
'defn_ver_association',
'schd_actual_period',
'secuty_policy',
'string_rsrc_val',
'target_match'
 ) then 'Critical' 
 when dbschemas.name like '%_system' then 'Non-critical' else '' end as System_Table_Classification,
cast(indexstats.avg_fragmentation_in_percent as decimal(6,2))  as FragmentationPct,
Last_Updated
,install_date
,is_auto_create_stats_on
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.object_id = indexstats.object_id
INNER JOIN sys.schemas dbschemas on dbtables.schema_id = dbschemas.schema_id
INNER JOIN sys.indexes AS dbindexes ON dbindexes.object_id = indexstats.object_id
AND indexstats.index_id = dbindexes.index_id and alloc_unit_type_desc = 'IN_ROW_DATA'
--
JOIN   (select s.object_id,name as index_name, min(STATS_DATE(s.object_id, S.stats_id)) as last_updated 
		from  sys.stats S 
		where auto_created = 0 -- only index stats
		group by s.object_id,name ) ss
        ON  Ss.object_id = dbtables.object_id and ss.index_name = dbindexes.name
cross join (select name as database_name,create_date as install_date, is_auto_create_stats_on from sys.databases d where d.database_id = db_id()) DBStats
WHERE indexstats.database_id = DB_ID()
and indexstats.page_count > 8
and dbtables.name  not like '%_FH_%'
and dbtables.name  not like '%_ST_%'
and dbtables.name != 'dyn_table_script'
go



if OBJECT_ID('fpscdb001_system.v_DB_Index_Fragmentation','V') is not null
begin
drop view fpscdb001_system.v_DB_Index_Fragmentation
end
go

create view fpscdb001_system.v_DB_Index_Fragmentation
as
select 
q.database_name,
q.SchemaName
,q.System_Table_Classification
, max(FragmentationPct) as Max_Fragmentation
, cast(avg(FragmentationPct) as decimal(6,2)) as Avg_Fragmentation
, min(q.last_updated) as last_updated
--
,ltrim(ISNULL(STR(DATEDIFF(day,min(q.last_updated), GetDate())), 'Never')) as Days_Since_Statistics_Update
,DATEDIFF(day,min(q.last_updated), GetDate()) as Last_Stats_Updated_Day_Ct 
--
,q.Install_Date
,DATEDIFF(day,q.Install_Date, GetDate()) as Install_Day_ct
from
v_DB_Index_Fragmentation_Table q
group by database_name,System_Table_Classification, q.SchemaName ,q.install_date
go

if OBJECT_ID('fpscdb001_system.v_DB_Index_Fragmentation_Health','V') is not null
begin
drop view fpscdb001_system.v_DB_Index_Fragmentation_Health
end
go

create view fpscdb001_system.v_DB_Index_Fragmentation_Health
as
select 
case 
when v.Max_Fragmentation > 90 and v.Avg_Fragmentation > 50 then 'Extremely High'
when v.Max_Fragmentation > 66 and v.Avg_Fragmentation > 40 then 'Very High'
when v.Max_Fragmentation > 50 and v.Avg_Fragmentation > 25 then 'High'
when v.Max_Fragmentation > 25 and v.Avg_Fragmentation > 15 then 'Medium'
when v.Max_Fragmentation > 15 and v.Avg_Fragmentation > 10 then 'Low'
else 'Minimal' end as Fragmentation_Level
,
case 
when v.Last_Stats_Updated_Day_Ct >= 7 then 'Reindex.'
when v.Last_Stats_Updated_Day_Ct > v.Install_Day_ct then 'Reindex.'
else 'None' end as Recommendation,
case when a.externalName is not null then a.externalName else '' end as ContainerName,
* 
from fpscdb001_system.v_DB_Index_Fragmentation v with (nolock)
left join fpscdb001_system.v_cntnr_defn_active a with (nolock) on a.containerSchemaName = v.SchemaName
go

-- now query the views

select * from fpscdb001_system.v_DB_Index_Fragmentation_Health h
order by h.system_table_classification desc, SchemaName
go


select top 100 percent t.*
from
(
select 
 cast(avg(FragmentationPct) over (partition by schemaName) as decimal(6,2)) as AvgSchemaFragmention
,cast(avg(FragmentationPct) over (partition by schemaName, TableName) as decimal(6,2)) as AvgTableFragmention
,*
from fpscdb001_system.v_DB_Index_Fragmentation_Table
--where system_table_classification = 'Critical'
) t
order by t.AvgSchemaFragmention desc, t.AvgTableFragmention desc, indexName
go


--dbcc dbreindex('fpscdb001_system.dyn_table_script')
select * from fpscdb001_system.v_DB_Index_Fragmentation v