RETURN
SELECT Util.dbo.StringConcat(DISTINCT DataFolder , ','),
Util.dbo.StringConcat(DISTINCT '''' + DataFolder + '''', ',')
FROM Diablo.Control.vLoadControlDetail (NOLOCK)
WHERE PromoteCompleteTime > DATEADD(HOUR, -20, GETDATE())
GO
RETURN
-- Lets get the profile results for a list of load controls for the last 4 days
DECLARE @StartTime DATETIME = DATEADD(DAY, -7, GETDATE()),
@EndTime DATETIME = GETDATE()
EXEC Util.dbo.ProfileImport
@StartTime = @StartTime,
@EndTime = @EndTime,
@ApplicationNameList = '%trans%',
@ApplicationNameDelimiter = ',',
@ApplicationName_Equal_WildChar_CharIndex = 2,
@HostNameList = NULL,
@HostNameDelimiter = ',',
@HostName_Equal_WildChar_CharIndex = 2,
@LoginNameList = NULL,
@LoginNameDelimiter = ',',
@LoginName_Equal_WildChar_CharIndex = 2,
@DatabaseNameList = NULL,
@DatabaseNameDelimiter = ',',
@DatabaseName_Equal_WildChar_CharIndex = 2,
@MinDurationSec = NULL,
@TextDataList = 'db013_2,db014_13,db014_14,db014_15,db014_16,db014_17,db014_18,db014_19,db014_20,db014_21,db014_22,db014_23,db014_24,db014_25,db015_10,db015_11,db015_12,db015_13,db015_14,db015_15,db015_16,db015_17,db015_18',
@TextDataDelimiter = ',',
@TextData_Equal_WildChar_CharIndex = 2,
@TemporaryFileName = NULL /*OUTPUT*/,
@SQL = NULL /*OUTPUT*/,
@ExecuteSQL = 1,
@PrintSQL = 0,
@ImportAuditSQLStmt = 0
GO
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp
GO
-- Lets query the table created at previous step and join to loadcontroldetail to get the DataFolder, cntycde
SELECT ROW_NUMBER() OVER (ORDER BY TextData) AS TextId,
DENSE_RANK() OVER (PARTITION BY DataFolder ORDER BY TextData) AS QueryId,
ROW_NUMBER() OVER (PARTITION BY DataFolder, TextData ORDER BY starttime) AS QueryRow,
*
INTO #temp
FROM (SELECT d.DataFolder,
CASE WHEN Tokened LIKE 'EXEC%'
OR Tokened LIKE 'exec sp_executesql N''EXEC%' THEN 1
ELSE 0
END AS SPCall,
CntyCD,
StartTime,
READS,
Writes,
CPU,
Duration,
Tokened AS TextData,
OrigTextData
/* MAKE SURE TO CHANGE THE PROFILE TABLLE NAME */
FROM TEMPORARY.[dbo].[ProfileReport_20110927_062538003] a
CROSS APPLY (SELECT TOP 1
DataFolder,
CntyCD
FROM Diablo.Control.vLoadControlDetail b (NOLOCK)
CROSS APPLY (SELECT CHARINDEX (b.DataFolder + '_', a.TextData) AS ci) c
WHERE b.DataFolder IN ('db013_2', 'db014_13', 'db014_14', 'db014_15', 'db014_16', 'db014_17', 'db014_18', 'db014_19', 'db014_20',
'db014_21', 'db014_22', 'db014_23', 'db014_24', 'db014_25', 'db015_10', 'db015_11', 'db015_12', 'db015_13',
'db015_14', 'db015_15', 'db015_16', 'db015_17', 'db015_18')
AND CHARINDEX (b.DataFolder + '_', a.TextData)>0 ) d
CROSS APPLY (SELECT REPLACE (REPLACE (TextData, DataFolder, 'DataFolder'), CntyCD, 'CNTY_CD') AS OrigTextData) od
CROSS APPLY (SELECT CASE WHEN OrigTextData NOT LIKE 'exec sp_executesql%' THEN [Util].[dbo].[TokenizeSQL](0, OrigTextData)
ELSE OrigTextData
END AS Tokened) t
WHERE Duration IS NOT NULL
AND textdata NOT LIKE '%set fmtonly%'
AND CHARINDEX('exec [sys].sp_tablecollations', textdata) = 0) k
ORDER BY TextData,
StartTime
GO
SELECT TextId,
QueryId,
QueryRow,
DataFolder,
CntyCD,
SPCall,
StartTime,
READS,
Writes,
CPU,
Duration,
LEN(TextData) AS LenText,
LEN(OrigTextData) AS LenOrig,
TextData,
OrigTextData
--,Util.dbo.CastXML(TextData) AS TextDataXML
--,Util.dbo.CastXML(OrigTextData) AS OrigTextDataXML
FROM #temp
ORDER BY DataFolder,
queryid,
QueryRow
go
-- SELECT * FROM #temp WHERE spcall = 1