IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopSQLforDatabase') = 1
DROP PROC TopSQLforDatabase
GO
-- TopSQLforDatabase PROC
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@CONDNAME' , '@DBNAME'
-- EXAMPLE:
--
-- exec TopSQLforDatabase '2015-04-21 00:00','2015-04-22 00:00','WT10A_DEMO','MASTER'
CREATE PROC TopSQLforDatabase
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @CONDNAME VARCHAR(50), @DBNAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @CONDNAME
SELECT @SQL = 'SELECT ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME, SUM(SW.QP/100) TIMESECS ' +
'FROM CONSW_' + @DBID + ' SW LEFT OUTER JOIN CON_SQL_NAME N ON SW.IZHO = N.HASH ' +
'INNER JOIN CONO_' + @DBID + ' O ON SW.IXOY = O.ID ' +
'WHERE SW.D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'AND UPPER(O.NAME) = UPPER(' + '''' + @DBNAME + '''' + ') ' +
'GROUP BY ISNULL(N.NAME, SW.IZHO) ' +
'ORDER BY SUM(SW.QP/100) DESC '
EXEC (@SQL)
END
GO
