CREATE PROCEDURE [dbo].[GetSQLStatisticsIO]
@Database VARCHAR(256) = NULL,
@SQL VARCHAR(MAX) = NULL,
@Tag VARCHAR(256) = NULL,
@Rowcount INT = 0 OUTPUT
AS
SET NOCOUNT ON
IF ISNULL(@Database, '') = ''
OR ISNULL(@SQL, '') = ''
BEGIN
PRINT '
/*
-- SILENT OPERATION LOCAL TEMP
IF OBJECT_ID(''tempdb..#Stats'') IS NOT NULL DROP TABLE #Stats
CREATE TABLE #Stats (Id INT, TableName VARCHAR(256), ScanCount INT, LogicalReads INT, PhysicalReads INT, ReadAheadReads INT, LobLogicalReads INT, LobPhysicalReads INT, LobReadAheadReads INT, Tag VARCHAR(256))
-- SILENT OPERATION GLOBAL TEMP
IF OBJECT_ID(''tempdb..##Stats'') IS NOT NULL DROP TABLE ##Stats
CREATE TABLE ##Stats (Id INT, TableName VARCHAR(256), ScanCount INT, LogicalReads INT, PhysicalReads INT, ReadAheadReads INT, LobLogicalReads INT, LobPhysicalReads INT, LobReadAheadReads INT, Tag VARCHAR(256))
*/
'
EXEC sp_ExecTemplate
@ObjectName = '[dbo].[GetSQLStatisticsIO]',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN 30
END
IF ISNULL(@Tag, '') = ''
SET @Tag = CONVERT(VARCHAR(30), GETDATE(), 114) + ' - ' + RIGHT(CAST(NEWID() AS VARCHAR(256)), 8)
IF OBJECT_ID('tempdb..#Stats') IS NOT NULL
INSERT #Stats
(Id,
TableName,
ScanCount,
LogicalReads,
PhysicalReads,
ReadAheadReads,
LobLogicalReads,
LobPhysicalReads,
LobReadAheadReads,
Tag)
EXEC [dbo].[GetSQLStatisticsIO_CLR]
@ServerName = @@SERVERNAME,
@Database = @Database,
@SQL = @SQL,
@Tag = @Tag
ELSE
IF OBJECT_ID('tempdb..##Stats') IS NOT NULL
INSERT ##Stats
(Id,
TableName,
ScanCount,
LogicalReads,
PhysicalReads,
ReadAheadReads,
LobLogicalReads,
LobPhysicalReads,
LobReadAheadReads,
Tag)
EXEC [dbo].[GetSQLStatisticsIO_CLR]
@ServerName = @@SERVERNAME,
@Database = @Database,
@SQL = @SQL,
@Tag = @Tag
ELSE
EXEC [dbo].[GetSQLStatisticsIO_CLR]
@ServerName = @@SERVERNAME,
@Database = @Database,
@SQL = @SQL,
@Tag = @Tag
SET @Rowcount = @@ROWCOUNT