Use DBA_Tools
GO

If object_id('Long_Running_Ops_usp') is not null
Begin
	Drop Procedure [Long_Running_Ops_usp]
End
Go

Create Procedure [dbo].[Long_Running_Ops_usp] 
As
--===============================================================
--== Procedure:			[dbo].[Long_Running_Ops_usp] 
--== Author:			Patrick Purviance
--== Version History:	
--==					v1.0 Created, PPurviance - 06/07/2010 
--==					v1.1 Added Seconds_to_Complete, PPurviance - 11/01/2010 
--== 
--===============================================================
Declare @version varchar(4),
		@SQL	 varchar(2000)

Select @version = left(convert(varchar, SERVERPROPERTY('ProductVersion')), 2)

Select @version = 
	Case @version 
		When '7.' then '7'
		When '8.' then '2000'
		When '9.' then '2005'
		When '10' then '2008'
	End
	
If convert(int, @version) < 2005
BEGIN
	Print 'Required Dynamic Management Object not available prior to SQL Server 2005'
	Return
END
ELSE
BEGIN
	SET @SQL  = 
		'SELECT	requests.[Session_ID], 
				[connections].[Client_Net_Address], 
				[sessions].[Host_Name], 
				[connections].[Connect_Time], 
				[sessions].[last_request_start_time] AS [Request_Start_Time],
				CURRENT_TIMESTAMP AS [Current_Time], 
				requests.[Percent_Complete],
				DATEDIFF (ss, CURRENT_TIMESTAMP, (DATEADD (MILLISECOND, requests.[estimated_completion_time], CURRENT_TIMESTAMP))) AS [Seconds_to_Complete], 
				DATEADD (MILLISECOND, requests.[estimated_completion_time], CURRENT_TIMESTAMP) AS [Estimated_Finish_Time],
				SUBSTRING([TSQL].[text], 
				requests.[statement_start_offset]/2, 
				COALESCE(NULLIF(requests.[statement_end_offset], -1)/2, 2147483647)) AS [Current_Command],
				COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME([TSQL].[objectid], [TSQL].[dbid])) + ''.'' + 
					QUOTENAME(OBJECT_NAME([TSQL].[objectid], [TSQL].[dbid])), ''<ad hoc>'') AS [Module] 
		FROM
			sys.dm_exec_requests AS [requests] INNER JOIN 
			sys.dm_exec_connections AS [connections] ON [requests].[session_id] = [connections].[session_id] INNER JOIN 
			sys.dm_exec_sessions AS [sessions] ON [requests].[session_id] = [sessions].[session_id] CROSS APPLY 
			sys.dm_exec_sql_text([requests].[sql_handle]) AS [TSQL]
		WHERE [requests].[percent_complete] <> 0;'
	
	Exec(@SQL)
END
;