IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopLikeSQLText') = 1
DROP PROC TopLikeSQLText
GO
-- TopLikeSQLText PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@SQLTEXT';
-- EXAMPLE:
--
-- EXEC TopLikeSQLText '2009-03-10 00:00','2009-03-10 23:59','SEBOX1\SE12005','select * from'
CREATE PROC TopLikeSQLText
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50), @SQLTEXT VARCHAR(100))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT SQL_ID, SQL_STMT, TOTALSECS, TOTALEXECS, AVGSECS ' +
'FROM ( ' +
'SELECT ISNULL(N.NAME,ST.H) SQL_ID, ' +
'SQL_STMT, ' +
'SUM(TIMESECS) TOTALSECS, ' +
'SUM(EXECS) TOTALEXECS, ' +
'AVGSECS = CASE WHEN SUM(EXECS) = 0 THEN 0 ' +
'ELSE ROUND(SUM(TIMESECS)/SUM(EXECS),4) END ' +
'FROM ( ' +
'SELECT DISTINCT H ' +
'FROM CONST_' + @DBID +
' WHERE UPPER(ST) LIKE UPPER(' + '''' + '%' + @SQLTEXT + '%' + '''' + ')) ST ' +
'INNER JOIN CON_STATS_SUM_' + @DBID + ' SS ON SS.SQLHASH = ST.H ' +
'LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'INNER JOIN (SELECT H, ST SQL_STMT FROM CONST_' + @DBID + ' WHERE P = 0) UT ' +
'ON UT.H = ST.H ' +
'WHERE SS.DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'GROUP BY ISNULL(N.NAME,ST.H), SQl_STMT) SUBQ ' +
'ORDER BY 3 DESC'
EXEC (@SQL)
END
GO
