IF EXISTS
(
	SELECT 1
	FROM	INFORMATION_SCHEMA.ROUTINES
	WHERE	ROUTINE_NAME 	= 'ProcedureCacheUsage'
	AND	ROUTINE_SCHEMA 	= 'dbo'
	AND	ROUTINE_TYPE 	= 'PROCEDURE'
)
DROP PROC dbo.ProcedureCacheUsage
GO

CREATE PROC dbo.ProcedureCacheUsage
(
	@Table			sysname,
	@Owner			sysname		= NULL,
	@LoginName		nvarchar(256)	= NULL,
	@HostName		nvarchar(256) 	= NULL,
	@ApplicationName	nvarchar(256) 	= NULL,
	@NTUserName 		nvarchar(256)	= NULL,
	@StartTime		datetime	= NULL,
	@EndTime		datetime	= NULL,
	@Debug			bit		= 0
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SELECT nvarchar(25), @ColumnList nvarchar(650), @FROM nvarchar(150), @WHERE nvarchar(250), @ORDERBY nvarchar(250)

	SET @SELECT = 'SELECT ' 
	SET @ColumnList = '
				COALESCE(CAST(DATEDIFF(mi, MIN(StartTime), MAX(StartTime))/60. AS decimal(5, 2)), 0) AS ''Trace Duration (Hours)'',
				COALESCE(SUM(CASE WHEN EventClass = 42 THEN 1 ELSE 0 END), 0) AS ''Total Stored Procedures started'',
				COALESCE(SUM(CASE WHEN EventClass = 37 THEN 1 ELSE 0 END), 0) AS ''Total Stored Procedure Recompiled'',
				COALESCE(SUM(CASE WHEN EventClass = 34 THEN 1 ELSE 0 END), 0) AS ''Total Cache Misses'',
				COALESCE(SUM(CASE WHEN EventClass = 38 THEN 1 ELSE 0 END), 0) AS ''Total Cache Hits'',
				COALESCE(SUM(CASE WHEN EventClass = 39 THEN 1 ELSE 0 END), 0) AS ''Total Execution Context Hits'''

	SET @FROM = ' FROM ' + QUOTENAME(COALESCE(@Owner, 'dbo')) + '.' + QUOTENAME(@Table)

	SET @WHERE = ' WHERE EventClass IN (34, 37, 38, 39, 42)'

	IF @LoginName IS NOT NULL
	BEGIN
		SET @WHERE = @WHERE + ' AND LoginName LIKE ' + QUOTENAME(@LoginName, '''')
	END

	IF @HostName IS NOT NULL
	BEGIN
		SET @WHERE = @WHERE + ' AND HostName LIKE ' + QUOTENAME(@HostName, '''')
	END

	IF @ApplicationName IS NOT NULL
	BEGIN
		SET @WHERE = @WHERE + ' AND ApplicationName LIKE ' + QUOTENAME(@ApplicationName, '''')
	END	

	IF @NTUserName IS NOT NULL
	BEGIN
		SET @WHERE = @WHERE + ' AND NTUserName LIKE ' + QUOTENAME(@NTUserName, '''')
	END	

	IF (@StartTime IS NOT NULL) AND (@EndTime IS NOT NULL)
	BEGIN
		SET @WHERE = @WHERE + ' AND StartTime BETWEEN ' + QUOTENAME(CONVERT(varchar, @StartTime, 109), '''') + ' AND ' + QUOTENAME(CONVERT(varchar, @EndTime, 109), '''')
	END
	ELSE IF @StartTime IS NOT NULL
	BEGIN
		SET @WHERE = @WHERE + ' AND StartTime >= ' + QUOTENAME(CONVERT(varchar, @StartTime, 109), '''') 
	END
	ELSE IF @EndTime IS NOT NULL
	BEGIN
		SET @WHERE = @WHERE + ' AND StartTime <= ' + QUOTENAME(CONVERT(varchar, @EndTime, 109), '''') 
	END

	SET @ORDERBY = ''

	IF @Debug = 1
	BEGIN
		SELECT @SELECT + char(13) + @ColumnList + char(13) + @FROM + char(13) + @WHERE + char(13) + @ORDERBY
	END
	
	EXEC(@SELECT + @ColumnList + @FROM + @WHERE + @ORDERBY)
	
END