Recompile Stuck Procs : sp_recompile_stuck_procs

USE [master]
GO
/*
exec sp_recompile_stuck_procs 2
*/
CREATE PROCEDURE [dbo].[sp_recompile_stuck_procs]
(
@maxcount int = 10
)
AS
BEGIN
    SET NOCOUNT ON;


declare @datestr nvarchar(100), @sql nvarchar(4000), @tblname nvarchar(100)
--declare @maxcount int = 10
select @datestr = CONVERT(nvarchar(10),getdate(), 112)+'_'+CONVERT(nvarchar(10),datepart(hour,getdate()))+'_'+CONVERT(nvarchar(10),datepart(minute,getdate()))+'_'+CONVERT(nvarchar(10),datepart(SECOND,getdate()))
--select @datestr
set @tblname = '__drop_who3_collection_1_minute_'+@datestr

set @sql = 'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@tblname+']'') AND type in (N''U''))
create table '+@tblname+'
(SPID bigint,Status varchar(100),Login varchar(100),Host varchar(100),BlkBy bigint, TempDBWait varchar(100),DBName varchar(100),CommandType varchar(100),
SQLStatement varchar(max),ObjectName varchar(100),ElapsedMS bigint,CPUTime bigint,IOReads bigint,IOWrites bigint,LastWaitType varchar(100),
StartTime datetime,Protocol varchar(100),ConnectionWrites bigint,ConnectionReads bigint,ClientAddress  varchar(100),Authentication  varchar(100), Collectiontime datetime)
'
--print @sql
exec sp_executesql @sql   

set @sql = 'insert into '+@tblname+'
exec sp_who3 '
--print @sql
exec sp_executesql @sql   

set @sql = 'select dbname, objectname, count(*) as cc into '+@tblname+'_loop from '+@tblname+' where objectname is not null group by  dbname, objectname having count(*) > '+CONVERT(varchar, @maxcount)
--print @sql
exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname
----print @sql
--exec sp_executesql @sql   

--set @sql = 'select * from '+@tblname+'_loop'
----print @sql
--exec sp_executesql @sql   


---------------------------------------------------------
--declare @tblname nvarchar(100) = '__drop_who3_collection_1_minute_20120917_13_37_16' , @sql nvarchar(1000)
declare @tbl table (db nvarchar(100) , sp nvarchar(100), cc int)
declare @db nvarchar(100) , @sp nvarchar(100)

set @sql = 'select dbname, objectname, cc From '+@tblname+'_loop'

insert into @tbl
exec sp_executesql @sql

declare tmp_cur cursor for
select db, sp From @tbl

open tmp_cur
Fetch next from tmp_cur into @db, @sp

while @@fetch_status = 0
begin
    --print  @db
    --print @sp
    set @sql = 'use ' +@db +'
    exec sp_recompile '+replace(@sp, 'dbo.','')

    print @sql
    --exec sp_executesql @sql


Fetch next from tmp_cur into  @db, @sp
print '---'
end

close tmp_cur
deallocate tmp_cur

-----------------------------------
if (select COUNT(*) from @tbl) > 0
begin

DECLARE @tableHTML  NVARCHAR(MAX) ='...' , @subjectstring nvarchar(200)='...'

SET @tableHTML =
    N'

Proc Re-compile on '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N' ' +
    CAST ( ( SELECT td = db,       '',
                    td = sp,       '',
                    td = cc
              FROM @tbl
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Database	Proc	Counts

' ;
   
    print @tableHTML
   
set @subjectstring = 'Alert : Proc Re-compile on SQL Server '+@@SERVERNAME
print @subjectstring

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'default',
            @recipients ='patrick@csod.com',
            @body = @tableHTML,
            @body_format = 'HTML' ,
            @subject = @subjectstring;
           
end

---------------------------------------

set @sql = 'drop table '+@tblname+'_loop'
--print @sql
exec sp_executesql @sql   



set @sql = 'drop table '+@tblname
--print @sql
exec sp_executesql @sql   


END