获取SqlProfiler中的存储过程

--将 textdata 列由 text 类型改为 nvarchar(max)
ALTER TABLE traces_20170615 ALTER COLUMN textdata NVARCHAR(MAX) --改成实际的跟踪表
GO
--视图1: 获取存储过程（以 proc 开头则视为存储过程）
IF OBJECT_ID('view_traces') IS NOT NULL
	DROP VIEW view_traces
GO
CREATE VIEW view_traces
AS
SELECT CASE 
            WHEN CHARINDEX('Proc', textdata) > 0 THEN LEFT(
                     SUBSTRING(textdata, CHARINDEX('Proc', textdata), LEN(textdata)),
                     CHARINDEX(
                         ' ',
                         SUBSTRING(textdata, CHARINDEX('Proc', textdata), LEN(textdata))
                     )
                 )
            ELSE textdata
       END AS procName,
       CAST(duration / 1000.0 / 1000 AS DECIMAL(12, 1)) AS SECONDS,
       *
FROM   traces_20170615	--改成实际的跟踪表
GO

--视图2: 获取统计信息
IF OBJECT_ID('view_stat') IS NOT NULL
	DROP VIEW view_stat
GO
CREATE VIEW view_stat
AS
SELECT procName,
       COUNT(1)         AS cnt,
       MAX(SECONDS)     AS maxSeconds,
       AVG(SECONDS)     AS avgSeconds
FROM   view_traces
WHERE  procname IS NOT     NULL
GROUP BY
       procName