USE [Admin]
GO

/*
Name: USP_Fragmentation_Report
Description: This SP uses linked servers to connect to all the servers whose names are present in table
 SmartReIndex_servers and pulls the latest reindex details and populates that data into table fragementation_Report
PreRequisite: 1. Smart ReIndex has to be configured in a server in order for this SP to work 2.Server name
should be added to the table SmartReIndex_servers and linked server should be created for that server
*/


CREATE procedure [dbo].[USP_Fragmentation_Report]
AS
Begin
declare @DBName varchar(30), @LatestRunDate datetime, @srvr_name sysname, @str nvarchar(1000), @ParmDefinition nvarchar(1000), @appname varchar(20)

declare srvr_list cursor for select server_name,app_name from SmartReIndex_servers order by App_Name,server_name

open srvr_list
fetch next from srvr_list into @srvr_name,@appname

while(@@FETCH_STATUS=0)
begin

set @str='declare dblist cursor for select distinct(Database_Name) from ['+@srvr_name+'].master.dbo.autoindex_audit  order by Database_Name'

exec(@str)
open dblist

fetch next from dblist into @DBName


while(@@FETCH_STATUS=0)
begin

SET @ParmDefinition = N'@retvalOUT datetime OUTPUT'
	SET @STR='set @retvalOUT=(select top 1 RunDateTime from ['+@srvr_name+'].master.dbo.autoindex_audit where Database_Name='+''''+@DBName+''''+
' order by Audit_Id desc)'
	EXEC  sp_executesql @STR,@ParmDefinition, @retvalOUT=@LatestRunDate OUTPUT;

set @str='create synonym AA for ['+@srvr_name+'].master.[dbo].autoindex_audit'
	exec(@str)


begin try

insert into Fragmentation_Report select @appname,  @srvr_name, Database_Name, Table_Name,Index_Name,Avg_Fragmentation_In_Percent as Fragmentation_Before_ReIndex,
StartDateTime as ReIndex_StartDateTime, EndDateTime as ReIndex_EndDateTime, AFTER_Fragmentation as Fragmentation_After_ReIndex,
DATEDIFF(WEEK, RunDateTime, getdate()),case IsProcessed when 1 then 'Yes' when 0 then 'No' END
from AA where Database_Name=@DBName and RunDateTime=@LatestRunDate and ReOrganize_Or_ReBuild<>'N'

end try

begin catch
insert into server_fail_audit values (@srvr_name ,error_message(),GETDATE())
end catch

drop synonym AA

fetch next from dblist into @DBName
end




close dblist
deallocate dblist

fetch next from srvr_list into @srvr_name,@appname
end

close srvr_list
deallocate srvr_list

--select server_name, Database_Name, Table_Name, Index_Name, Fragmentation_Before_ReIndex, ReIndex_StartDateTime=case ReIndex_StartDateTime when NULL then 'NULL' else ReIndex_StartDateTime end,
--ReIndex_EndDateTime=case ReIndex_EndDateTime when NULL then 'NUL' else ReIndex_EndDateTime end, fragmentation_After_ReIndex= case Fragmentation_After_ReIndex when NULL then 'NULL' else Fragmentation_After_ReIndex end
select * from Fragmentation_Report

truncate table Fragmentation_Report
END

GO