CREATE PROCEDURE dbo.ProfileImport
@StartTime SMALLDATETIME = NULL OUTPUT,
@EndTime SMALLDATETIME = NULL OUTPUT,
@ApplicationNameList VARCHAR(MAX) = NULL,
@ApplicationNameDelimiter VARCHAR(10) = ',',
@ApplicationName_Equal_WildChar_CharIndex TINYINT = 2,
@HostNameList VARCHAR(MAX) = NULL,
@HostNameDelimiter VARCHAR(10) = ',',
@HostName_Equal_WildChar_CharIndex TINYINT = 2,
@LoginNameList VARCHAR(MAX) = NULL,
@LoginNameDelimiter VARCHAR(10) = ',',
@LoginName_Equal_WildChar_CharIndex TINYINT = 2,
@DatabaseNameList VARCHAR(MAX) = NULL,
@DatabaseNameDelimiter VARCHAR(10) = ',',
@DatabaseName_Equal_WildChar_CharIndex TINYINT = 2,
@MinDurationSec INT = NULL,
@TextDataList VARCHAR(MAX) = NULL,
@TextDataDelimiter VARCHAR(10) = ',',
@TextData_Equal_WildChar_CharIndex TINYINT = 2,
@Tokenize BIT = 0,
@TemporaryFileName VARCHAR(256) = NULL OUTPUT,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@ExecuteSQL BIT = 1,
@PrintSQL BIT = 0,
@ImportAuditSQLStmt BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @Tokenize = 1
BEGIN
IF OBJECT_ID('[Util].[dbo].[TokenizeSQL]') IS NULL
SET @Tokenize = 0
END
IF @StartTime IS NULL
AND @EndTime IS NULL
BEGIN
EXEC sp_ExecTemplate
@ObjectName = 'ProfileImport',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
SELECT @StartTime = ISNULL(@StartTime, CAST(CAST(GETDATE() AS DATE) AS SMALLDATETIME)),
@EndTime = ISNULL(@EndTime, GETDATE()),
@TemporaryFileName = ISNULL(@TemporaryFileName,
'Temporary..ProfileReport_' + CONVERT(VARCHAR(8), GETDATE(), 112) + '_' + REPLACE(CONVERT(VARCHAR(12), GETDATE(), 114), ':',
''))
DECLARE @ProfilePath VARCHAR(1000),
@LoginNameCount INT,
@ApplicationNameCount INT,
@DatabaseNameCount INT,
@TextDataCount INT,
@HostNameCount INT
IF OBJECT_ID('TEMPDB..#LoginNames') IS NOT NULL
DROP TABLE #LoginNames
CREATE TABLE #LoginNames (LoginName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #LoginNames
SELECT CASE WHEN @LoginName_Equal_WildChar_CharIndex = 2
AND Field NOT LIKE '%[%]%' THEN '%' + Field + '%'
ELSE Field
END AS Field
FROM dbo.ParseDelimited(@LoginNameList, ISNULL(NULLIF(@LoginNameDelimiter, ''), ','))
WHERE LTRIM(RTRIM(Field)) <> ''
SET @LoginNameCount = @@ROWCOUNT
IF OBJECT_ID('TEMPDB..#HostNames') IS NOT NULL
DROP TABLE #HostNames
CREATE TABLE #HostNames (HostName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #HostNames
SELECT CASE WHEN @HostName_Equal_WildChar_CharIndex = 2
AND Field NOT LIKE '%[%]%' THEN '%' + Field + '%'
ELSE Field
END AS Field
FROM dbo.ParseDelimited(@HostNameList, ISNULL(NULLIF(@HostNameDelimiter, ''), ','))
WHERE LTRIM(RTRIM(Field)) <> ''
SET @HostNameCount = @@ROWCOUNT
IF OBJECT_ID('TEMPDB..#ApplicationNames') IS NOT NULL
DROP TABLE #ApplicationNames
CREATE TABLE #ApplicationNames (ApplicationName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #ApplicationNames
SELECT CASE WHEN @ApplicationName_Equal_WildChar_CharIndex = 2
AND Field NOT LIKE '%[%]%' THEN '%' + Field + '%'
ELSE Field
END AS Field
FROM dbo.ParseDelimited(@ApplicationNameList, ISNULL(NULLIF(@ApplicationNameDelimiter, ''), ','))
WHERE LTRIM(RTRIM(Field)) <> ''
SET @ApplicationNameCount = @@ROWCOUNT
IF OBJECT_ID('TEMPDB..#DatabaseNames') IS NOT NULL
DROP TABLE #DatabaseNames
CREATE TABLE #DatabaseNames (DatabaseName VARCHAR(256) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #DatabaseNames
SELECT CASE WHEN @DatabaseName_Equal_WildChar_CharIndex = 2
AND Field NOT LIKE '%[%]%' THEN '%' + Field + '%'
ELSE Field
END AS Field
FROM dbo.ParseDelimited(@DatabaseNameList, ISNULL(NULLIF(@DatabaseNameDelimiter, ''), ','))
WHERE LTRIM(RTRIM(Field)) <> ''
SET @DatabaseNameCount = @@ROWCOUNT
IF OBJECT_ID('TEMPDB..#TextDatas') IS NOT NULL
DROP TABLE #TextDatas
CREATE TABLE #TextDatas (TextData VARCHAR(8000) NOT NULL
PRIMARY KEY CLUSTERED)
INSERT #TextDatas
SELECT CASE WHEN @TextData_Equal_WildChar_CharIndex = 2
AND Field NOT LIKE '%[%]%' THEN '%' + Field + '%'
ELSE Field
END AS Field
FROM dbo.ParseDelimited(@TextDataList, ISNULL(NULLIF(@TextDataDelimiter, ''), ','))
SET @TextDataCount = @@ROWCOUNT
SELECT @ProfilePath = ProfilePath
FROM dbo.Servers
WHERE [SQLSERVERNAME] = @@SERVERNAME ;
WITH profile01
AS (SELECT [name],
[Size],
CAST([CreateDate] AS SMALLDATETIME) AS [CreateDate],
CAST([LastWritten] AS SMALLDATETIME) AS [LastWritten],
CAST([LastAccessed] AS SMALLDATETIME) AS [LastAccessed],
[attributes]
FROM FS.GetDirectoryInfo(@ProfilePath, @@SERVICENAME + CASE WHEN @ImportAuditSQLStmt = 1 THEN '-AuditTraceSQLStmt-*.trc'
ELSE '-AuditTrace-*.trc'
END)
WHERE [IsDirectory] = 0),
profile02
AS (SELECT CASE WHEN @EndTime > MAX([LastWritten]) THEN MAX([LastWritten])
ELSE @EndTime
END AS EndFileDate,
CASE WHEN @StartTime < MIN([CreateDate]) THEN MIN([CreateDate])
ELSE @StartTime
END AS StartFileDate
FROM profile01),
ProfileFilesList
AS (SELECT TOP 999999
[name] AS FileName,
@ProfilePath + [name] AS FullPath,
[Size],
[CreateDate],
[LastWritten],
[LastAccessed],
[attributes]
FROM profile01 pl
CROSS JOIN profile02 dr
WHERE (dr.EndFileDate BETWEEN pl.[CreateDate] AND pl.LastWritten)
OR (dr.StartFileDate BETWEEN pl.[CreateDate] AND pl.LastWritten)
OR (dr.StartFileDate <= pl.[CreateDate]
AND pl.LastWritten <= dr.EndFileDate)
ORDER BY [CreateDate])
SELECT @SQL = 'IF OBJECT_ID(''' + @TemporaryFileName + ''') IS NOT NULL DROP TABLE ' + @TemporaryFileName + '
CREATE TABLE ' + @TemporaryFileName + ' (
[RowNum] INT IDENTITY NOT NULL,
[Category] varchar(128) NULL,
[EVENT] varchar(128) NULL,
[HostName] varchar(256) NULL,
[ApplicationName] varchar(256) NULL,
[LoginName] varchar(256) NULL,
[DatabaseName] varchar(256) NULL,
[SPID] int NULL,
[Duration] numeric(20, 3) NULL,
[READS] bigint NULL,
[Writes] bigint NULL,
[CPU] int NULL,
[StartTime] datetime NULL,
[EndTime] datetime NULL,
[textdata] varchar(MAX) NULL,' + CASE WHEN @Tokenize = 1 THEN '
[TokenSQL] varchar(MAX) NULL,' ELSE ''
END + '
[FileName] varchar(256) NOT NULL)
' + dbo.StringConcat('INSERT ' + @TemporaryFileName
+ '([Category], [EVENT], [HostName], [ApplicationName], [LoginName], [DatabaseName], [SPID], [Duration], [READS], [Writes], [CPU], [StartTime], [EndTime], [textdata], '
+ CASE WHEN @Tokenize = 1 THEN '[TokenSQL], '
ELSE ''
END + ' [FileName])
SELECT tc.name AS Category,
te.name AS EVENT,
HostName,
ApplicationName,
LoginName,
DatabaseName,
SPID,
CAST(Duration / 1000000.0 as NUMERIC(20,3)) AS Duration,
READS,
Writes,
CPU,
StartTime,
EndTime,
LTRIM(RTRIM(REPLACE(CAST(textdata AS VARCHAR(MAX)), CHAR(0), ''''))) as textdata,' + CASE WHEN @Tokenize = 1 THEN '
[Util].[dbo].[TokenizeSQL](0, TextData) AS TokenSQL,' ELSE ''
END + '
''' + FileName + ''' as FileName
FROM FN_TRACE_GETTABLE(''' + FullPath + ''', 1) tr
INNER JOIN sys.trace_events te (NOLOCK)
ON te.trace_event_id = tr.EventClass
LEFT OUTER JOIN sys.trace_categories tc (NOLOCK)
ON tc.category_id = te.category_id
WHERE
(
(StartTime BETWEEN ''' + CONVERT(VARCHAR(30), @StartTime, 120) + ''' AND ''' + CONVERT(VARCHAR(30), @EndTime, 120) + ''')
OR
(EndTime BETWEEN ''' + CONVERT(VARCHAR(30), @StartTime, 120) + ''' AND ''' + CONVERT(VARCHAR(30), @EndTime, 120) + ''')
OR
(''' + CONVERT(VARCHAR(30), @StartTime, 120) + ''' <= StartTime AND EndTime >= ''' + CONVERT(VARCHAR(30), @EndTime, 120) + ''')
)
' + CASE WHEN @TextDataCount > 0 THEN CASE @TextData_Equal_WildChar_CharIndex
WHEN 1 THEN 'AND EXISTS(SELECT * FROM #TextDatas x WHERE CAST(tr.TextData AS VARCHAR(MAX)) = x.TextData)'
WHEN 2 THEN 'AND EXISTS(SELECT * FROM #TextDatas x WHERE CAST(tr.TextData AS VARCHAR(MAX)) LIKE x.TextData)'
WHEN 3 THEN 'AND EXISTS(SELECT * FROM #TextDatas x WHERE CHARINDEX(x.TextData, CAST(tr.TextData AS VARCHAR(MAX))) > 0)'
ELSE ''
END + '
' ELSE ''
END + CASE WHEN @ApplicationNameCount > 0
THEN CASE @ApplicationName_Equal_WildChar_CharIndex
WHEN 1 THEN 'AND EXISTS(SELECT * FROM #ApplicationNames x WHERE tr.ApplicationName = x.ApplicationName)'
WHEN 2 THEN 'AND EXISTS(SELECT * FROM #ApplicationNames x WHERE tr.ApplicationName LIKE x.ApplicationName)'
WHEN 3 THEN 'AND EXISTS(SELECT * FROM #ApplicationNames x WHERE CHARINDEX(x.ApplicationName, tr.ApplicationName) > 0)'
ELSE ''
END + '
' ELSE ''
END + +CASE WHEN @HostNameCount > 0 THEN CASE @HostName_Equal_WildChar_CharIndex
WHEN 1 THEN 'AND EXISTS(SELECT * FROM #HostNames x WHERE tr.HostName = x.HostName)'
WHEN 2 THEN 'AND EXISTS(SELECT * FROM #HostNames x WHERE tr.HostName LIKE x.HostName)'
WHEN 3 THEN 'AND EXISTS(SELECT * FROM #HostNames x WHERE CHARINDEX(x.HostName, tr.HostName) > 0)'
ELSE ''
END + '
' ELSE ''
END + CASE WHEN @LoginNameCount > 0 THEN CASE @LoginName_Equal_WildChar_CharIndex
WHEN 1 THEN 'AND EXISTS(SELECT * FROM #LoginNames x WHERE tr.LoginName = x.LoginName)'
WHEN 2 THEN 'AND EXISTS(SELECT * FROM #LoginNames x WHERE tr.LoginName LIKE x.LoginName)'
WHEN 3 THEN 'AND EXISTS(SELECT * FROM #LoginNames x WHERE CHARINDEX(x.LoginName, tr.LoginName) > 0)'
ELSE ''
END + '
' ELSE ''
END
+ CASE WHEN @DatabaseNameCount > 0
THEN CASE @DatabaseName_Equal_WildChar_CharIndex
WHEN 1 THEN 'AND EXISTS(SELECT * FROM #DatabaseNames x WHERE tr.DatabaseName = x.DatabaseName)'
WHEN 2 THEN 'AND EXISTS(SELECT * FROM #DatabaseNames x WHERE tr.DatabaseName LIKE x.DatabaseName)'
WHEN 3 THEN 'AND EXISTS(SELECT * FROM #DatabaseNames x WHERE CHARINDEX(x.DatabaseName, tr.DatabaseName) > 0)'
ELSE ''
END + '
' ELSE ''
END + ISNULL('AND (Duration / 1000000) >= ' + CAST(@MinDurationSec AS VARCHAR) + '
', ''), '
') + '
SELECT
TOP 1000
CASE WHEN textdata IS NOT NULL THEN(SELECT 1 AS tag, NULL AS parent,''
'' + textdata + ''
'' AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS textdataxml' + CASE WHEN @Tokenize = 1 THEN ',
CASE WHEN TokenSQL IS NOT NULL THEN(SELECT 1 AS tag, NULL AS parent,''
'' + TokenSQL + ''
'' AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS TokenSQLXML
' ELSE ''
END + ',
*
FROM ' + @TemporaryFileName + '
-- DROP TABLE ' + @TemporaryFileName + '
'
FROM ProfileFilesList
IF @SQL <> ''
BEGIN
IF @PrintSQL = 1
BEGIN
SELECT '#LoginNames' AS [Table],
@LoginName_Equal_WildChar_CharIndex AS Equal_WildChar_CharIndex,
'LoginName' AS ColumnName,
LoginName AS Value
FROM #LoginNames
UNION ALL
SELECT '#HostNames' AS [Table],
@HostName_Equal_WildChar_CharIndex AS Equal_WildChar_CharIndex,
'HostName' AS ColumnName,
HostName AS Value
FROM #HostNames
UNION ALL
SELECT '#ApplicationNames' AS [Table],
@ApplicationName_Equal_WildChar_CharIndex AS Equal_WildChar_CharIndex,
'ApplicationName' AS ColumnName,
ApplicationName AS Value
FROM #ApplicationNames
UNION ALL
SELECT '#DatabaseNames' AS [Table],
@DatabaseName_Equal_WildChar_CharIndex AS Equal_WildChar_CharIndex,
'DatabaseName' AS ColumnName,
DatabaseName AS Value
FROM #DatabaseNames
UNION ALL
SELECT '#TextDatas' AS [Table],
@TextData_Equal_WildChar_CharIndex AS Equal_WildChar_CharIndex,
'TextData' AS ColumnName,
TextData AS Value
FROM #TextDatas
EXEC dbo.PrintLargeText
@SQL
END
IF @ExecuteSQL = 1
EXEC(@SQL)
PRINT ' --- ALL SQL STATEMENTS ---
SELECT
TOP 1000
CASE WHEN textdata IS NOT NULL THEN(SELECT 1 AS tag, NULL AS parent, ''
'' + textdata + ''
'' AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS textdataxml
' + CASE WHEN @Tokenize = 1 THEN ', CASE WHEN TokenSQL IS NOT NULL THEN(SELECT 1 AS tag, NULL AS parent,''
'' + TokenSQL + ''
'' AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS TokenSQLXML'
ELSE ''
END + ',
*
FROM ' + @TemporaryFileName + '
' + CASE WHEN @ImportAuditSQLStmt = 1 THEN ''
ELSE '
--- NOT COMPLETED SQL STATEMENTS ---
SELECT
TOP 1000
CASE WHEN textdata IS NOT NULL THEN(SELECT 1 AS tag, NULL AS parent,''
'' + textdata + ''
'' AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS textdataxml,
' + CASE WHEN @Tokenize = 1 THEN ', CASE WHEN TokenSQL IS NOT NULL THEN(SELECT 1 AS tag, NULL AS parent,''
'' + TokenSQL + ''
'' AS [Q!1!Q!CDATA] FOR XML EXPLICIT, TYPE) END AS TokenSQLXML'
ELSE ''
END + ',
*
FROM ' + @TemporaryFileName + ' a
WHERE NOT EXISTS(SELECT *
FROM ' + @TemporaryFileName + ' b
WHERE b.EVENT IN(''SQL:BatchCompleted'', ''RPC:Completed'')
AND a.[SPID] = B.[SPID]
AND a.[textdata] = B.[textdata]
AND a.LoginName = B.LoginName
AND a.StartTime = B.StartTime)
AND a.EVENT IN(''SQL:BatchStarting'', ''RPC:Starting'')
ORDER by A.StartTime DESC'
END
END