NutsAndBolts_V1.0

USE [NutsAndBolts]
GO
/****** Object:  StoredProcedure [dbo].[alert_AlterFailedJobInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    auto alter failed schedule job info
-- =============================================
CREATE PROCEDURE [dbo].[alert_AlterFailedJobInfo] 
   @p_JobName             VARCHAR(255) = NULL,  -- Optional job name filter  
   @p_ShowDisabled        BIT = 0, -- Include disabled jobs?  
   @p_ShowUnscheduled     BIT = 0, -- Include Unscheduled jobs?  
   @p_JobThresholdSec     INT = 0, -- If positive, show only the jobs with LAST duration above this.  
   @p_AvgExecThresholdSec INT = 0  -- If positive, show only the jobs with AVERAGE duration above this.  

AS 
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT * INTO #TJob
     FROM ( SELECT JobName, 
                   ISNULL(LastStep,'') LastStep,
                   CASE WHEN StartDate IS NOT NULL AND FinishDate IS NULL THEN 'Running' 
                        WHEN Enabled = 0 THEN 'Disabled' 
                        WHEN StepCount = 0 THEN 'No steps' 
                        WHEN RunStatus IS NOT NULL THEN RunStatus 
                        WHEN ScheduleCount = 0 THEN 'Not scheduled' 
                        ELSE 'UNKNOWN' END Info,
                   DatabaseName, 
                   Enabled, 
                   ScheduleCount, 
                   StepCount, 
                   StartDate, 
                   FinishDate, 
                   DurationSec, 
                   RIGHT('0'+convert(varchar(5),DurationSec/3600),2)+':'+
                   RIGHT('0'+convert(varchar(5),DurationSec%3600/60),2)+':'+ 
                   RIGHT('0'+convert(varchar(5),(DurationSec%60)),2) DurationSecFormatted, 
                   avgDurationSec,
                   RIGHT('0'+convert(varchar(5),avgDurationSec/3600),2)+':'+
                   RIGHT('0'+convert(varchar(5),avgDurationSec%3600/60),2)+':'+ 
                   RIGHT('0'+convert(varchar(5),(avgDurationSec%60)),2) avgDurationSecFormatted, 
                   CASE WHEN (DurationSec IS NULL OR ISNULL(avgDurationSec, 0) = 0) THEN 0 
                        ELSE CONVERT(DECIMAL(18,2), (100*CAST(DurationSec AS DECIMAL)) / CAST (avgDurationSec as DECIMAL)) END AS DurationRatio, 
                   NextRunDate, 
                   StepCommand, 
                   HistoryMessage 
              FROM ( SELECT j.name JobName,
                            j.enabled Enabled, 
                            (SELECT COUNT(1) FROM msdb..sysjobschedules jss WHERE jss.job_id = j.job_id) ScheduleCount, 
                            (SELECT COUNT(1) FROM msdb..sysjobsteps jps WHERE jps.job_id = j.job_id) StepCount, 
                            ls1.job_history_id HistoryID, 
                            ls1.start_execution_date StartDate, 
                            ls1.stop_execution_date FinishDate, 
                            ls1.last_executed_step_id LastStepID, 
                            DATEDIFF(SECOND, 
                                     ls1.start_execution_date, 
                                     CASE WHEN ls1.stop_execution_date IS NULL THEN GETDATE() 
                                     ELSE ls1.stop_execution_date END) DurationSec, 
                            ISNULL(avgSec, 0) avgDurationSec, 
                            ls1.next_scheduled_run_date NextRunDate, 
                            st.step_name LastStep, 
                            st.command StepCommand, 
                            st.database_name DatabaseName, 
                            h.message HistoryMessage, 
                            CASE WHEN h.job_id IS NULL THEN 'Never Run' 
                            ELSE CASE h.run_status WHEN 0 THEN 'Failed' 
                                                   WHEN 1 THEN 'Succeeded' 
                                                   WHEN 2 THEN 'Retry' 
                                                   WHEN 3 THEN 'Canceled' END END RunStatus, 
                            h.run_date rawRunDate, 
                            h.run_time rawRunTime, 
                            h.run_duration rawRunDuration 
                       FROM msdb..sysjobactivity ls1 (NOLOCK) 
                            INNER JOIN msdb..sysjobs j (NOLOCK) ON ls1.job_id = j.job_id 
                            INNER JOIN (SELECT job_id JobID, 
                                               MAX(session_id) LastSessionID 
                                          FROM msdb..sysjobactivity (NOLOCK) 
                                      GROUP BY job_id ) ls2 ON ls1.job_id = ls2.JobID 
                                                               AND ls1.session_id = ls2.LastSessionID 
                            LEFT OUTER JOIN msdb..sysjobsteps st (NOLOCK) ON st.job_id = j.job_id 
                                                                     AND ls1.last_executed_step_id = st.step_id 
                            LEFT OUTER JOIN msdb..sysjobhistory h (NOLOCK) ON h.instance_id = ls1.job_history_id 
                            LEFT OUTER JOIN ( SELECT j.job_id JobID, 
                                                     SUM(h.avgSecs) avgSec 
                                                FROM msdb..sysjobs j (NOLOCK) 
                                                     INNER JOIN ( SELECT job_id, 
                                                                         step_id, 
                                                                         AVG(run_duration/10000*3600 + 
                                                                             run_duration%10000/100*60 + 
                                                                             run_duration%100) avgSecs 
                                                                    FROM msdb..sysjobhistory 
                                                                   WHERE step_id > 0 
                                                                 AND run_status = 1 
                                                               GROUP BY job_id,
                                                                        step_id ) h ON j.job_id = h.job_id 
                                             GROUP BY j.job_id ) jobavg ON jobavg.JobID = j.job_id )jj 
             WHERE (@p_ShowDisabled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR Enabled = 1) 
                   AND (@p_JobName IS NULL OR JobName = @p_JobName) 
                   AND (@p_ShowUnscheduled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR ScheduleCount > 0) 
                   AND (@p_JobThresholdSec = 0 OR DurationSec >= @p_JobThresholdSec) 
                   AND (@p_AvgExecThresholdSec = 0 OR avgDurationSec >= @p_AvgExecThresholdSec))x 
 ORDER BY CASE Info 
          WHEN 'Running' THEN 0 
          WHEN 'Failed' THEN 1 
          WHEN 'Retry' THEN 2
          WHEN 'Succeeded' THEN 3
          WHEN 'Canceled' THEN 4
          WHEN 'No steps' THEN 5 
          WHEN 'Not scheduled' THEN 6
          WHEN 'Disabled' THEN 7
          WHEN 'Never Run' THEN 8
          WHEN 'UNKNOWN' THEN -1
          ELSE -2 END, 
          NextRunDate, 
          JobName



   --send alert report part 
   IF EXISTS(SELECT 1 FROM #TJob WHERE Info IN ('Failed') )
   BEGIN 
      SELECT * INTO #TReport FROM #TJob WHERE Info IN ('Failed')

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Alert Failed Job' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
   END

   DROP TABLE #TJob
END

GO
/****** Object:  StoredProcedure [dbo].[dbm_KillInactiveConnection]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-06
-- Description:    kill the inactive connection which last 1 minute
-- =============================================
CREATE PROCEDURE [dbo].[dbm_KillInactiveConnection] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE cConnections CURSOR READ_ONLY FAST_FORWARD FOR
      SELECT spid
        FROM master..sysprocesses p
             INNER JOIN master..syslogins l ON p.sid = l.sid
       WHERE l.loginname LIKE 'MSDOMAIN1\%' AND l.loginname NOT IN ('MSDOMAIN1\xzhang3', 'MSDOMAIN1\ouli', 'MSDOMAIN1\atian1')
             AND p.last_batch <= DATEADD(mi, -1, GETDATE())

   DECLARE @l_ConnectionId INT,
           @l_Sql          VARCHAR(100)

   OPEN cConnections

   WHILE 1 = 1
   BEGIN
      FETCH NEXT FROM cConnections INTO @l_ConnectionId
   
      IF @@FETCH_STATUS <> 0
         BREAK
   
      SET @l_Sql = 'KILL ' + CONVERT(VARCHAR, @l_ConnectionId)
      EXECUTE( @l_Sql )
   END

   CLOSE cConnections
   DEALLOCATE cConnections

END

GO
/****** Object:  StoredProcedure [dbo].[demo_EmailHtmlTableAlert]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Purpose: a template for sending alert script part    
-- Create Date: 08/29/2012     
-- Last Update: 08/29/2012  
-- Author:      Alex Tian  
CREATE PROCEDURE [dbo].[demo_EmailHtmlTableAlert] 
AS 
BEGIN

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   --generate a temp table 
   SELECT TOP 10 *  INTO #TAlert FROM master.sys.objects

   DECLARE @l_Html NVARCHAR(max) 
   DECLARE @l_HHeader NVARCHAR(max) 
   DECLARE @l_QColumn NVARCHAR(max) 
   DECLARE @l_TColumn NVARCHAR(max) 
   DECLARE @l_TQuery NVARCHAR(max) 
   DECLARE @l_EmailObject NVARCHAR(200) 
   DECLARE @l_EmailBody NVARCHAR(max) 

   SET @l_Html ='' 
   SET @l_HHeader ='' 
   SET @l_QColumn ='' 
   SET @l_TColumn ='' 
   SET @l_TQuery ='' 
   SET @l_EmailObject='demo_EmailHtmlTableAlert' 
   SET @l_EmailBody='' 

   SELECT @l_TColumn = @l_TColumn + name + ',' 
     FROM tempdb.sys.columns 
    WHERE object_id = object_id('tempdb..#TAlert') 

   SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
   SET @l_QColumn=dbo.Fn_splitstringtoquerycolumn(@l_TColumn, ',') 
   SET @l_HHeader=dbo.Fn_splitstringtohtmlheader(@l_TColumn, ',') 
   SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                  ' FROM #TAlert AS TR 
                                     FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

   EXECUTE Sp_executesql @l_TQuery, 
                         N'@Html NVARCHAR(MAX) OUTPUT', 
                         @l_Html OUTPUT 

   SET @l_EmailBody=dbo.Fn_formathtmltable(@l_Html, @l_HHeader) 

   EXECUTE dbo.Utility_emailhtmlstringtohtmltable @p_Subject=@l_EmailObject, 
                                                  @p_HtmlString=@l_EmailBody  
END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportIndexFragementInfoForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    report frgement info for all databases on a given server
-- =============================================

CREATE PROCEDURE [dbo].[rpt_ReportIndexFragementInfoForAllDatabase]
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   CREATE TABLE #FragmentedIndexes
   (   DatabaseName       NVARCHAR(200),
       SchemaName         NVARCHAR(200),
       TableName          NVARCHAR(200),
       IndexName          NVARCHAR(200),
       [Fragmentation%]   FLOAT )

   EXEC sp_MSforeachdb 'USE [?];
      INSERT INTO #FragmentedIndexes
      SELECT DB_NAME(DB_ID()) AS DatabaseName,
             sc.name AS SchemaName,
             OBJECT_NAME (s.object_id) AS TableName,
             i.name AS IndexName,
             s.avg_fragmentation_in_percent AS [Fragmentation%]
        FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL,''SAMPLED'') s
             INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
             INNER JOIN sys.objects o ON s.object_id = o.object_id
             INNER JOIN sys.schemas sc ON sc.[schema_id] = o.[schema_id]
       WHERE s.database_id = DB_ID()
             AND i.index_id != 0
             AND s.record_count > 0
             AND s.avg_fragmentation_in_percent>5
             AND o.is_ms_shipped = 0 ;'




   --Generate rebuild/reorganize index script

      SELECT 
          CASE WHEN [Fragmentation%] > 30
                  THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
                       + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                       + QUOTENAME(TableName) + ' REBUILD;'
               WHEN [Fragmentation%] > 10
                  THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
                       + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                       + QUOTENAME(TableName) + ' REORGANIZE;' END AS MaintanceScript
     INTO #TScript
     FROM #FragmentedIndexes
    WHERE [Fragmentation%] > 10


   
   --send alert report part 
   IF EXISTS(SELECT 1 FROM #FragmentedIndexes WHERE [Fragmentation%]>=10 )
   BEGIN 
      SELECT * INTO #TReport FROM  #FragmentedIndexes WHERE [Fragmentation%]>=10
      SELECT * INTO #TReport1 FROM #TScript

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 
      

      --for #TReport part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Index Fragement List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT
                                 
      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody                                      
                                                     
      --for  #TReport1 part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Index Fragement Maintance Script For All Database'  
      SET @l_EmailBody='' 


      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport1') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport1 AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT     

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  

      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody 
      DROP TABLE #TReport
      DROP TABLE #TReport1
   END
   
   DROP TABLE #FragmentedIndexes
   DROP TABLE #TScript

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportJobSummaryInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get job summary
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportJobSummaryInfo] 
   @p_JobName             VARCHAR(255) = NULL,  -- Optional job name filter  
   @p_ShowDisabled        BIT = 0, -- Include disabled jobs?  
   @p_ShowUnscheduled     BIT = 0, -- Include Unscheduled jobs?  
   @p_JobThresholdSec     INT = 0, -- If positive, show only the jobs with LAST duration above this.  
   @p_AvgExecThresholdSec INT = 0  -- If positive, show only the jobs with AVERAGE duration above this.  

AS 
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT * INTO #TJob
     FROM ( SELECT JobName, 
                   ISNULL(LastStep,'') LastStep,
                   CASE WHEN StartDate IS NOT NULL AND FinishDate IS NULL THEN 'Running' 
                        WHEN Enabled = 0 THEN 'Disabled' 
                        WHEN StepCount = 0 THEN 'No steps' 
                        WHEN RunStatus IS NOT NULL THEN RunStatus 
                        WHEN ScheduleCount = 0 THEN 'Not scheduled' 
                        ELSE 'UNKNOWN' END Info,
                   DatabaseName, 
                   Enabled, 
                   ScheduleCount, 
                   StepCount, 
                   StartDate, 
                   FinishDate, 
                   DurationSec, 
                   RIGHT('0'+convert(varchar(5),DurationSec/3600),2)+':'+
                   RIGHT('0'+convert(varchar(5),DurationSec%3600/60),2)+':'+ 
                   RIGHT('0'+convert(varchar(5),(DurationSec%60)),2) DurationSecFormatted, 
                   avgDurationSec,
                   RIGHT('0'+convert(varchar(5),avgDurationSec/3600),2)+':'+
                   RIGHT('0'+convert(varchar(5),avgDurationSec%3600/60),2)+':'+ 
                   RIGHT('0'+convert(varchar(5),(avgDurationSec%60)),2) avgDurationSecFormatted, 
                   CASE WHEN (DurationSec IS NULL OR ISNULL(avgDurationSec, 0) = 0) THEN 0 
                        ELSE CONVERT(DECIMAL(18,2), (100*CAST(DurationSec AS DECIMAL)) / CAST (avgDurationSec as DECIMAL)) END AS DurationRatio, 
                   NextRunDate, 
                   StepCommand, 
                   HistoryMessage 
              FROM ( SELECT j.name JobName,
                            j.enabled Enabled, 
                            (SELECT COUNT(1) FROM msdb..sysjobschedules jss WHERE jss.job_id = j.job_id) ScheduleCount, 
                            (SELECT COUNT(1) FROM msdb..sysjobsteps jps WHERE jps.job_id = j.job_id) StepCount, 
                            ls1.job_history_id HistoryID, 
                            ls1.start_execution_date StartDate, 
                            ls1.stop_execution_date FinishDate, 
                            ls1.last_executed_step_id LastStepID, 
                            DATEDIFF(SECOND, 
                                     ls1.start_execution_date, 
                                     CASE WHEN ls1.stop_execution_date IS NULL THEN GETDATE() 
                                     ELSE ls1.stop_execution_date END) DurationSec, 
                            ISNULL(avgSec, 0) avgDurationSec, 
                            ls1.next_scheduled_run_date NextRunDate, 
                            st.step_name LastStep, 
                            st.command StepCommand, 
                            st.database_name DatabaseName, 
                            h.message HistoryMessage, 
                            CASE WHEN h.job_id IS NULL THEN 'Never Run' 
                            ELSE CASE h.run_status WHEN 0 THEN 'Failed' 
                                                   WHEN 1 THEN 'Succeeded' 
                                                   WHEN 2 THEN 'Retry' 
                                                   WHEN 3 THEN 'Canceled' END END RunStatus, 
                            h.run_date rawRunDate, 
                            h.run_time rawRunTime, 
                            h.run_duration rawRunDuration 
                       FROM msdb..sysjobactivity ls1 (NOLOCK) 
                            INNER JOIN msdb..sysjobs j (NOLOCK) ON ls1.job_id = j.job_id 
                            INNER JOIN (SELECT job_id JobID, 
                                               MAX(session_id) LastSessionID 
                                          FROM msdb..sysjobactivity (NOLOCK) 
                                      GROUP BY job_id ) ls2 ON ls1.job_id = ls2.JobID 
                                                               AND ls1.session_id = ls2.LastSessionID 
                            LEFT OUTER JOIN msdb..sysjobsteps st (NOLOCK) ON st.job_id = j.job_id 
                                                                     AND ls1.last_executed_step_id = st.step_id 
                            LEFT OUTER JOIN msdb..sysjobhistory h (NOLOCK) ON h.instance_id = ls1.job_history_id 
                            LEFT OUTER JOIN ( SELECT j.job_id JobID, 
                                                     SUM(h.avgSecs) avgSec 
                                                FROM msdb..sysjobs j (NOLOCK) 
                                                     INNER JOIN ( SELECT job_id, 
                                                                         step_id, 
                                                                         AVG(run_duration/10000*3600 + 
                                                                             run_duration%10000/100*60 + 
                                                                             run_duration%100) avgSecs 
                                                                    FROM msdb..sysjobhistory 
                                                                   WHERE step_id > 0 
                                                                 AND run_status = 1 
                                                               GROUP BY job_id,
                                                                        step_id ) h ON j.job_id = h.job_id 
                                             GROUP BY j.job_id ) jobavg ON jobavg.JobID = j.job_id )jj 
             WHERE (@p_ShowDisabled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR Enabled = 1) 
                   AND (@p_JobName IS NULL OR JobName = @p_JobName) 
                   AND (@p_ShowUnscheduled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR ScheduleCount > 0) 
                   AND (@p_JobThresholdSec = 0 OR DurationSec >= @p_JobThresholdSec) 
                   AND (@p_AvgExecThresholdSec = 0 OR avgDurationSec >= @p_AvgExecThresholdSec))x 
 ORDER BY CASE Info 
          WHEN 'Running' THEN 0 
          WHEN 'Failed' THEN 1 
          WHEN 'Retry' THEN 2
          WHEN 'Succeeded' THEN 3
          WHEN 'Canceled' THEN 4
          WHEN 'No steps' THEN 5 
          WHEN 'Not scheduled' THEN 6
          WHEN 'Disabled' THEN 7
          WHEN 'Never Run' THEN 8
          WHEN 'UNKNOWN' THEN -1
          ELSE -2 END, 
          NextRunDate, 
          JobName



   --send alert report part 
   IF EXISTS(SELECT 1 FROM #TJob )
   BEGIN 
      SELECT * INTO #TReport FROM #TJob 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Job Summary' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
   END

   DROP TABLE #TJob
END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopLongestBlockedQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report the queries spend the longest time being blocked for all database
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportTopLongestBlockedQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT TOP 20
          CAST((qs.total_elapsed_time - qs.total_worker_time) /1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)],
          CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],
          CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],
          qs.execution_count,
          CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)],
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                      ELSE qs.statement_end_offset
                                                                  END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName
          --qp.query_plan
     INTO #TResult
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
 ORDER BY [Total time blocked (s)] DESC
 
 
 
  --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top Longest Blocked Query List' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult
END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopLongestTimeQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    The queries that take the longest time to run for all database
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportTopLongestTimeQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT TOP 20
          CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))AS [Total Duration (s)],
          CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2))AS [% CPU],
          CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],
          qs.execution_count,
          CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2))AS [Average Duration (s)],
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName
          --qp.query_plan
     INTO #TResult
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
 ORDER BY qs.total_elapsed_time DESC
 
 
 
  --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top Longest Time Cost Query List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult
END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopMissingIndexForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report the top missing index for all database
-- =============================================

CREATE PROCEDURE [dbo].[rpt_ReportTopMissingIndexForAllDatabase]
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SELECT TOP 20
          'CREATE NONCLUSTERED INDEX '+ 
          QUOTENAME('IX_AutoGenerated_'+ 
          REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ' ', '_'), ':', '_')+ 
          '_' + CAST(d.index_handle AS VARCHAR(22)))+ ' ON ' + d.[statement] +
          '('+ CASE
               WHEN d.equality_columns IS NULL THEN d.inequality_columns
               WHEN d.inequality_columns IS NULL THEN d.equality_columns
               ELSE d.equality_columns + ',' + d.inequality_columns END + ')'+ 
               CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE ( ' + d.included_columns + ')'
               ELSE '' END AS MissingIndexSQL,
           ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Cost],
           d.[statement] AS [Table Name],
           d.equality_columns,
           d.inequality_columns,
           d.included_columns
       INTO #MissingIndexes
       FROM sys.dm_db_missing_index_groups g
            INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
   ORDER BY [Total Cost] DESC


   SELECT  MissingIndexSQL AS  MaintanceScript INTO #TScript
     FROM #MissingIndexes

  
   --send alert report part 
   IF EXISTS(SELECT 1 FROM #MissingIndexes)
   BEGIN 
  
      SELECT * INTO #TAlert FROM #MissingIndexes 
      SELECT * INTO #TAlert1 FROM #TScript

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 
      

      --for #TAlert part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top Missing Index List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT
                                 
      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody                                      
                                                     
      --for  #TAlert1 part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top Missing Index Maintance Script For All Database' 
      SET @l_EmailBody='' 


      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert1') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert1 AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT     

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  

      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody 
      DROP TABLE #TAlert
      DROP TABLE #TAlert1

   END
   
   DROP TABLE #MissingIndexes
   DROP TABLE #TScript

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopMostCPUCostQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report the queries that use the most CPU for all database
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportTopMostCPUCostQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED



   SELECT TOP 20
          CAST((qs.total_worker_time) / 1000000.0AS DECIMAL(28,2)) AS [Total CPU time (s)],
          CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],
          CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],
          qs.execution_count,
          CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)],
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName
          --qp.query_plan
     INTO #TResult
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
 ORDER BY [Total CPU time (s)] DESC
 
 
 --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top More CUP Cost Query List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopMostIOCostQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report the queries that use the most I/O
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportTopMostIOCostQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT TOP 20
          [Total IO] = (qs.total_logical_reads + qs.total_logical_writes),
          [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count,
          qs.execution_count,
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName
          --qp.query_plan
      INTO #TResult
      FROM sys.dm_exec_query_stats qs
           CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
           CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
   ORDER BY [Total IO] DESC
   
   
  --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top More IO Cost Query List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopMostOftenExecutedQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report the queries that have been executed the most often
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportTopMostOftenExecutedQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT TOP 20
          qs.execution_count,
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName
          --qp.query_plan
     INTO #TResult
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 ORDER BY qs.execution_count DESC;
 
 
 --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top More Most Often Executed Query List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTopMostRecompiledQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report the most-recompiled queries
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportTopMostRecompiledQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT TOP 20
          qs.plan_generation_num,
          qs.total_elapsed_time,
          qs.execution_count,
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DBName,
          qs.creation_time,
          qs.last_execution_time
     INTO #TResult
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
 ORDER BY plan_generation_num DESC
 
 
 --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Top More Most Recompiled Query List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportUnusedIndexForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Report unused indexes for all database
-- =============================================

CREATE PROCEDURE [dbo].[rpt_ReportUnusedIndexForAllDatabase]
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET NOCOUNT ON

   SELECT DB_NAME() AS DatabaseName,
          SCHEMA_NAME(o.Schema_ID) AS SchemaName,
          OBJECT_NAME(s.[object_id]) AS TableName,
          i.name AS IndexName,
          s.user_updates,
          s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
     INTO #TempUnusedIndexes
     FROM sys.dm_db_index_usage_stats s
          INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
          INNER JOIN sys.objects o ON i.object_id = o.object_id
    WHERE 1=2

   EXEC sp_MSforeachdb 'USE [?];
   INSERT INTO #TempUnusedIndexes
   SELECT TOP 20
          DB_NAME() AS DatabaseName,
          SCHEMA_NAME(o.Schema_ID) AS SchemaName,
          OBJECT_NAME(s.[object_id]) AS TableName,
          i.name AS IndexName,
          s.user_updates,
          s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
     FROM sys.dm_db_index_usage_stats s
          INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
          INNER JOIN sys.objects o ON i.object_id = o.object_id
    WHERE s.database_id = DB_ID()
          AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
          AND user_seeks = 0
          AND user_scans = 0
          AND user_lookups = 0
          AND i.name IS NOT NULL
 ORDER BY user_updates DESC'

   DECLARE @DisableOrDrop INT
   DECLARE @DisableIndexesSQL NVARCHAR(MAX)
   SET @DisableOrDrop = 1
   SET @DisableIndexesSQL = ''

   SELECT CASE
             WHEN @DisableOrDrop = 1
               THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
                    + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                    + QUOTENAME(TableName) + ' DISABLE;'
               ELSE CHAR(10) + 'DROP INDEX ' + QUOTENAME(IndexName) + ' ON '
                    + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                    + QUOTENAME(TableName) 
          END AS MaintanceScript INTO #TScript
     FROM #TempUnusedIndexes

   
   --send alert report part 
   IF EXISTS(SELECT 1 FROM #TempUnusedIndexes )
   BEGIN 
      SELECT * INTO #TReport FROM  #TempUnusedIndexes 
      SELECT * INTO #TReport1 FROM #TScript

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 
      

      --for #TReport part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Unused Index List For All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT
                                 
      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody                                      
                                                     
      --for  #TReport1 part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Unused Index Maintance Script For All Database'  
      SET @l_EmailBody='' 


      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport1') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport1 AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT     

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  

      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody 
      DROP TABLE #TReport
      DROP TABLE #TReport1
   END
   
   DROP TABLE #TempUnusedIndexes
   DROP TABLE #TScript

END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportWeeklyBackupEvaluationForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get backup evaluated info for all database
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportWeeklyBackupEvaluationForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT a.server_name AS 'Server',
          a.database_name AS 'Database',
          CONVERT(VARCHAR(25), a.backup_start_date,100) AS 'Start Date',
          CONVERT(VARCHAR(25), a.backup_finish_date,100) AS 'Finish Date',
          DATENAME(WEEKDAY, a.backup_finish_date) AS 'Day' ,
          DATEDIFF(MILLISECOND , a.backup_start_date, a.backup_finish_date) AS 'MSec' ,
          DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) AS 'Sec' ,
          DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) AS 'Mins' ,
          CAST(CAST(DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) AS DECIMAL(8,3))/60 AS DECIMAL(8,1)) AS 'Hours',
          CASE WHEN DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) > 0
               THEN CAST(CEILING(a.backup_size /1048576) / DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) AS DECIMAL(8,1))
          ELSE 0
          END AS 'Meg/Min',
          CEILING(a.backup_size /1048576) AS 'Size Meg',
          CAST((a.backup_size /1073741824) AS DECIMAL(9,2)) AS 'Gig',
          a.user_name,a.backup_size AS 'Raw Size'
     INTO #TResult
     FROM msdb.dbo.backupset a
          JOIN msdb.dbo.backupset b ON a.server_name = b.server_name AND a.database_name = b.database_name
    WHERE a.type = 'D' 
          AND b.type = 'D'
          AND DATEDIFF(WEEKDAY,a.backup_start_date,GETDATE())<=7
 GROUP BY a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
 ORDER BY a.server_name DESC , a.database_name,a.backup_start_date DESC
 
 
 
 --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report The Lastest Week Backup Evaluation' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult

END



GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportWeeklyDiskSpaceTrend]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportTheLatestWeekEventLog]    Script Date: 2012/9/6 17:29:49 ******/

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    auto report daily disk space trend
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportWeeklyDiskSpaceTrend] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_BeginDate  SMALLDATETIME
   DECLARE @l_EndDate    SMALLDATETIME
   DECLARE @l_DateNow    SMALLDATETIME
   
   SET @l_DateNow = CONVERT(VARCHAR(10), GETDATE(), 120)
   SET @l_BeginDate = DATEADD(d, - DATEPART(dw, @l_DateNow) - 7, @l_DateNow)
   SET @l_EndDate   = DATEADD(d, - DATEPART(dw, @l_DateNow), @l_DateNow)

   
   IF EXISTS ( SELECT 1
                 FROM dbo.AvailableDiskSpace
                WHERE LastUpdate >= @l_BeginDate
                  AND LastUpdate < @l_EndDate )
   BEGIN
      SELECT *
        INTO #TResult
        FROM dbo.AvailableDiskSpace
       WHERE LastUpdate >= @l_BeginDate
             AND LastUpdate < @l_EndDate 
      ORDER BY DriveLetter,LastUpdate DESC
   END
   ELSE RETURN 0
      
 
  --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Daily Disk Space Trend' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult
END

GO
/****** Object:  StoredProcedure [dbo].[rpt_ReportWeeklyEventLog]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    auto report event logs
-- =============================================
CREATE PROCEDURE [dbo].[rpt_ReportWeeklyEventLog] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_BeginDate  SMALLDATETIME
   DECLARE @l_EndDate    SMALLDATETIME
   DECLARE @l_DateNow    SMALLDATETIME
   
   SET @l_DateNow = CONVERT(VARCHAR(10), GETDATE(), 120)
   SET @l_BeginDate = DATEADD(d, - DATEPART(dw, @l_DateNow) - 7, @l_DateNow)
   SET @l_EndDate   = DATEADD(d, - DATEPART(dw, @l_DateNow), @l_DateNow)

   
   IF EXISTS ( SELECT 1
                 FROM dbo.EventLog
                WHERE EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') >= @l_BeginDate
                  AND EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') < @l_EndDate )
   BEGIN
      SELECT EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)') AS DatabaseName,
             EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)') AS EventType,
             EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)') AS UserName,
             EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)') AS LoginName,
             EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS PostTime,
             EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText
        INTO #TResult
        FROM dbo.EventLog
       WHERE EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') >= @l_BeginDate
             AND EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') < @l_EndDate 
   END
   ELSE RETURN 0
      
 
  --send alert report part 
   IF EXISTS(SELECT 1 FROM #TResult )
   BEGIN 
      SELECT * INTO #TReport FROM #TResult 

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Auto Report Latest Event Log' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TReport') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TReport AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TReport
  END
  
  DROP TABLE #TResult
END

GO
/****** Object:  StoredProcedure [dbo].[utility_AUDIT_CheckDefaultStatisticsOptionForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Check statistics option default value
-- =============================================
CREATE PROCEDURE [dbo].[utility_AUDIT_CheckDefaultStatisticsOptionForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT name AS DatabaseName,
          is_auto_create_stats_on AS AutoCreateStatistics,
          is_auto_update_stats_on AS AutoUpdateStatistics,
          is_auto_update_stats_async_on    AS AutoUpdateStatisticsAsync
     FROM sys.databases
 ORDER BY DatabaseName

END

GO
/****** Object:  StoredProcedure [dbo].[utility_AUDIT_CheckDisparateColumWithDifferentDataTypeForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Find disparate columns with different data types
-- =============================================
CREATE PROCEDURE [dbo].[utility_AUDIT_CheckDisparateColumWithDifferentDataTypeForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT DISTINCT
          DB_NAME() AS DBName,
          C1.COLUMN_NAME,
          C1.TABLE_SCHEMA,
          C1.TABLE_NAME,
          C1.DATA_TYPE,
          C1.CHARACTER_MAXIMUM_LENGTH,
          C1.NUMERIC_PRECISION,
          C1.NUMERIC_SCALE,
          0 AS [%] 
          INTO #TResult
     FROM INFORMATION_SCHEMA.COLUMNS C1
          INNER JOIN INFORMATION_SCHEMA.COLUMNS C2 ON C1.COLUMN_NAME = C2.COLUMN_NAME
    WHERE 1=2
    
          
   EXEC sp_MSforeachdb 'USE [?];
      SELECT COLUMN_NAME,
             [%] = CONVERT(DECIMAL(12,2),COUNT(COLUMN_NAME)*100.0 / COUNT(*)OVER())
        INTO #Prevalence
        FROM INFORMATION_SCHEMA.COLUMNS
    GROUP BY COLUMN_NAME
 
      INSERT INTO #TResult
      SELECT DISTINCT
             DB_NAME() AS DBName,
             C1.COLUMN_NAME,
             C1.TABLE_SCHEMA,
             C1.TABLE_NAME,
             C1.DATA_TYPE,
             C1.CHARACTER_MAXIMUM_LENGTH,
             C1.NUMERIC_PRECISION,
             C1.NUMERIC_SCALE,
             [%]
        FROM INFORMATION_SCHEMA.COLUMNS C1
             INNER JOIN INFORMATION_SCHEMA.COLUMNS C2 ON C1.COLUMN_NAME = C2.COLUMN_NAME
             INNER JOIN #Prevalence p ON p.COLUMN_NAME = C1.COLUMN_NAME
       WHERE ((C1.DATA_TYPE != C2.DATA_TYPE)
              OR (C1.CHARACTER_MAXIMUM_LENGTH != C2.CHARACTER_MAXIMUM_LENGTH)
              OR (C1.NUMERIC_PRECISION != C2.NUMERIC_PRECISION)
              OR (C1.NUMERIC_SCALE != C2.NUMERIC_SCALE))
    ORDER BY [%] DESC, C1.COLUMN_NAME, C1.TABLE_SCHEMA, C1.TABLE_NAME ;'
 
   SELECT * FROM #TResult
   DROP TABLE #TResult
   
END



GO
/****** Object:  StoredProcedure [dbo].[utility_AUDIT_CheckIdentityColumnInfoForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Check identity column info for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_AUDIT_CheckIdentityColumnInfoForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   
   SELECT DB_NAME() AS DBName,
          QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName, 
          c.name AS ColumnName,
          CASE c.system_type_id
             WHEN 127 THEN 'bigint'
             WHEN 56 THEN 'int'
             WHEN 52 THEN 'smallint'
             WHEN 48 THEN 'tinyint'
          END AS DataType,
          IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
          CASE c.system_type_id
             WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 9223372036854775807
             WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 2147483647
             WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 32767
             WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) * 100.) / 255
          END AS PercentageUsed 
     INTO #TResult
     FROM sys.columns AS c 
          INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
    WHERE 1=2
    
    
      EXEC sp_MSforeachdb 'USE [?];
      INSERT INTO #TResult
         SELECT DB_NAME() AS DBName,
                QUOTENAME(SCHEMA_NAME(t.schema_id)) + ''.'' +  QUOTENAME(t.name) AS TableName, 
                c.name AS ColumnName,
                CASE c.system_type_id
                   WHEN 127 THEN ''bigint''
                   WHEN 56 THEN ''int''
                   WHEN 52 THEN ''smallint''
                   WHEN 48 THEN ''tinyint''
                END AS DataType,
                IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + ''.'' + t.name) AS CurrentIdentityValue,
                CASE c.system_type_id
                   WHEN 127 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + ''.'' + t.name) * 100.) / 9223372036854775807
                   WHEN 56 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + ''.'' + t.name) * 100.) / 2147483647
                   WHEN 52 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + ''.'' + t.name) * 100.) / 32767
                   WHEN 48 THEN (IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + ''.'' + t.name) * 100.) / 255
                END AS PercentageUsed 
           FROM sys.columns AS c 
                INNER JOIN sys.tables AS t ON t.[object_id] = c.[object_id]
          WHERE c.is_identity = 1
       ORDER BY PercentageUsed DESC;'

   SELECT * FROM #TResult ORDER BY DBName
   DROP TABLE #TResult

END

GO
/****** Object:  StoredProcedure [dbo].[utility_AUDIT_CheckTableWithoutClusteredIndexForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Find the tables without clustered index for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_AUDIT_CheckTableWithoutClusteredIndexForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT DB_NAME() AS DBName,
          o.name
     INTO #TResult
     FROM sys.objects o
    WHERE 1=2
                                                                              
   EXEC sp_MSforeachdb 'USE [?];
      INSERT INTO #TResult
      SELECT DB_NAME() AS DBName,
             o.name
        FROM sys.objects o
       WHERE o.type=''U''
          AND NOT EXISTS(SELECT 1 
                           FROM sys.indexes i
                          WHERE o.object_id = i.object_id
                                AND i.type_desc = ''CLUSTERED'');'
                                
          
   SELECT * FROM #TResult
   DROP TABLE #TResult
                                
                             

END

GO
/****** Object:  StoredProcedure [dbo].[utility_BACKUP_GetWeeklyBackupEvaluatedInfoForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get backup evaluated info for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_BACKUP_GetWeeklyBackupEvaluatedInfoForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT a.server_name AS 'Server',
          a.database_name AS 'Database',
          CONVERT(VARCHAR(25), a.backup_start_date,100) AS 'Start Date',
          CONVERT(VARCHAR(25), a.backup_finish_date,100) AS 'Finish Date',
          DATENAME(WEEKDAY, a.backup_finish_date) AS 'Day' ,
          DATEDIFF(MILLISECOND , a.backup_start_date, a.backup_finish_date) AS 'MSec' ,
          DATEDIFF(SECOND, a.backup_start_date, a.backup_finish_date) AS 'Sec' ,
          DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) AS 'Mins' ,
          CAST(CAST(DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) AS DECIMAL(8,3))/60 AS DECIMAL(8,1)) AS 'Hours',
          CASE WHEN DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) > 0
               THEN CAST(CEILING(a.backup_size /1048576) / DATEDIFF(MINUTE, a.backup_start_date, a.backup_finish_date) AS DECIMAL(8,1))
          ELSE 0
          END AS 'Meg/Min',
          CEILING(a.backup_size /1048576) AS 'Size Meg',
          CAST((a.backup_size /1073741824) AS DECIMAL(9,2)) AS 'Gig',
          a.user_name,a.backup_size AS 'Raw Size'
     FROM msdb.dbo.backupset a
          JOIN msdb.dbo.backupset b ON a.server_name = b.server_name AND a.database_name = b.database_name
    WHERE a.type = 'D' 
          AND b.type = 'D'
          AND DATEDIFF(WEEKDAY,a.backup_start_date,GETDATE())<=7
 GROUP BY a.server_name, a.database_name, a.backup_start_date, a.backup_finish_date, a.backup_size, a.user_name
 ORDER BY a.server_name DESC , a.database_name,a.backup_start_date DESC

END



GO
/****** Object:  StoredProcedure [dbo].[utility_BASIC_GetCPUUsageBasicInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get CPU usage basic info 
-- =============================================
CREATE PROCEDURE [dbo].[utility_BASIC_GetCPUUsageBasicInfo] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @ts_now BIGINT

   SELECT @ts_now = cpu_ticks / CONVERT(FLOAT, cpu_ticks_in_ms)
     FROM sys.dm_os_sys_info
    
    SELECT RecordId,
           DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime, 
           SQLProcessUtilization,
           SystemIdle,
           100 - SystemIdle - SQLProcessUtilization AS OtherProcessUtilization
      FROM ( SELECT timestamp,
                    record.value('(./Record/@id)[1]', 'int') AS RecordId,
                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS SystemIdle,
                    record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS SQLProcessUtilization
               FROM (SELECT timestamp, 
                            CONVERT(XML, record) AS record 
               FROM sys.dm_os_ring_buffers 
              WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
                    AND record LIKE '% %') AS x
           ) AS y 
  ORDER BY RecordId DESC


END

GO
/****** Object:  StoredProcedure [dbo].[utility_BASIC_GetDatabaseBasicInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get database basic info 
-- =============================================
CREATE PROCEDURE [dbo].[utility_BASIC_GetDatabaseBasicInfo] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   IF EXISTS ( SELECT *
                 FROM tempdb.dbo.sysobjects
                WHERE id = OBJECT_ID(N'[tempdb].[dbo].[HoldforEachDB]') ) 
    DROP TABLE [tempdb].[dbo].[HoldforEachDB] 
     
    CREATE TABLE [tempdb].[dbo].[HoldforEachDB]
    ( [Server] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS  NULL,
      [DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Size] [int] NOT NULL,
      [File_Status] [int] NULL,
      [Name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Filename] [nvarchar](260) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [Status] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Updateability] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [User_Access] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Recovery] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON  [PRIMARY]


    INSERT INTO [tempdb].[dbo].[HoldforEachDB]
       EXEC sp_MSforeachdb 'SELECT CONVERT(char(100), SERVERPROPERTY(''Servername'')) AS Server,
                                   ''?'' AS DatabaseName,
                                   [?]..sysfiles.size, 
                                   [?]..sysfiles.status, 
                                   [?]..sysfiles.name, 
                                   [?]..sysfiles.filename,
                                   CONVERT(sysname,DatabasePropertyEx(''?'',''Status'')) AS Status,
                                   CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability'')) AS Updateability,
                                   CONVERT(sysname,DatabasePropertyEx(''?'',''UserAccess'')) AS User_Access,
                                   CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery'')) AS Recovery From [?]..sysfiles'


   SELECT * FROM [tempdb].[dbo].[HoldforEachDB] ORDER BY DatabaseName
   DROP TABLE [tempdb].[dbo].[HoldforEachDB]


END

GO
/****** Object:  StoredProcedure [dbo].[utility_BASIC_GetSecurityBasicInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get security basic info 
-- =============================================
CREATE PROCEDURE [dbo].[utility_BASIC_GetSecurityBasicInfo] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   IF EXISTS ( SELECT *
                 FROM tempdb.dbo.sysobjects
                WHERE id = OBJECT_ID(N'[tempdb].[dbo].[SQL_DB_REP]') ) 
    DROP TABLE [tempdb].[dbo].[SQL_DB_REP] ; 


   CREATE TABLE [tempdb].[dbo].[SQL_DB_REP]
    ( [Server] [varchar](100) NOT NULL,
      [DB_Name] [varchar](70) NOT NULL,
      [User_Name] [nvarchar](90) NULL,
      [Group_Name] [varchar](100) NULL,
      [Account_Type] [varchar](22) NULL,
      [Login_Name] [varchar](80) NULL,
      [Def_DB] [varchar](100) NULL
    ) ON  [PRIMARY]

   INSERT INTO [tempdb].[dbo].[SQL_DB_REP]
        EXEC sp_MSforeachdb 'SELECT CONVERT(varchar(100), SERVERPROPERTY(''Servername'')) AS Server, 
                                    ''?'' AS DB_Name,
                                    usu.name u_name,
                                    CASE WHEN (usg.uid is null) THEN ''public''
                                    ELSE usg.name END as Group_Name,
                                    CASE WHEN usu.isntuser=1 THEN ''Windows Domain Account'' 
                                         WHEN usu.isntgroup = 1 THEN ''Windows Group'' 
                                         WHEN usu.issqluser = 1 THEN''SQL Account'' 
                                         WHEN usu.issqlrole = 1 THEN ''SQL Role'' END as Account_Type,
                                    lo.loginname,
                                    lo.dbname AS Def_DB
                               FROM [?]..sysusers usu 
                                     LEFT OUTER JOIN([?]..sysmembers mem 
                                     INNER JOIN [?]..sysusers usg ON mem.groupuid = usg.uid) ON usu.uid = mem.memberuid
                                     LEFT OUTER JOIN master.dbo.syslogins  lo ON usu.sid = lo.sid
                               WHERE ( usu.islogin = 1 
                                       AND usu.isaliased = 0 
                                       AND usu.hasdbaccess = 1) 
                                      AND (usg.issqlrole = 1 
                                           OR usg.uid is null)'

   SELECT * FROM [tempdb].[dbo].[SQL_DB_REP] ORDER BY [DB_Name]
   DROP TABLE [tempdb].[dbo].[SQL_DB_REP]


END

GO
/****** Object:  StoredProcedure [dbo].[utility_BASIC_GetServerBasicInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get database server basic info 
-- =============================================
CREATE PROCEDURE [dbo].[utility_BASIC_GetServerBasicInfo] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT  CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
           CONVERT(CHAR(100), SERVERPROPERTY('ProductVersion')) AS ProductVersion,
           CONVERT(CHAR(100), SERVERPROPERTY('ProductLevel')) AS ProductLevel,
           CONVERT(CHAR(100), SERVERPROPERTY('ResourceLastUpdateDateTime')) AS ResourceLastUpdateDateTime,
           CONVERT(CHAR(100), SERVERPROPERTY('ResourceVersion')) AS ResourceVersion,
           CASE WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 1
                THEN 'Integrated security'
                WHEN SERVERPROPERTY('IsIntegratedSecurityOnly') = 0
                THEN 'Not Integrated security'
           END AS IsIntegratedSecurityOnly,
           CASE WHEN SERVERPROPERTY('EngineEdition') = 1 THEN 'Personal Edition'
                WHEN SERVERPROPERTY('EngineEdition') = 2 THEN 'Standard Edition'
                WHEN SERVERPROPERTY('EngineEdition') = 3
                THEN 'Enterprise Edition'
                WHEN SERVERPROPERTY('EngineEdition') = 4 THEN 'Express Edition'
           END AS EngineEdition,
           CONVERT(CHAR(100), SERVERPROPERTY('InstanceName')) AS InstanceName,
           CONVERT(CHAR(100), SERVERPROPERTY('ComputerNamePhysicalNetBIOS')) AS ComputerNamePhysicalNetBIOS,
           CONVERT(CHAR(100), SERVERPROPERTY('LicenseType')) AS LicenseType,
           CONVERT(CHAR(100), SERVERPROPERTY('NumLicenses')) AS NumLicenses,
           CONVERT(CHAR(100), SERVERPROPERTY('BuildClrVersion')) AS BuildClrVersion,
           CONVERT(CHAR(100), SERVERPROPERTY('Collation')) AS Collation,
           CONVERT(CHAR(100), SERVERPROPERTY('CollationID')) AS CollationID,
           CONVERT(CHAR(100), SERVERPROPERTY('ComparisonStyle')) AS ComparisonStyle,
           CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('EditionID')) = -1253826760
                THEN 'Desktop Edition'
                WHEN SERVERPROPERTY('EditionID') = -1592396055
                THEN 'Express Edition'
                WHEN SERVERPROPERTY('EditionID') = -1534726760
                THEN 'Standard Edition'
                WHEN SERVERPROPERTY('EditionID') = 1333529388
                THEN 'Workgroup Edition'
                WHEN SERVERPROPERTY('EditionID') = 1804890536
                THEN 'Enterprise Edition'
                WHEN SERVERPROPERTY('EditionID') = -323382091
                THEN 'Personal Edition'
                WHEN SERVERPROPERTY('EditionID') = -2117995310
                THEN 'Developer Edition'
                WHEN SERVERPROPERTY('EditionID') = 610778273
                THEN 'Enterprise Evaluation Edition'
                WHEN SERVERPROPERTY('EditionID') = 1044790755
                THEN 'Windows Embedded SQL'
                WHEN SERVERPROPERTY('EditionID') = 4161255391
                THEN 'Express Edition with Advanced Services'
           END AS ProductEdition,
           CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsClustered')) = 1
                THEN 'Clustered'
                WHEN SERVERPROPERTY('IsClustered') = 0 THEN 'Not Clustered'
                WHEN SERVERPROPERTY('IsClustered') = NULL THEN 'Error'
           END AS IsClustered,
           CASE WHEN CONVERT(CHAR(100), SERVERPROPERTY('IsFullTextInstalled')) = 1
                THEN 'Full-text is installed'
                WHEN SERVERPROPERTY('IsFullTextInstalled') = 0
                THEN 'Full-text is not installed'
                WHEN SERVERPROPERTY('IsFullTextInstalled') = NULL THEN 'Error'
           END AS IsFullTextInstalled,
           CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSet')) AS SqlCharSet,
           CONVERT(CHAR(100), SERVERPROPERTY('SqlCharSetName')) AS SqlCharSetName,
           CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrder')) AS SqlSortOrderID,
           CONVERT(CHAR(100), SERVERPROPERTY('SqlSortOrderName')) AS SqlSortOrderName
  ORDER BY CONVERT(CHAR(100), SERVERPROPERTY('Servername'))  


END

GO
/****** Object:  StoredProcedure [dbo].[utility_BASIC_ServiceCredentialBasicInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get service credential basic info 
-- =============================================
CREATE PROCEDURE [dbo].[utility_BASIC_ServiceCredentialBasicInfo] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   IF @@microsoftversion / power(2, 24) >= 9
   BEGIN
      EXEC sp_configure 'show advanced options', 1
      RECONFIGURE WITH OVERRIDE
      EXEC sp_configure 'xp_cmdshell', 1
      RECONFIGURE WITH OVERRIDE
   END

   IF EXISTS ( SELECT Name
                 FROM tempdb..sysobjects
                WHERE name LIKE '#MyTempTable%') 

   DROP TABLE #MyTempTable

   CREATE TABLE #MyTempTable
   ( 
      Big_String nvarchar(500)
   )

   INSERT INTO #MyTempTable
      EXEC master..xp_cmdshell 'WMIC SERVICE GET Name,StartName | findstr /I SQL'

   -- show service accounts

   SELECT @@ServerName AS ServerName,
          RTRIM(Left(Big_String, CHARINDEX('     ', Big_String))) AS ServiceName,
          RTRIM(LTRIM(RTRIM(SUBSTRING(Big_String, CHARINDEX('     ', Big_String),LEN(Big_String))))) AS ServiceAccount
     FROM #MyTempTable

   IF @@microsoftversion / power(2, 24) >= 9
   BEGIN
      EXEC sp_configure 'xp_cmdshell', 0
      RECONFIGURE WITH OVERRIDE

      EXEC sp_configure 'show advanced options', 0
      RECONFIGURE WITH OVERRIDE
   END

END

GO
/****** Object:  StoredProcedure [dbo].[utility_BLOCK_GetLockingInfoForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get locking information for all active processes, that is
                --processes that have a running request, is holding locks or have an
                --open transaction. Information about all locked objects are included,
                --as well the last command sent from the client and the currently
                --running statement. The procedure also displays the blocking chain
                --for blocked processes.
-- =============================================
-- And here comes the procedure itself!
CREATE PROCEDURE [dbo].[utility_BLOCK_GetLockingInfoForAllDatabase] @allprocesses bit     = 0,
                              @textmode     bit     = 0,
                              @procdata     char(1) = NULL,
                              @debug        bit     = 0 AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- This table holds the information in sys.dm_tran_locks, aggregated
-- on a number of items. Note that we do not include subthreads or
-- requests in the aggregation. The IDENTITY column is there, because
-- we don't want character data in the clustered index.
DECLARE @locks TABLE (
   database_id     int      NOT NULL,
   entity_id       bigint   NULL,
   session_id      int      NOT NULL,
   req_mode        varchar(60)   COLLATE Latin1_General_BIN2 NOT NULL,
   rsc_type        varchar(60)   COLLATE Latin1_General_BIN2 NOT NULL,
   rsc_subtype     varchar(60)   COLLATE Latin1_General_BIN2 NOT NULL,
   req_status      varchar(60)   COLLATE Latin1_General_BIN2 NOT NULL,
   req_owner_type  varchar(60)   COLLATE Latin1_General_BIN2 NOT NULL,
   rsc_description nvarchar(256) COLLATE Latin1_General_BIN2 NULL,
   min_entity_id   bigint   NULL,
   ismultipletemp  bit      NOT NULL DEFAULT 0,
   cnt             int      NOT NULL,
   activelock AS CASE WHEN rsc_type = 'DATABASE' AND
                           req_status = 'GRANT'
                      THEN convert(bit, 0)
                      ELSE convert(bit, 1)
                 END,
   ident          int IDENTITY,
   rowno          int NULL     -- Set per session_id if @procdata is F.
   UNIQUE CLUSTERED (database_id, entity_id, session_id, ident)
)

-- This table holds the translation of entity_id in @locks. This is a
-- temp table since we access it from dynamic SQL. The type_desc is used
-- for allocation units. The columns session_id, min_id and cnt are used
-- when consolidating temp tables.
CREATE TABLE #objects (
     idtype         char(4)       NOT NULL
        CHECK (idtype IN ('OBJ', 'HOBT', 'AU', 'MISC')),
     database_id    int           NOT NULL,
     entity_id      bigint        NOT NULL,
     hobt_id        bigint        NULL,
     object_name    nvarchar(550) COLLATE Latin1_General_BIN2 NULL,
     type_desc      varchar(60)   COLLATE Latin1_General_BIN2 NULL,
     session_id     smallint      NULL,
     min_id         bigint        NOT NULL,
     cnt            int           NOT NULL DEFAULT 1
     PRIMARY KEY CLUSTERED (database_id, idtype, entity_id),
     UNIQUE NONCLUSTERED (database_id, entity_id, idtype),
     CHECK (NOT (session_id IS NOT NULL AND database_id <> 2))
)

-- This table captures sys.dm_os_waiting_tasks and later augment it with
-- data about the block chain. A waiting task always has a always has a
-- task address, but the blocker may be idle and without a task.
-- All columns for the blocker are nullable, as we add extra rows for
-- non-waiting blockers.
DECLARE @dm_os_waiting_tasks TABLE
   (wait_session_id   smallint     NOT NULL,
    wait_task         varbinary(8) NOT NULL,
    block_session_id  smallint     NULL,
    block_task        varbinary(8) NULL,
    wait_type         varchar(60) COLLATE Latin1_General_BIN2  NULL,
    wait_duration_ms  bigint       NULL,
    -- The level in the chain. Level 0 is the lead blocker. NULL for
    -- tasks that are waiting, but not blocking.
    block_level       smallint     NULL,
    -- The lead blocker for this block chain.
    lead_blocker_spid smallint     NULL,
    -- Whether the block chain consists of the threads of the same spid only.
    blocksamespidonly bit         NOT NULL DEFAULT 0,
  UNIQUE CLUSTERED (wait_session_id, wait_task, block_session_id, block_task),
  UNIQUE (block_session_id, block_task, wait_session_id, wait_task)
)

-- This table holds information about transactions tied to a session.
-- A session can have multiple transactions when there are multiple
-- requests, but in that case we only save data about the oldest
-- transaction.
DECLARE @transactions TABLE (
   session_id       smallint      NOT NULL,
   is_user_trans    bit           NOT NULL,
   trans_start      datetime      NOT NULL,
   trans_since      decimal(10,3) NULL,
   trans_type       int           NOT NULL,
   trans_state      int           NOT NULL,
   dtc_state        int           NOT NULL,
   is_bound         bit           NOT NULL,
   PRIMARY KEY (session_id)
)


-- This table holds information about all sessions and requests.
DECLARE @procs TABLE (
   session_id       smallint      NOT NULL,
   task_address     varbinary(8)  NOT NULL,
   exec_context_id  int           NOT NULL,
   request_id       int           NOT NULL,
   spidstr AS ltrim(str(session_id)) +
              CASE WHEN exec_context_id <> 0 OR request_id <> 0
                   THEN '/' + ltrim(str(exec_context_id)) +
                        '/' + ltrim(str(request_id))
                   ELSE ''
              END,
   is_user_process  bit           NOT NULL,
   orig_login       nvarchar(128) COLLATE Latin1_General_BIN2 NULL,
   current_login    nvarchar(128) COLLATE Latin1_General_BIN2 NULL,
   session_state    varchar(30)   COLLATE Latin1_General_BIN2 NOT NULL,
   task_state       varchar(60)   COLLATE Latin1_General_BIN2 NULL,
   proc_dbid        smallint      NULL,
   request_dbid     smallint      NULL,
   host_name        nvarchar(128) COLLATE Latin1_General_BIN2 NULL,
   host_process_id  int           NULL,
   endpoint_id      int           NOT NULL,
   program_name     nvarchar(128) COLLATE Latin1_General_BIN2 NULL,
   request_command  varchar(32)   COLLATE Latin1_General_BIN2 NULL,
   trancount        int           NOT NULL,
   session_cpu      int           NOT NULL,
   request_cpu      int           NULL,
   session_physio   bigint        NOT NULL,
   request_physio   bigint        NULL,
   session_logreads bigint        NOT NULL,
   request_logreads bigint        NULL,
   session_tempdb   bigint        NULL,
   request_tempdb   bigint        NULL,
   isclr            bit           NOT NULL DEFAULT 0,
   nest_level       int           NULL,
   now              datetime      NOT NULL,
   login_time       datetime      NOT NULL,
   last_batch       datetime      NOT NULL,
   last_since       decimal(10,3) NULL,
   curdbid          smallint      NULL,
   curobjid         int           NULL,
   current_stmt     nvarchar(MAX) COLLATE Latin1_General_BIN2 NULL,
   sql_handle       varbinary(64) NULL,
   plan_handle      varbinary(64) NULL,
   stmt_start       int           NULL,
   stmt_end         int           NULL,
   current_plan     xml           NULL,
   rowno            int           NOT NULL,
   block_level      tinyint       NULL,
   block_session_id smallint      NULL,
   block_exec_context_id int      NULL,
   block_request_id      int      NULL,
   blockercnt        int          NULL,
   block_spidstr AS ltrim(str(block_session_id)) +
               CASE WHEN block_exec_context_id <> 0 OR block_request_id <> 0
                    THEN '/' + ltrim(str(block_exec_context_id)) +
                         '/' + ltrim(str(block_request_id))
                    ELSE ''
               END +
               CASE WHEN blockercnt > 1
                    THEN ' (+' + ltrim(str(blockercnt - 1)) + ')'
                    ELSE ''
               END,
   blocksamespidonly bit          NOT NULL DEFAULT 0,
   waiter_no_blocker bit          NOT NULL DEFAULT 0,
   wait_type        varchar(60)   COLLATE Latin1_General_BIN2 NULL,
   wait_time        decimal(18,3) NULL,
   PRIMARY KEY (session_id, task_address))


-- Output from DBCC INPUTBUFFER. The IDENTITY column is there to make
-- it possible to add the spid later.
DECLARE @inputbuffer TABLE
       (eventtype    nvarchar(30)   NULL,
        params       int            NULL,
        inputbuffer  nvarchar(4000) NULL,
        ident        int            IDENTITY UNIQUE,
        spid         int            NOT NULL DEFAULT 0 PRIMARY KEY)

------------------------------------------------------------------------
-- Local variables.
------------------------------------------------------------------------
DECLARE @now             datetime,
        @ms              int,
        @spid            smallint,
        @rowc            int,
        @lvl             int,
        @dbname          sysname,
        @dbidstr         varchar(10),
        @objnameexpr     nvarchar(MAX),
        @stmt            nvarchar(MAX),
        @request_id      int,
        @handle          varbinary(64),
        @stmt_start      int,
        @stmt_end        int;

------------------------------------------------------------------------
-- Set up.
------------------------------------------------------------------------
-- All reads are dirty! The most important reason for this is tempdb.sys.objects.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;


SELECT @now = getdate();

-- Validate the @procdata parameter, and set default.
IF @procdata IS NULL
   SELECT @procdata = CASE @textmode WHEN 1 THEN 'A' ELSE 'F' END
IF @procdata NOT IN ('A', 'F')
BEGIN
   RAISERROR('Invalid value for @procdata parameter. A and F are permitted', 16, 1)
   RETURN
END

-- Check that user has permissions enough.
IF NOT EXISTS (SELECT *
               FROM   sys.fn_my_permissions(NULL, NULL)
               WHERE  permission_name = 'VIEW SERVER STATE')
BEGIN
   RAISERROR('You need to have the permission VIEW SERVER STATE to run this procedure', 16, 1)
   RETURN
END

-- If there is a request for textdata output, jump to the end where we call
-- ourselves non-texmode. (Ugly? Yes, having two procedures would be
-- prettier, but it's easier only have to distribute one.)
IF @textmode = 1 GOTO do_textmode

------------------------------------------------------------------------
-- First capture all locks. We aggregate by type, object etc to keep
-- down the volume.
------------------------------------------------------------------------
IF @debug = 1
BEGIN
   RAISERROR ('Compiling lock information, time 0 ms.', 0, 1) WITH NOWAIT
END;

-- We force binary collation, to make the GROUP BY operation faster.
WITH CTE AS (
   SELECT request_session_id,
          req_mode        = request_mode       COLLATE Latin1_General_BIN2,
          rsc_type        = resource_type      COLLATE Latin1_General_BIN2,
          rsc_subtype     = resource_subtype   COLLATE Latin1_General_BIN2,
          req_status      = request_status     COLLATE Latin1_General_BIN2,
          req_owner_type  = request_owner_type COLLATE Latin1_General_BIN2,
          rsc_description =
             CASE WHEN resource_type = 'APPLICATION'
                  THEN nullif(resource_description
                              COLLATE Latin1_General_BIN2, '')
             END,
          resource_database_id, resource_associated_entity_id
    FROM  sys.dm_tran_locks)
INSERT @locks (session_id, req_mode, rsc_type, rsc_subtype, req_status,
               req_owner_type, rsc_description,
               database_id, entity_id,
               min_entity_id, cnt)
   SELECT request_session_id, req_mode, rsc_type, rsc_subtype, req_status,
          req_owner_type, rsc_description,
          resource_database_id, resource_associated_entity_id,
          resource_associated_entity_id, COUNT(*)
   FROM   CTE
   GROUP  BY request_session_id, req_mode, rsc_type, rsc_subtype, req_status,
          req_owner_type, rsc_description,
          resource_database_id, resource_associated_entity_id

-----------------------------------------------------------------------
-- Get the blocking chain.
-----------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Determining blocking chain, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

-- First capture sys.dm_os_waiting_tasks, skipping non-spid tasks. The
-- DISTINCT is needed, because there may be duplicates. (I've seen them.)
INSERT @dm_os_waiting_tasks (wait_session_id, wait_task, block_session_id,
                             block_task, wait_type, wait_duration_ms)
   SELECT DISTINCT
          owt.session_id, owt.waiting_task_address, owt.blocking_session_id,
          CASE WHEN owt.blocking_session_id IS NOT NULL
               THEN coalesce(owt.blocking_task_address, 0x)
          END, owt.wait_type, owt.wait_duration_ms
   FROM   sys.dm_os_waiting_tasks owt
   WHERE  owt.session_id IS NOT NULL;

-----------------------------------------------------------------------
-- Get transaction.
-----------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Determining active transactions, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

; WITH oldest_tran AS (
    SELECT tst.session_id, tst.is_user_transaction,
           tat.transaction_begin_time, tat.transaction_type,
           tat.transaction_state, tat.dtc_state, tst.is_bound,
           rowno = row_number() OVER (PARTITION BY tst.session_id
                                      ORDER BY tat.transaction_begin_time ASC)
    FROM   sys.dm_tran_session_transactions tst
    JOIN   sys.dm_tran_active_transactions tat
       ON  tst.transaction_id = tat.transaction_id
)
INSERT @transactions(session_id, is_user_trans, trans_start,
                     trans_since,
                     trans_type, trans_state, dtc_state, is_bound)
   SELECT session_id, is_user_transaction, transaction_begin_time,
          CASE WHEN datediff(DAY, transaction_begin_time, @now) > 20
               THEN NULL
               ELSE datediff(MS, transaction_begin_time,  @now) / 1000.000
          END,
          transaction_type, transaction_state, dtc_state, is_bound
   FROM   oldest_tran
   WHERE  rowno = 1

------------------------------------------------------------------------
-- Then get the processes. We filter here for active processes once for all
------------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Collecting process information, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

INSERT @procs(session_id, task_address,
              exec_context_id, request_id,
              is_user_process,
              current_login,
              orig_login,
              session_state, task_state, endpoint_id, proc_dbid, request_dbid,
              host_name, host_process_id, program_name, request_command,
              trancount,
              session_cpu, request_cpu,
              session_physio, request_physio,
              session_logreads, request_logreads,
              session_tempdb, request_tempdb,
              isclr, nest_level,
              now, login_time, last_batch,
              last_since,
              sql_handle, plan_handle,
              stmt_start, stmt_end,
              rowno)
   SELECT es.session_id, coalesce(ot.task_address, 0x),
          coalesce(ot.exec_context_id, 0), coalesce(er.request_id, 0),
          es.is_user_process,
          coalesce(nullif(es.login_name, ''), suser_sname(es.security_id)),
          coalesce(nullif(es.original_login_name, ''),
                   suser_sname(es.original_security_id)),
          es.status, ot.task_state, es.endpoint_id, sp.dbid, er.database_id,
          es.host_name, es.host_process_id, es.program_name, er.command,
          coalesce(er.open_transaction_count, sp.open_tran),
          es.cpu_time, er.cpu_time,
          es.reads + es.writes, er.reads + er.writes,
          es.logical_reads, er.logical_reads,
          ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count +
             ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count,
          tsu.pages,
          coalesce(er.executing_managed_code, 0), er.nest_level,
          @now, es.login_time, es.last_request_start_time,
          CASE WHEN datediff(DAY, es.last_request_start_time, @now) > 20
               THEN NULL
               ELSE datediff(MS, es.last_request_start_time,  @now) / 1000.000
          END,
          er.sql_handle, er.plan_handle,
          er.statement_start_offset, er.statement_end_offset,
          rowno = row_number() OVER (PARTITION BY es.session_id
                                     ORDER BY ot.exec_context_id, er.request_id)
   FROM   sys.dm_exec_sessions es
   JOIN   (SELECT spid, dbid = MIN(dbid), open_tran = MIN(open_tran)
           FROM   sys.sysprocesses
           WHERE  ecid = 0
           GROUP  BY spid) AS sp ON sp.spid = es.session_id
   LEFT   JOIN sys.dm_os_tasks ot ON es.session_id = ot.session_id
   LEFT   JOIN sys.dm_exec_requests er ON ot.task_address = er.task_address
   LEFT   JOIN sys.dm_db_session_space_usage ssu ON es.session_id = ssu.session_id
   LEFT   JOIN (SELECT session_id, request_id,
                       SUM(user_objects_alloc_page_count -
                           user_objects_dealloc_page_count +
                           internal_objects_alloc_page_count -
                           internal_objects_dealloc_page_count) AS pages
                FROM   sys.dm_db_task_space_usage
                WHERE  database_id = 2
                GROUP  BY session_id, request_id) AS tsu ON tsu.session_id = er.session_id
                                                        AND tsu.request_id = er.request_id
   WHERE  -- All processes requested
          @allprocesses > 0
          -- All user sessions with a running request save ourselevs.
      OR  ot.exec_context_id IS NOT NULL AND
          es.is_user_process = 1  AND
          es.session_id <> @@spid
          -- All sessions with an open transaction, even if they are idle.
     OR   sp.open_tran > 0 AND es.session_id <> @@spid
          -- All sessions that have an interesting lock, save ourselves.
     OR   EXISTS (SELECT *
                   FROM   @locks l
                   WHERE  l.session_id = es.session_id
                     AND  l.activelock = 1) AND es.session_id <> @@spid
          -- All sessions that is blocking someone.
     OR   EXISTS (SELECT *
                  FROM   @dm_os_waiting_tasks owt
                  WHERE  owt.block_session_id = es.session_id)
     OR  ssu.user_objects_alloc_page_count - ssu.user_objects_dealloc_page_count +
         ssu.internal_objects_alloc_page_count - ssu.internal_objects_dealloc_page_count > 1000

------------------------------------------------------------------------
-- Get input buffers. Note that we can only find one per session, even
-- a session has several requests.
-- We skip this part if @@nestlevel is > 1, as presumably we are calling
-- ourselves recursively from INSERT EXEC, and we may no not do another
-- level of INSERT-EXEC.
------------------------------------------------------------------------
IF @@nestlevel = 1
BEGIN
   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Getting input buffers, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   DECLARE C1 CURSOR FAST_FORWARD LOCAL FOR
      SELECT DISTINCT session_id
      FROM   @procs
      WHERE  is_user_process = 1
   OPEN C1

   WHILE 1 = 1
   BEGIN
      FETCH C1 INTO @spid
      IF @@fetch_status <> 0
         BREAK

      BEGIN TRY
         INSERT @inputbuffer(eventtype, params, inputbuffer)
            EXEC sp_executesql N'DBCC INPUTBUFFER (@spid) WITH NO_INFOMSGS',
                               N'@spid int', @spid

         UPDATE @inputbuffer
         SET    spid = @spid
         WHERE  ident = scope_identity()
      END TRY
      BEGIN CATCH
         INSERT @inputbuffer(inputbuffer, spid)
            VALUES('Error getting inputbuffer: ' + error_message(), @spid)
      END CATCH
  END

   DEALLOCATE C1
END

-----------------------------------------------------------------------
-- Compute the blocking chain.
-----------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Computing blocking chain, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

-- Mark blockers that are waiting, that is waiting for something else
-- than another spid.
UPDATE @dm_os_waiting_tasks
SET    block_level = 0,
       lead_blocker_spid = a.wait_session_id
FROM   @dm_os_waiting_tasks a
WHERE  a.block_session_id IS NULL
  AND  EXISTS (SELECT *
               FROM   @dm_os_waiting_tasks b
               WHERE  a.wait_session_id = b.block_session_id
                 AND  a.wait_task       = b.block_task)
SELECT @rowc = @@rowcount

-- Add an extra row for blockers that are not waiting at all.
INSERT @dm_os_waiting_tasks (wait_session_id, wait_task,
                             block_level, lead_blocker_spid)
   SELECT DISTINCT a.block_session_id, coalesce(a.block_task, 0x),
                   0, a.block_session_id
   FROM   @dm_os_waiting_tasks a
   WHERE  NOT EXISTS (SELECT *
                      FROM  @dm_os_waiting_tasks b
                      WHERE a.block_session_id = b.wait_session_id
                        AND a.block_task       = b.wait_task)
     AND  a.block_session_id IS NOT NULL;

SELECT @rowc = @rowc + @@rowcount, @lvl = 0

-- Then iterate as long as we find blocked processes. You may think
-- that a recursive CTE would be great here, but we want to exclude
-- rows that has already been marked. This is difficult to do with a CTE.
WHILE @rowc > 0
BEGIN
   UPDATE a
   SET    block_level = b.block_level + 1,
          lead_blocker_spid = b.lead_blocker_spid
   FROM   @dm_os_waiting_tasks a
   JOIN   @dm_os_waiting_tasks b ON a.block_session_id = b.wait_session_id
                                AND a.block_task       = b.wait_task
   WHERE  b.block_level = @lvl
     AND  a.block_level IS NULL

  SELECT @rowc = @@rowcount, @lvl = @lvl + 1
END

-- Next to find are processes that are blocked, but no one is waiting for.
-- They are directly or indirectly blocked by a deadlock. They get a
-- negative level initially. We clean this up later.
UPDATE @dm_os_waiting_tasks
SET    block_level = -1
FROM   @dm_os_waiting_tasks a
WHERE  a.block_level IS NULL
  AND  a.block_session_id IS NOT NULL
  AND  NOT EXISTS (SELECT *
                   FROM   @dm_os_waiting_tasks b
                   WHERE  b.block_session_id = a.wait_session_id
                     AND  b.block_task       = a.wait_task)

SELECT @rowc = @@rowcount, @lvl = -2

-- Then unwind these chains in the opposite direction to before.
WHILE @rowc > 0
BEGIN
   UPDATE @dm_os_waiting_tasks
   SET    block_level = @lvl
   FROM   @dm_os_waiting_tasks a
   WHERE  a.block_level IS NULL
     AND  a.block_session_id IS NOT NULL
     AND  NOT EXISTS (SELECT *
                      FROM   @dm_os_waiting_tasks b
                      WHERE  b.block_session_id = a.wait_session_id
                        AND  b.block_task       = a.wait_task
                        AND  b.block_level IS NULL)
   SELECT @rowc = @@rowcount, @lvl = @lvl - 1
END

-- Determine which blocking tasks that only block tasks within the same
-- spid.
UPDATE @dm_os_waiting_tasks
SET    blocksamespidonly = 1
FROM   @dm_os_waiting_tasks a
WHERE  a.block_level IS NOT NULL
  AND  a.wait_session_id = a.lead_blocker_spid
  AND  NOT EXISTS (SELECT *
                   FROM   @dm_os_waiting_tasks b
                   WHERE  a.wait_session_id = b.lead_blocker_spid
                     AND  a.wait_session_id <> b.wait_session_id)

-----------------------------------------------------------------------
-- Add block-chain and wait information to @procs. If a blockee has more
-- than one blocker, we pick one.
-----------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Adding blocking chain to @procs, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

; WITH block_chain AS (
    SELECT wait_session_id, wait_task, block_session_id, block_task,
           block_level = CASE WHEN block_level >= 0 THEN block_level
                              ELSE block_level - @lvl - 1
                         END,
    wait_duration_ms, wait_type, blocksamespidonly,
    cnt   = COUNT(*) OVER (PARTITION BY wait_task),
    rowno = row_number() OVER (PARTITION BY wait_task
                               ORDER BY block_level, block_task)
    FROM @dm_os_waiting_tasks
)
UPDATE p
SET    block_level           = bc.block_level,
       block_session_id      = bc.block_session_id,
       block_exec_context_id = coalesce(p2.exec_context_id, -1),
       block_request_id      = coalesce(p2.request_id, -1),
       blockercnt            = bc.cnt,
       blocksamespidonly     = bc.blocksamespidonly,
       wait_time             = convert(decimal(18, 3), bc.wait_duration_ms) / 1000,
       wait_type             = bc.wait_type
FROM   @procs p
JOIN   block_chain bc ON p.session_id   = bc.wait_session_id
                     AND p.task_address = bc.wait_task
                     AND bc.rowno = 1
LEFT   JOIN @procs p2 ON bc.block_session_id = p2.session_id
                     AND bc.block_task       = p2.task_address

--------------------------------------------------------------------
-- Delete "uninteresting" locks from @locks for processes not in @procs.
--------------------------------------------------------------------
IF @allprocesses = 0
BEGIN
   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Deleting uninteresting locks, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   DELETE @locks
   FROM   @locks l
   WHERE  (activelock = 0 OR session_id = @@spid)
     AND  NOT EXISTS (SELECT *
                      FROM   @procs p
                      WHERE  p.session_id = l.session_id)
END

----------------------------------------------------------------------
-- Get the query text. This is not done in the main query, as we could
-- be blocked if someone is creating an SP and executes it in a
-- transaction.
----------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Retrieving current statement, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

-- Set lock timeout to avoid being blocked.
SET LOCK_TIMEOUT 5

-- First try to get all query plans in one go.
BEGIN TRY
   UPDATE @procs
   SET    curdbid      = est.dbid,
          curobjid     = est.objectid,
          current_stmt =
          CASE WHEN est.encrypted = 1
               THEN '-- ENCRYPTED, pos ' +
                    ltrim(str((p.stmt_start + 2)/2)) + ' - ' +
                    ltrim(str((p.stmt_end + 2)/2))
               WHEN p.stmt_start >= 0
               THEN substring(est.text, (p.stmt_start + 2)/2,
                              CASE p.stmt_end
                                   WHEN -1 THEN datalength(est.text)
                                 ELSE (p.stmt_end - p.stmt_start + 2) / 2
                              END)
          END
   FROM   @procs p
   CROSS  APPLY sys.dm_exec_sql_text(p.sql_handle) est
END TRY
BEGIN CATCH
   -- If this fails, try to get the texts one by one.
   DECLARE text_cur CURSOR STATIC LOCAL FOR
      SELECT DISTINCT session_id, request_id, sql_handle,
                      stmt_start, stmt_end
      FROM   @procs
      WHERE  sql_handle IS NOT NULL
   OPEN text_cur

   WHILE 1 = 1
   BEGIN
      FETCH text_cur INTO @spid, @request_id, @handle,
                          @stmt_start, @stmt_end
      IF @@fetch_status <> 0
         BREAK

      BEGIN TRY
         UPDATE @procs
         SET    curdbid      = est.dbid,
                curobjid     = est.objectid,
                current_stmt =
                CASE WHEN est.encrypted = 1
                     THEN '-- ENCRYPTED, pos ' +
                          ltrim(str((p.stmt_start + 2)/2)) + ' - ' +
                          ltrim(str((p.stmt_end + 2)/2))
                     WHEN p.stmt_start >= 0
                     THEN substring(est.text, (p.stmt_start + 2)/2,
                                    CASE p.stmt_end
                                         WHEN -1 THEN datalength(est.text)
                                       ELSE (p.stmt_end - p.stmt_start + 2) / 2
                                    END)
                END
         FROM   @procs p
         CROSS  APPLY sys.dm_exec_sql_text(p.sql_handle) est
         WHERE  p.session_id = @spid
           AND  p.request_id = @request_id
      END TRY
      BEGIN CATCH
          UPDATE @procs
          SET    current_stmt = 'ERROR: *** ' + error_message() + ' ***'
          WHERE  session_id = @spid
            AND  request_id = @request_id
      END CATCH
   END

   DEALLOCATE text_cur

   END CATCH

SET LOCK_TIMEOUT 0


-----------------------------------------------------------------------
-- Get object names from ids in @procs and @locks. You may think that
-- we could use object_name and its second database parameter, but
-- object_name takes out a Sch-S lock (even with READ UNCOMMITTED) and
-- gets blocked if a object (read temp table) has been created in a transaction.
-----------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Getting object names, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

-- First get all entity ids into the temp table. And yes, do save them in
-- three columns. We translate the resource types to our own type, depending
-- on names are to be looked up. The session_id is only of interest in
-- tempdb and only for temp tables. We use MIN, since is the same data
-- appears for the same session_id, it cannot be a temp table.
INSERT #objects (idtype, database_id, entity_id, hobt_id, min_id, session_id)
   SELECT idtype, database_id, entity_id, entity_id, entity_id,
          MIN(session_id)
   FROM   (SELECT CASE WHEN rsc_type = 'OBJECT' THEN 'OBJ'
                       WHEN rsc_type IN ('PAGE', 'KEY', 'RID', 'HOBT') THEN 'HOBT'
                       WHEN rsc_type = 'ALLOCATION_UNIT' THEN 'AU'
                   END AS idtype,
                   database_id, entity_id,
                   CASE database_id WHEN 2 THEN session_id END AS session_id
           FROM   @locks) AS l
   WHERE   idtype IS NOT NULL
   GROUP   BY idtype, database_id, entity_id
   UNION
   SELECT DISTINCT 'OBJ', curdbid, curobjid, curobjid, curobjid, NULL
   FROM   @procs
   WHERE  curdbid IS NOT NULL
     AND  curobjid IS NOT NULL

-- If the user does not have CONTROL SERVER, he may not be able to access all
-- databases. In this case, we save this to the table directly, rather than
-- handling it the error handler below (because else it destroys textmode).
IF NOT EXISTS (SELECT *
               FROM   sys.fn_my_permissions(NULL, NULL)
               WHERE  permission_name = 'CONTROL SERVER')
BEGIN
   UPDATE #objects
   SET    object_name = 'You do not have permissions to access the database ' +
                        quotename(db_name(database_id)) + '.'
   WHERE  has_dbaccess(db_name(database_id)) = 0
END


DECLARE C2 CURSOR STATIC LOCAL FOR
   SELECT DISTINCT str(database_id),
                   quotename(db_name(database_id))
   FROM   #objects
   WHERE  idtype IN  ('OBJ', 'HOBT', 'AU')
     AND  object_name IS NULL
   OPTION (KEEPFIXED PLAN)

OPEN C2

WHILE 1 = 1
BEGIN
   FETCH C2 INTO @dbidstr, @dbname
   IF @@fetch_status <> 0
      BREAK

  -- This expression is used to for the object name. It looks differently
  -- in tempdb where we drop the unique parts of temp-tables.
  SELECT @objnameexpr =
         CASE @dbname
              WHEN '[tempdb]'
              THEN 'CASE WHEN len(o.name) = 9 AND
                           o.name LIKE "#" + replicate("[0-9A-F]", 8)
                      THEN "#(tblvar or dropped temp table)"
                      WHEN len(o.name) = 128 AND o.name LIKE "#[^#]%"
                      THEN substring(o.name, 1, charindex("_____", o.name) - 1)
                      ELSE db_name(@dbidstr) + "." +
                           coalesce(s.name + "." + o.name,
                                    "<" + ltrim(str(ob.entity_id)) + ">")
                   END'
              ELSE 'db_name(@dbidstr) + "." +
                           coalesce(s.name + "." + o.name,
                                    "<" + ltrim(str(ob.entity_id)) + ">")'
         END


   -- First handle allocation units. They bring us a hobt_id, or we go
   -- directly to the object when the container is a partition_id. We
   -- always get the type_desc. To make the dynamic SQL easier to read,
   -- we use some placeholders.
   SELECT @stmt = '
      UPDATE #objects
      SET    type_desc = au.type_desc,
             hobt_id   = CASE WHEN au.type IN (1, 3)
                              THEN au.container_id
                         END,
             idtype    = CASE WHEN au.type IN (1, 3)
                              THEN "HOBT"
                              ELSE "AU"
                         END,
             object_name = CASE WHEN au.type = 2 THEN  ' +
                              @objnameexpr + ' +
                              CASE WHEN p.index_id <= 1
                                   THEN ""
                                   ELSE "." + i.name
                              END +
                              CASE WHEN p.partition_number > 1
                                   THEN "(" +
                                         ltrim(str(p.partition_number)) +
                                        ")"
                                   ELSE ""
                              END
                              WHEN au.type = 0 THEN
                                 db_name(@dbidstr) + " (dropped table et al)"
                           END
      FROM   #objects ob
      JOIN   @dbname.sys.allocation_units au ON ob.entity_id = au.allocation_unit_id
      -- We should only go all the way from sys.partitions, for type = 3.
      LEFT   JOIN  (@dbname.sys.partitions p
                    JOIN    @dbname.sys.objects o ON p.object_id = o.object_id
                    JOIN    @dbname.sys.indexes i ON p.object_id = i.object_id
                                                 AND p.index_id  = i.index_id
                    JOIN    @dbname.sys.schemas s ON o.schema_id = s.schema_id)
         ON  au.container_id = p.partition_id
        AND  au.type = 2
      WHERE  ob.database_id = @dbidstr
        AND  ob.idtype = "AU"
      OPTION (KEEPFIXED PLAN);
   '

   -- Now we can translate all hobt_id, including those we got from the
   -- allocation units.
   SELECT @stmt = @stmt + '
      UPDATE #objects
      SET    object_name = ' + @objnameexpr + ' +
                           CASE WHEN p.index_id <= 1
                                THEN ""
                                ELSE "." + i.name
                           END +
                           CASE WHEN p.partition_number > 1
                                THEN "(" +
                                      ltrim(str(p.partition_number)) +
                                     ")"
                                ELSE ""
                           END + coalesce(" (" + ob.type_desc + ")", "")
      FROM   #objects ob
      JOIN   @dbname.sys.partitions p ON ob.hobt_id  = p.hobt_id
      JOIN   @dbname.sys.objects o    ON p.object_id = o.object_id
      JOIN   @dbname.sys.indexes i    ON p.object_id = i.object_id
                                     AND p.index_id  = i.index_id
      JOIN   @dbname.sys.schemas s    ON o.schema_id = s.schema_id
      WHERE  ob.database_id = @dbidstr
        AND  ob.idtype = "HOBT"
      OPTION (KEEPFIXED PLAN)
      '

   -- And now object ids, idtype = OBJ.
   SELECT @stmt = @stmt + '
      UPDATE #objects
      SET    object_name = ' + @objnameexpr + '
      FROM   #objects ob
      LEFT   JOIN   (@dbname.sys.objects o
                     JOIN @dbname.sys.schemas s ON o.schema_id = s.schema_id)
             ON convert(int, ob.entity_id) = o.object_id
      WHERE  ob.database_id = @dbidstr
        AND  ob.idtype = "OBJ"
      OPTION (KEEPFIXED PLAN)
   '

   -- When running beta_lockinfo with only VIEW SERVER STATE, without being
   -- sysadmin, reading from the system tables will block on SQL 2005 and
   -- SQL 2008. Address this.
   SELECT @stmt = ' BEGIN TRY
                       SET LOCK_TIMEOUT 5
                  ' + @stmt +
                  ' END TRY
                    BEGIN CATCH
                       UPDATE #objects
                       SET    object_name = "Error getting object name: " +
                                            error_message()
                       WHERE  database_id = @dbidstr
                         AND  object_name IS NULL
                    END CATCH
                  '

   -- Fix the placeholders.
   SELECT @stmt = replace(replace(replace(@stmt,
                         '"', ''''),
                         '@dbname', @dbname),
                         '@dbidstr', @dbidstr)

   --  And run the beast.
   -- PRINT @stmt
   EXEC (@stmt)
END
DEALLOCATE C2

-------------------------------------------------------------------
-- Consolidate temp tables, so that if a procedure has a lock on
-- several temp tables with the same name, it is only listed once.
-------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Consolidating temp tables, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

-- Count the temp tables, and find the lowest id in each group.
; WITH mintemp AS (
   SELECT object_name, session_id, idtype,
          MIN(entity_id) AS min_id, COUNT(*) AS cnt
   FROM   #objects
   WHERE  database_id = 2
     AND  object_name LIKE '#[^#]%'
   GROUP  BY object_name, session_id, idtype
   HAVING COUNT(*) > 1
)
UPDATE #objects
SET    min_id = m.min_id,
       cnt    = m.cnt,
       object_name = m.object_name + ' (x' + ltrim(str(m.cnt)) + ')'
FROM   #objects o
JOIN   mintemp m ON m.object_name = o.object_name
                AND m.idtype      = o.idtype
                AND m.session_id  = o.session_id
WHERE  o.database_id = 2
OPTION (KEEPFIXED PLAN)


SELECT @rowc = @@rowcount

IF @rowc > 0
BEGIN
   UPDATE @locks
   SET    min_entity_id  = ob.min_id,
          ismultipletemp = 1
   FROM   @locks  l
   JOIN   #objects ob ON l.database_id = ob.database_id
                     AND l.entity_id   = ob.entity_id
                     AND l.session_id  = ob.session_id
   WHERE  l.database_id = 2
     AND  ob.database_id = 2
     AND  ob.cnt > 1
   OPTION (KEEPFIXED PLAN)

INSERT @locks (session_id, req_mode, rsc_type, rsc_subtype, req_status,
               req_owner_type, database_id, entity_id, cnt)
   SELECT session_id, req_mode, rsc_type, rsc_subtype, req_status,
          req_owner_type, 2, min_entity_id, SUM(cnt)
   FROM   @locks
   WHERE  database_id = 2
     AND  ismultipletemp = 1
   GROUP  BY session_id, req_mode, rsc_type, rsc_subtype, req_status,
            req_owner_type, min_entity_id
END

--------------------------------------------------------------------
-- Get query plans. The difficult part is that the convert to xml may
-- fail if the plan is too deep. Therefore we catch this error, and
-- resort to a cursor in this case. Since query plans are not included
-- in text mode, we skip if @nestlevel is > 1.
--------------------------------------------------------------------
IF @@nestlevel = 1
BEGIN
   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Retrieving query plans, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   -- Adam says that getting the query plans can time out too...
   SET LOCK_TIMEOUT 5

   BEGIN TRY
      UPDATE @procs
      SET    current_plan = convert(xml, etqp.query_plan)
      FROM   @procs p
      OUTER  APPLY sys.dm_exec_text_query_plan(
                   p.plan_handle, p.stmt_start, p.stmt_end) etqp
      WHERE  p.plan_handle IS NOT NULL
   END TRY
   BEGIN CATCH
      DECLARE plan_cur CURSOR STATIC LOCAL FOR
         SELECT DISTINCT session_id, request_id, plan_handle,
                         stmt_start, stmt_end
         FROM   @procs
         WHERE  plan_handle IS NOT NULL
      OPEN plan_cur

      WHILE 1 = 1
      BEGIN
         FETCH plan_cur INTO @spid, @request_id, @handle,
                             @stmt_start, @stmt_end
         IF @@fetch_status <> 0
            BREAK

         BEGIN TRY
            UPDATE @procs
            SET    current_plan = (SELECT convert(xml, etqp.query_plan)
                                   FROM   sys.dm_exec_text_query_plan(
                                      @handle, @stmt_start, @stmt_end) etqp)
            FROM   @procs p
            WHERE  p.session_id = @spid
              AND  p.request_id = @request_id
         END TRY
         BEGIN CATCH
            UPDATE @procs
            SET    current_plan =
                     (SELECT 'Could not get query plan' AS [@alert],
                             error_number() AS [@errno],
                             error_severity() AS [@level],
                             error_message() AS [@errmsg]
                      FOR    XML PATH('ERROR'))
            WHERE  session_id = @spid
              AND  request_id = @request_id
         END CATCH
      END

      DEALLOCATE plan_cur
   END CATCH

   SET LOCK_TIMEOUT 0

   -- There is a bug in dm_exec_text_query_plan which causes the attribute
   -- StatementText to include the full text of the batch up to current
   -- statement. This causes bloat in SSMS. Whence we fix the attribute.
   ; WITH XMLNAMESPACES(
      'http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS SP)
   UPDATE @procs
   SET    current_plan.modify('
            replace value of (
                  /SP:ShowPlanXML/SP:BatchSequence/SP:Batch/
                   SP:Statements/SP:StmtSimple/@StatementText)[1]
            with
               substring((/SP:ShowPlanXML/SP:BatchSequence/SP:Batch/
                         SP:Statements/SP:StmtSimple/@StatementText)[1],
                        (sql:column("stmt_start") + 2) div 2)
          ')
   WHERE  current_plan IS NOT NULL
     AND  stmt_start IS NOT NULL
END

--------------------------------------------------------------------
-- If user has selected to see process data only on the first row,
-- we should number the rows in @locks.
--------------------------------------------------------------------
IF @procdata = 'F'
BEGIN
   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Determining first row, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   ; WITH locks_rowno AS (
       SELECT rowno,
              new_rowno = row_number() OVER(PARTITION BY l.session_id
                          ORDER BY CASE l.req_status
                                        WHEN 'GRANT' THEN 'ZZZZ'
                                        ELSE l.req_status
                                   END,
                          o.object_name, l.rsc_type, l.rsc_description)
              FROM   @locks l
              LEFT   JOIN   #objects o ON l.database_id = o.database_id
                                      AND l.entity_id   = o.entity_id)
   UPDATE locks_rowno
   SET    rowno = new_rowno
   OPTION (KEEPFIXED PLAN)
END

---------------------------------------------------------------------
-- Before we can join in the locks, we need to make sure that all
-- processes with a running request has a row with exec_context_id =
-- request_id = 0. (Those without already has such a row.)
---------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Supplementing @procs, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

INSERT @procs(session_id, task_address, exec_context_id, request_id,
              is_user_process, orig_login, current_login,
              session_state, endpoint_id, trancount, proc_dbid,
              host_name, host_process_id, program_name,
              session_cpu, session_physio, session_logreads,
              now, login_time, last_batch, last_since, rowno)
   SELECT session_id, 0x, 0, 0,
          is_user_process, orig_login, current_login,
          session_state, endpoint_id, 0, proc_dbid,
          host_name, host_process_id, program_name,
          session_cpu, session_physio, session_logreads,
          now, login_time, last_batch, last_since, 0
    FROM  @procs a
    WHERE a.rowno = 1
      AND NOT EXISTS (SELECT *
                      FROM   @procs b
                      WHERE  b.session_id      = a.session_id
                        AND  b.exec_context_id = 0
                        AND  b.request_id      = 0)

-- A process may be waiting for a lock according sys.dm_os_tran_locks,
-- but it was not in sys.dm_os_waiting_tasks. Let's mark this up.
UPDATE @procs
SET    waiter_no_blocker = 1
FROM   @procs p
WHERE  EXISTS (SELECT *
               FROM   @locks l
               WHERE  l.req_status = 'WAIT'
                 AND  l.session_id = p.session_id
                 AND  NOT EXISTS (SELECT *
                                  FROM   @procs p2
                                  WHERE  p.session_id = l.session_id))

------------------------------------------------------------------------
-- For Plain results we are ready to return now.
------------------------------------------------------------------------
IF @debug = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Returning result set, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

IF @textmode = 0
BEGIN
   -- Note that the query is a full join, since @locks and @procs may not
   -- be in sync. Processes may have gone away, or be active without any
   -- locks. As for the transactions, we team up with the processes.
   SELECT spid        = coalesce(p.spidstr, ltrim(str(l.session_id))),
          command     = CASE WHEN coalesce(p.exec_context_id, 0) = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN p.request_command
                             ELSE ''
                        END,
          login       = CASE WHEN coalesce(p.exec_context_id, 0) = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN
                             CASE WHEN p.is_user_process = 0
                                  THEN 'SYSTEM PROCESS'
                                  ELSE p.orig_login +
                                     CASE WHEN p.current_login <> p.orig_login OR
                                               p.orig_login IS NULL
                                          THEN ' (' + p.current_login + ')'
                                          ELSE ''
                                     END
                            END
                            ELSE ''
                        END,
          host        = CASE WHEN coalesce(p.exec_context_id, 0)= 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN p.host_name
                             ELSE ''
                        END,
          hostprc     = CASE WHEN coalesce(p.exec_context_id, 0) = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN ltrim(str(p.host_process_id))
                             ELSE ''
                        END,
          endpoint    = CASE WHEN coalesce(p.exec_context_id, 0) = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN e.name
                             ELSE ''
                        END,
          appl        = CASE WHEN coalesce(p.exec_context_id, 0) = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN p.program_name
                             ELSE ''
                        END,
          dbname      = CASE WHEN coalesce(l.rowno, 1) = 1 AND
                                  coalesce(p.exec_context_id, 0) = 0
                             THEN coalesce(db_name(p.request_dbid),
                                           db_name(p.proc_dbid))
                             ELSE ''
                        END,
          prcstatus   = CASE WHEN coalesce(l.rowno, 1) = 1
                             THEN coalesce(p.task_state, p.session_state)
                             ELSE ''
                        END,
          spid_       = p.spidstr,
          opntrn      = CASE WHEN p.exec_context_id = 0
                             THEN coalesce(ltrim(str(nullif(p.trancount, 0))), '')
                             ELSE ''
                        END,
          trninfo     = CASE WHEN coalesce(l.rowno, 1) = 1 AND
                                  p.exec_context_id = 0 AND
                                  t.is_user_trans IS NOT NULL
                             THEN CASE t.is_user_trans
                                       WHEN 1 THEN 'U'
                                       ELSE 'S'
                                  END + '-' +
                                  CASE t.trans_type
                                       WHEN 1 THEN 'RW'
                                       WHEN 2 THEN 'R'
                                       WHEN 3 THEN 'SYS'
                                       WHEN 4 THEN 'DIST'
                                       ELSE ltrim(str(t.trans_type))
                                  END + '-' +
                                  ltrim(str(t.trans_state)) +
                                  CASE t.dtc_state
                                       WHEN 0 THEN ''
                                       ELSE '-'
                                  END +
                                  CASE t.dtc_state
                                     WHEN 0 THEN ''
                                     WHEN 1 THEN 'DTC:ACTIVE'
                                     WHEN 2 THEN 'DTC:PREPARED'
                                     WHEN 3 THEN 'DTC:COMMITED'
                                     WHEN 4 THEN 'DTC:ABORTED'
                                     WHEN 5 THEN 'DTC:RECOVERED'
                                     ELSE 'DTC:' + ltrim(str(t.dtc_state))
                                 END +
                                 CASE t.is_bound
                                    WHEN 0 THEN ''
                                    WHEN 1 THEN '-BND'
                                 END
                            ELSE ''
                        END,
          blklvl      = CASE WHEN p.block_level IS NOT NULL
                             THEN CASE p.blocksamespidonly
                                       WHEN 1 THEN '('
                                       ELSE ''
                                  END +
                                  CASE WHEN p.block_level = 0
                                       THEN '!!'
                                       ELSE ltrim(str(p.block_level))
                                  END +
                                  CASE p.blocksamespidonly
                                       WHEN 1 THEN ')'
                                       ELSE ''
                                  END
                             -- If the process is blocked, but we do not
                             -- have a block level, the process is in a
                             -- dead lock.
                             WHEN p.block_session_id IS NOT NULL
                             THEN 'DD'
                             WHEN p.waiter_no_blocker = 1
                             THEN '??'
                             ELSE ''
                        END,
          blkby       = coalesce(p.block_spidstr, ''),
          cnt         = CASE WHEN p.exec_context_id = 0 AND
                                  p.request_id = 0
                             THEN coalesce(ltrim(str(l.cnt)), '0')
                             ELSE ''
                        END,
          object      = CASE l.rsc_type
                           WHEN 'APPLICATION'
                           THEN coalesce(db_name(l.database_id) + '|', '') +
                                         l.rsc_description
                           ELSE coalesce(o2.object_name,
                                         db_name(l.database_id), '')
                        END,
          rsctype     = coalesce(l.rsc_type, ''),
          locktype    = coalesce(l.req_mode, ''),
          lstatus     = CASE l.req_status
                             WHEN 'GRANT' THEN lower(l.req_status)
                             ELSE coalesce(l.req_status, '')
                        END,
          ownertype   = CASE l.req_owner_type
                             WHEN 'SHARED_TRANSACTION_WORKSPACE' THEN 'STW'
                             ELSE coalesce(l.req_owner_type, '')
                        END,
          rscsubtype  = coalesce(l.rsc_subtype, ''),
          waittime    = CASE WHEN coalesce(l.rowno, 1) = 1
                             THEN coalesce(ltrim(str(p.wait_time, 18, 3)), '')
                             ELSE ''
                        END,
          waittype    = CASE WHEN coalesce(l.rowno, 1) = 1
                             THEN coalesce(p.wait_type, '')
                             ELSE ''
                        END,
          spid__      = p.spidstr,
          cpu         = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN coalesce(ltrim(str(p.session_cpu)), '') +
                             CASE WHEN p.request_cpu IS NOT NULL
                                  THEN ' (' + ltrim(str(p.request_cpu)) + ')'
                                  ELSE ''
                             END
                             ELSE ''
                        END,
          physio      = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN coalesce(ltrim(str(p.session_physio, 18)), '') +
                             CASE WHEN p.request_physio IS NOT NULL
                                  THEN ' (' + ltrim(str(p.request_physio)) + ')'
                                  ELSE ''
                             END
                             ELSE ''
                        END,
          logreads    = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN coalesce(ltrim(str(p.session_logreads, 18)), '')  +
                             CASE WHEN p.request_logreads IS NOT NULL
                                  THEN ' (' + ltrim(str(p.request_logreads)) + ')'
                                  ELSE ''
                             END
                             ELSE ''
                        END,
          tempdb    = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN coalesce(ltrim(str(p.session_tempdb, 18)), '')  +
                             CASE WHEN p.request_tempdb IS NOT NULL
                                  THEN ' (' + ltrim(str(p.request_tempdb)) + ')'
                                  ELSE ''
                             END
                             ELSE ''
                        END,
          now         = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN convert(char(12), p.now, 114)
                             ELSE ''
                        END,
          login_time  = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN
                             CASE datediff(DAY, p.login_time, @now)
                                  WHEN 0
                                  THEN convert(varchar(8), p.login_time, 8)
                                  ELSE convert(char(7), p.login_time, 12) +
                                       convert(varchar(8), p.login_time, 8)
                             END
                             ELSE ''
                        END,
          last_batch  = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN
                             CASE datediff(DAY, p.last_batch, @now)
                                  WHEN 0
                                  THEN convert(varchar(8),
                                               p.last_batch, 8)
                                  ELSE convert(char(7), p.last_batch, 12) +
                                       convert(varchar(8), p.last_batch, 8)
                             END
                             ELSE ''
                        END,
          trn_start   = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1 AND
                                  t.trans_start IS NOT NULL
                             THEN
                             CASE datediff(DAY, t.trans_start, @now)
                                  WHEN 0
                                  THEN convert(varchar(8),
                                               t.trans_start, 8)
                                  ELSE convert(char(7), t.trans_start, 12) +
                                       convert(varchar(8), t.trans_start, 8)
                             END
                             ELSE ''
                        END,
          last_since  = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN str(p.last_since, 11, 3)
                             ELSE ''
                        END,
          trn_since   = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1 AND
                                  t.trans_since IS NOT NULL
                             THEN str(t.trans_since, 11, 3)
                             ELSE ''
                        END,
          clr         = CASE WHEN p.exec_context_id = 0 AND p.isclr = 1
                             THEN 'CLR'
                             ELSE ''
                        END,
          nstlvl      = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN coalesce(ltrim(str(p.nest_level)), '')
                             ELSE ''
                        END,
          spid___     = p.spidstr,
          inputbuffer = CASE WHEN p.exec_context_id = 0 AND
                                  coalesce(l.rowno, 1) = 1
                             THEN coalesce(i.inputbuffer, '')
                             ELSE ''
                        END,
          current_sp  = coalesce(o1.object_name, ''),
          curstmt     = CASE WHEN coalesce(l.rowno, 1) = 1
                             THEN coalesce(p.current_stmt, '')
                             ELSE coalesce(substring(
                                        p.current_stmt, 1, 50), '')
                        END,
          current_plan = CASE WHEN p.exec_context_id = 0 AND
                                   coalesce(l.rowno, 1) = 1
                              THEN p.current_plan
                         END
   FROM   @procs p
   LEFT   JOIN #objects o1 ON p.curdbid  = o1.database_id
                          AND p.curobjid = o1.entity_id
   LEFT   JOIN @inputbuffer i ON p.session_id = i.spid
                             AND p.exec_context_id = 0
   LEFT   JOIN sys.endpoints e ON p.endpoint_id = e.endpoint_id
   LEFT   JOIN @transactions t ON t.session_id = p.session_id
   FULL   JOIN ((SELECT *
                 FROM   @locks
                 WHERE  ismultipletemp = 0) AS l
                 LEFT JOIN #objects o2 ON l.database_id = o2.database_id
                                      AND l.entity_id   = o2.entity_id)
     ON    p.session_id      = l.session_id
    AND    p.exec_context_id = 0
    AND    p.request_id      = 0
   ORDER BY coalesce(p.session_id, l.session_id),
            p.exec_context_id, coalesce(nullif(p.request_id, 0), 99999999),
            l.rowno, lstatus,
            coalesce(o2.object_name, db_name(l.database_id)),
            l.rsc_type, l.rsc_description
   OPTION (KEEPFIXED PLAN)
END
ELSE
BEGIN
do_textmode:
   ------------------------------------------------------------------------
   -- For textmode result, we run ourselves in gridmode, receiving the
   -- result into a temp table.
   ------------------------------------------------------------------------

   CREATE TABLE #textmode(
          ident       int            IDENTITY,
          spid        varchar(30)    COLLATE Latin1_General_BIN2 NOT NULL,
          command     varchar(32)    COLLATE Latin1_General_BIN2 NULL,
          login       sysname        COLLATE Latin1_General_BIN2 NULL,
          host        nvarchar(128)  COLLATE Latin1_General_BIN2 NULL,
          hostprc     varchar(10)    COLLATE Latin1_General_BIN2 NULL,
          endpoint    sysname        COLLATE Latin1_General_BIN2 NULL,
          appl        nvarchar(128)  COLLATE Latin1_General_BIN2 NULL,
          dbname      sysname        COLLATE Latin1_General_BIN2 NULL,
          prcstatus   varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          spid_       varchar(30)    COLLATE Latin1_General_BIN2 NULL,
          opntrn      varchar(10)    COLLATE Latin1_General_BIN2 NULL,
          trninfo     varchar(30)    COLLATE Latin1_General_BIN2 NULL,
          blklvl      char(3)        COLLATE Latin1_General_BIN2 NULL,
          blkby       varchar(30)    COLLATE Latin1_General_BIN2 NULL,
          cnt         varchar(10)    COLLATE Latin1_General_BIN2 NULL,
          object      nvarchar(520)  COLLATE Latin1_General_BIN2 NULL,
          rsctype     varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          locktype    varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          lstatus     varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          ownertype   varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          rscsubtype  varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          waittime    varchar(16)    COLLATE Latin1_General_BIN2 NULL,
          waittype    varchar(60)    COLLATE Latin1_General_BIN2 NULL,
          spid__      varchar(30)    COLLATE Latin1_General_BIN2 NULL,
          cpu         varchar(30)    COLLATE Latin1_General_BIN2 NULL,
          physio      varchar(50)    COLLATE Latin1_General_BIN2 NULL,
          logreads    varchar(50)    COLLATE Latin1_General_BIN2 NULL,
          tempdb      varchar(50)    COLLATE Latin1_General_BIN2 NULL,
          now         char(12)       COLLATE Latin1_General_BIN2 NULL,
          login_time  varchar(16)    COLLATE Latin1_General_BIN2 NULL,
          last_batch  varchar(16)    COLLATE Latin1_General_BIN2 NULL,
          trn_start   varchar(16)    COLLATE Latin1_General_BIN2 NULL,
          last_since  varchar(11)    COLLATE Latin1_General_BIN2 NULL,
          trn_since   varchar(11)    COLLATE Latin1_General_BIN2 NULL,
          clr         char(3)        COLLATE Latin1_General_BIN2 NULL,
          nstlvl      char(3)        COLLATE Latin1_General_BIN2 NULL,
          spid___     varchar(30)    COLLATE Latin1_General_BIN2 NULL,
          inputbuffer nvarchar(4000) COLLATE Latin1_General_BIN2 NULL,
          current_sp  nvarchar(400)  COLLATE Latin1_General_BIN2 NULL,
          curstmt     nvarchar(MAX)  COLLATE Latin1_General_BIN2 NULL,
          queryplan   xml            NULL,
          last        bit            NOT NULL DEFAULT 0)

   -- Do the recursive call.
   INSERT #textmode (spid, command, login, host, hostprc, endpoint, appl,
                     dbname, prcstatus, spid_, opntrn, trninfo,
                     blklvl, blkby, cnt, object, rsctype, locktype, lstatus,
                     ownertype, rscsubtype, waittime, waittype, spid__, cpu,
                     physio, logreads, tempdb, now, login_time,
                     last_batch, trn_start, last_since, trn_since, clr, nstlvl,
                     spid___, inputbuffer, current_sp, curstmt, queryplan)
      EXEC beta_lockinfo @allprocesses = @allprocesses, @textmode = 0,
                         @procdata = @procdata, @debug = @debug

   -- inputbuffer is always NULL, as the recursive call skips that part.
   -- We need to do that now.
   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Getting input buffers, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   DECLARE C3 CURSOR FAST_FORWARD LOCAL FOR
      SELECT DISTINCT spid
      FROM   #textmode
      WHERE  login <> 'SYSTEM PROCESS'
        AND  spid NOT LIKE '%/%'
   OPEN C3

   WHILE 1 = 1
   BEGIN
      FETCH C3 INTO @spid
      IF @@fetch_status <> 0
         BREAK

      BEGIN TRY
         INSERT @inputbuffer(eventtype, params, inputbuffer)
            EXEC sp_executesql N'DBCC INPUTBUFFER (@spid) WITH NO_INFOMSGS',
                               N'@spid int', @spid

         UPDATE @inputbuffer
         SET    spid = @spid
         WHERE  ident = scope_identity()
      END TRY
      BEGIN CATCH
         INSERT @inputbuffer(inputbuffer, spid)
            VALUES('Error getting inputbuffer: ' + error_message(), @spid)
      END CATCH
   END

   DEALLOCATE C3

   -- Copy to the temp table and remove line breaks while we're at it.
   UPDATE #textmode
   SET    inputbuffer = replace(replace(i.inputbuffer,
                                char(10), ' '), char(13), ' ')
   FROM   #textmode t
   JOIN   @inputbuffer i ON CASE WHEN t.spid NOT LIKE '%/%'
                                 THEN convert(int, t.spid)
                            END = i.spid
   OPTION (KEEPFIXED PLAN)

   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Adjusting result set for text mode, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   -- Mark last row.
   UPDATE #textmode
   SET    last = 1
   FROM   #textmode f1
   JOIN   (SELECT spid, ident = MAX(ident)
           FROM   (SELECT ident,
                          spid = substring(spid, 1,
                                 coalesce(nullif(
                                            charindex('-', spid, 2) - 1,
                                           -1), len(spid)))
                   FROM   #textmode) AS x
           GROUP  BY spid) AS f2 ON f2.ident = f1.ident
   OPTION (KEEPFIXED PLAN)

   -- Local varibles for the max lengths of all columns.
   DECLARE @spidlen        varchar(5),
           @commandlen     varchar(5),
           @loginlen       varchar(5),
           @hostlen        varchar(5),
           @hostprclen     varchar(5),
           @endpointlen    varchar(5),
           @appllen        varchar(5),
           @dbnamelen      varchar(5),
           @prcstatuslen   varchar(5),
           @opntrnlen      varchar(5),
           @trninfolen     varchar(5),
           @blkbylen       varchar(5),
           @cntlen         varchar(5),
           @objectlen      varchar(5),
           @rsctypelen     varchar(5),
           @locktypelen    varchar(5),
           @lstatuslen     varchar(5),
           @ownertypelen   varchar(5),
           @rscsubtypelen  varchar(5),
           @waittimelen    varchar(5),
           @waittypelen    varchar(5),
           @cpulen         varchar(5),
           @physiolen      varchar(5),
           @logreadslen    varchar(5),
           @tempdblen      varchar(5),
           @login_timelen  varchar(5),
           @last_batchlen  varchar(5),
           @trn_startlen   varchar(5),
           @last_sincelen  varchar(5),
           @trn_sincelen   varchar(5),
           @inputbufferlen varchar(5),
           @current_splen  varchar(5)

   -- Get all maxlengths
   SELECT @spidlen        = convert(varchar(5), coalesce(nullif(max(len(spid)), 0), 1)),
          @commandlen     = convert(varchar(5), coalesce(nullif(max(len(command)), 0), 1)),
          @loginlen       = convert(varchar(5), coalesce(nullif(max(len(login)), 0), 1)),
          @hostlen        = convert(varchar(5), coalesce(nullif(max(len(host)), 0), 1)),
          @hostprclen     = convert(varchar(5), coalesce(nullif(max(len(hostprc)), 0), 1)),
          @endpointlen    = convert(varchar(5), coalesce(nullif(max(len(endpoint)), 0), 1)),
          @appllen        = convert(varchar(5), coalesce(nullif(max(len(appl)), 0), 1)),
          @dbnamelen      = convert(varchar(5), coalesce(nullif(max(len(dbname)), 0), 1)),
          @prcstatuslen   = convert(varchar(5), coalesce(nullif(max(len(prcstatus)), 0), 1)),
          @opntrnlen      = convert(varchar(5), coalesce(nullif(max(len(opntrn)), 0), 1)),
          @trninfolen     = convert(varchar(5), coalesce(nullif(max(len(trninfo)), 0), 1)),
          @blkbylen       = convert(varchar(5), coalesce(nullif(max(len(blkby)), 0), 1)),
          @cntlen         = convert(varchar(5), coalesce(nullif(max(len(cnt)), 0), 1)),
          @objectlen      = convert(varchar(5), coalesce(nullif(max(len(object)), 0), 1)),
          @rsctypelen     = convert(varchar(5), coalesce(nullif(max(len(rsctype)), 0), 1)),
          @locktypelen    = convert(varchar(5), coalesce(nullif(max(len(locktype)), 0), 1)),
          @lstatuslen     = convert(varchar(5), coalesce(nullif(max(len(lstatus)), 0), 1)),
          @ownertypelen   = convert(varchar(5), coalesce(nullif(max(len(ownertype)), 0), 1)),
          @rscsubtypelen  = convert(varchar(5), coalesce(nullif(max(len(rscsubtype)), 0), 1)),
          @waittimelen    = convert(varchar(5), coalesce(nullif(max(len(waittime)), 0), 1)),
          @waittypelen    = convert(varchar(5), coalesce(nullif(max(len(waittype)), 0), 1)),
          @cpulen         = convert(varchar(5), coalesce(nullif(max(len(cpu)), 0), 1)),
          @physiolen      = convert(varchar(5), coalesce(nullif(max(len(physio)), 0), 1)),
          @logreadslen    = convert(varchar(5), coalesce(nullif(max(len(logreads)), 0), 1)),
          @tempdblen      = convert(varchar(5), coalesce(nullif(max(len(tempdb)), 0), 1)),
          @login_timelen  = convert(varchar(5), coalesce(nullif(max(len(login_time)), 0), 1)),
          @last_batchlen  = convert(varchar(5), coalesce(nullif(max(len(last_batch)), 0), 1)),
          @trn_startlen   = convert(varchar(5), coalesce(nullif(max(len(trn_start)), 0), 1)),
          @last_sincelen  = convert(varchar(5), coalesce(nullif(max(len(ltrim(last_since))), 0), 1)),
          @trn_sincelen   = convert(varchar(5), coalesce(nullif(max(len(ltrim(trn_since))), 0), 1)),
          @inputbufferlen = convert(varchar(5), coalesce(nullif(max(len(inputbuffer)), 0), 1)),
          @current_splen  = convert(varchar(5), coalesce(nullif(max(len(current_sp)), 0), 1))
   FROM   #textmode
   OPTION (KEEPFIXED PLAN)

   -- Remove line breaks in current statement
   UPDATE #textmode
   SET    curstmt = replace(replace(curstmt, char(10), ''), char(13), '')
   WHERE  len(curstmt) > 0
   OPTION (KEEPFIXED PLAN)

   -- Return the #textdata table with dynamic lengths.
   IF @debug = 1
   BEGIN
      SELECT @ms = datediff(ms, @now, getdate())
      RAISERROR ('Returning result set, time %d ms.', 0, 1, @ms) WITH NOWAIT
   END

   EXEC ('SELECT spid        = convert(varchar( ' + @spidlen + '), spid),
                 command     = convert(varchar( ' + @commandlen + '), command),
                 login       = convert(nvarchar( ' + @loginlen + '), login),
                 host        = convert(nvarchar( ' + @hostlen + '), host),
                 hostprc     = convert(varchar( ' + @hostprclen + '), hostprc),
                 endpoint    = convert(varchar( ' + @endpointlen + '), endpoint),
                 appl        = convert(nvarchar( ' + @appllen + '), appl),
                 dbname      = convert(nvarchar( ' + @dbnamelen + '), dbname),
                 prcstatus   = convert(varchar( ' + @prcstatuslen + '), prcstatus),
                 spid_       = convert(varchar( ' + @spidlen + '), spid),
                 opntrn      = convert(varchar( ' + @opntrnlen + '), opntrn),
                 trninfo     = convert(varchar( ' + @trninfolen + '), trninfo),
                 blklvl,
                 blkby       = convert(varchar( ' + @blkbylen + '), blkby),
                 cnt         = convert(varchar( ' + @cntlen + '), cnt),
                 object      = convert(nvarchar( ' + @objectlen + '), object),
                 rsctype     = convert(varchar( ' + @rsctypelen + '), rsctype),
                 locktype    = convert(varchar( ' + @locktypelen + '), locktype),
                 lstatus     = convert(varchar( ' + @lstatuslen + '), lstatus),
                 ownertype   = convert(varchar( ' + @ownertypelen + '), ownertype),
                 rscsubtype  = convert(varchar( ' + @rscsubtypelen + '), rscsubtype),
                 waittime    = convert(varchar( ' + @waittimelen + '), waittime),
                 waittype    = convert(varchar( ' + @waittypelen + '), waittype),
                 spid__      = convert(varchar( ' + @spidlen + '), spid),
                 cpu         = convert(varchar( ' + @cpulen + '), cpu),
                 physio      = convert(varchar( ' + @physiolen + '), physio),
                 logreads    = convert(varchar( ' + @logreadslen + '), logreads),
                 tempdb      = convert(varchar( ' + @tempdblen + '), tempdb),
                 now,
                 login_time  = convert(varchar( ' + @login_timelen + '), login_time),
                 last_batch  = convert(varchar( ' + @last_batchlen + '), last_batch),
                 trn_start   = convert(varchar( ' + @trn_startlen + '), trn_start),
                 last_since  = convert(varchar( ' + @last_sincelen + '), ltrim(last_since)),
                 trn_since   = convert(varchar( ' + @trn_sincelen + '), ltrim(trn_since)),
                 clr,
                 nstlvl,
                 spid___     = convert(varchar( ' + @spidlen + '), spid),
                 inputbuffer = convert(nvarchar( ' + @inputbufferlen + '), inputbuffer),
                 current_sp  = convert(nvarchar( ' + @current_splen + '), current_sp),
                 curstmt,
                 CASE last WHEN 1 THEN char(10) ELSE '' '' END
          FROM   #textmode
          ORDER  BY ident
          OPTION (KEEPFIXED PLAN)')
END

IF @debug = 1 AND @@nestlevel = 1
BEGIN
   SELECT @ms = datediff(ms, @now, getdate())
   RAISERROR ('Completed, time %d ms.', 0, 1, @ms) WITH NOWAIT
END

GO
/****** Object:  StoredProcedure [dbo].[utility_EmailHtmlStringToHtmlTable]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: send the text as html report
-- =============================================
CREATE PROCEDURE [dbo].[utility_EmailHtmlStringToHtmlTable]
(
   @p_Subject      NVARCHAR(200),
   @p_HtmlString   NVARCHAR(MAX)
)
AS
BEGIN

   --send email by send inti db email
   EXEC dbo.utility_SendInitDBEmail @p_Subject=@p_Subject,
                                    @p_Body=@p_HtmlString

END

GO
/****** Object:  StoredProcedure [dbo].[utility_EmailProcedureToHtmlTable]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Send the procedure result by html report
-- =============================================
CREATE PROCEDURE [dbo].[utility_EmailProcedureToHtmlTable]
(
   @p_Subject    NVARCHAR(200),
   @p_Procedure  NVARCHAR(MAX)
)
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_Subject    NVARCHAR(200)
   DECLARE @l_SqlQuery   NVARCHAR(MAX) 

   SET @l_SqlQuery='SELECT * FROM OPENQUERY(HelpServer,'''+@p_Procedure+''')'
  
   EXEC dbo.utility_EmailQueryToHtmlTable @p_Subject=@p_Subject,
                                          @p_SqlQuery=@l_SqlQuery
END

GO
/****** Object:  StoredProcedure [dbo].[utility_EmailQueryToHtmlTable]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Send the query's result by html report 
-- =============================================
CREATE PROCEDURE [dbo].[utility_EmailQueryToHtmlTable]
(
   @p_Subject    NVARCHAR(200),
   @p_SqlQuery   NVARCHAR(MAX)
)
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_Html      NVARCHAR(MAX)
   DECLARE @l_QHeader   NVARCHAR(MAX) 
   DECLARE @l_QColumn   NVARCHAR(MAX)
   DECLARE @l_TColumn   NVARCHAR(MAX)
   DECLARE @l_TQuery    NVARCHAR(MAX)
   DECLARE @l_CSS       NVARCHAR(MAX) 

   SET @l_Html      =''
   SET @l_QHeader   ='' 
   SET @l_QColumn   =''
   SET @l_TColumn   =''
   SET @l_TQuery    =''

   SET @l_CSS= '<style type="text/css">
                   table.gridtable {
                      font-family: verdana,arial,sans-serif;
                      font-size:11px;
                      color:#333333;
                      border- 1px;
                      border-color: #666666;
                      border-collapse: collapse; }

                   table.gridtable th {
                      border- 1px;
                      padding: 8px;
                      border-style: solid;
                      border-color: #666666;
                      background-color: #dedede;}

                   table.gridtable td {
                      border- 1px;
                      padding: 8px;
                      border-style: solid;
                      border-color: #666666;
                      background-color: #ffffff;}
                  </style>'


   SET @l_TQuery = 'SELECT * INTO #TColumns FROM ( ' + @p_SqlQuery + ') Temp  '

   SET @l_TQuery=@l_TQuery+ ' SELECT @l_TColumn = @l_TColumn + name  + '', '' 
                                FROM tempdb.sys.columns 
                               WHERE object_id = object_id('+'''tempdb..#TColumns'''+')'

   SET @l_TQuery=@l_TQuery +' DROP TABLE tempdb..#TColumns'
   SET @l_TQuery=@l_TQuery + ' SET  @l_TColumn = LEFT(@l_TColumn,LEN(@l_TColumn)-1)'

   EXECUTE sp_executesql @l_TQuery ,N'@l_TColumn NVARCHAR(MAX) OUTPUT',@l_TColumn OUTPUT

   SELECT @l_QColumn = @l_QColumn + 'ISNULL(' + 'CAST('+Value +' AS NVARCHAR(MAX))' +' ,'''')' + ' AS TD, ' 
     FROM dbo.fn_SplitStringToTable(@l_TColumn,',')

   SET  @l_QColumn = LEFT(@l_QColumn,LEN(@l_QColumn)-1)

   SELECT @l_QHeader = @l_QHeader + '<TH>' +  Value + '</TH>' 
     FROM dbo.fn_SplitStringToTable(@l_TColumn,',')

   SET @l_QHeader = '<TR>' + @l_QHeader  + '</TR>'
      
   SET @l_TQuery = 'SET  @Html = (SELECT ' + @l_QColumn + ' FROM ( ' + @p_SqlQuery + ') AS TR 
                                  FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS)'
                                 
                                 
   SELECT @l_TQuery 

   EXECUTE sp_executesql @l_TQuery,N'@Html NVARCHAR(MAX) OUTPUT',@l_Html OUTPUT
   SET  @l_Html = @l_CSS + REPLACE(@l_Html,'<TABLE>' ,'<TABLE  class="gridtable">' + @l_QHeader)

   --send email by send inti db email
   EXEC dbo.utility_SendInitDBEmail @p_Subject=@p_Subject,
                                       @p_Body=@l_Html

END

GO
/****** Object:  StoredProcedure [dbo].[utility_INDEX_GetIndexFragementInfoForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get frgement info for all databases on a given server
-- =============================================

CREATE PROCEDURE [dbo].[utility_INDEX_GetIndexFragementInfoForAllDatabase]
   @p_IsAlert           BIT = 0
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   CREATE TABLE #FragmentedIndexes
   (   DatabaseName       NVARCHAR(200),
       SchemaName         NVARCHAR(200),
       TableName          NVARCHAR(200),
       IndexName          NVARCHAR(200),
       [Fragmentation%]   FLOAT )

   EXEC sp_MSforeachdb 'USE [?];
      INSERT INTO #FragmentedIndexes
      SELECT DB_NAME(DB_ID()) AS DatabaseName,
             sc.name AS SchemaName,
             OBJECT_NAME (s.object_id) AS TableName,
             i.name AS IndexName,
             s.avg_fragmentation_in_percent AS [Fragmentation%]
        FROM sys.dm_db_index_physical_stats(db_id(),NULL, NULL, NULL,''SAMPLED'') s
             INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
             INNER JOIN sys.objects o ON s.object_id = o.object_id
             INNER JOIN sys.schemas sc ON sc.[schema_id] = o.[schema_id]
       WHERE s.database_id = DB_ID()
             AND i.index_id != 0
             AND s.record_count > 0
             AND s.avg_fragmentation_in_percent>5
             AND o.is_ms_shipped = 0 ;'




   --Generate rebuild/reorganize index script

      SELECT 
          CASE WHEN [Fragmentation%] > 30
                  THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
                       + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                       + QUOTENAME(TableName) + ' REBUILD;'
               WHEN [Fragmentation%] > 10
                  THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
                       + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                       + QUOTENAME(TableName) + ' REORGANIZE;' END AS MaintanceScript
     INTO #TScript
     FROM #FragmentedIndexes
    WHERE [Fragmentation%] > 10


   
   IF @p_IsAlert=0
      SELECT * FROM #FragmentedIndexes
      SELECT * FROM #TScript

   --send alert report part 
   IF EXISTS(SELECT 1 FROM #FragmentedIndexes WHERE @p_IsAlert=1 AND [Fragmentation%]>=10 )
   BEGIN 
      SELECT * INTO #TAlert FROM  #FragmentedIndexes WHERE [Fragmentation%]>=10
      SELECT * INTO #TAlert1 FROM #TScript

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 
      

      --for #TAlert part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Get Index Fragement From All Database' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT
                                 
      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody                                      
                                                     
      --for  #TAlert1 part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Get Index Fragement From All Database' 
      SET @l_EmailBody='' 


      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert1') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert1 AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT     

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  

      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody 
      DROP TABLE #TAlert
      DROP TABLE #TAlert1
   END
   
   DROP TABLE #FragmentedIndexes
   DROP TABLE #TScript

END

GO
/****** Object:  StoredProcedure [dbo].[utility_INDEX_GetStatisticsStateInfoForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get statistics state info for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_INDEX_GetStatisticsStateInfoForAllDatabase] 
AS
BEGIN

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET NOCOUNT ON
   
   SELECT DB_NAME() AS DBName,
          ss.name AS SchemaName,
          st.name AS TableName,
          s.name AS IndexName,
          STATS_DATE(s.id,s.indid) AS [Statistics Last Updated],
          s.rowcnt AS [Row Count],
          s.rowmodctr AS [Number Of Changes],
          CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS [% Rows Changed]
     INTO #TResult
     FROM sys.sysindexes s
          INNER JOIN sys.tables st ON st.[object_id] = s.[id]
          INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
    WHERE 1=2

   EXEC sp_MSforeachdb 'USE [?];
   INSERT INTO #TResult
      SELECT DB_NAME() AS DBName,
             ss.name AS SchemaName,
             st.name AS TableName,
             s.name AS IndexName,
             STATS_DATE(s.id,s.indid) AS [Statistics Last Updated],
             s.rowcnt AS [Row Count],
             s.rowmodctr AS [Number Of Changes],
             CAST((CAST(s.rowmodctr AS DECIMAL(28,8))/CAST(s.rowcnt AS DECIMAL(28,2)) * 100.0) AS DECIMAL(28,2)) AS [% Rows Changed]
        FROM sys.sysindexes s
             INNER JOIN sys.tables st ON st.[object_id] = s.[id]
             INNER JOIN sys.schemas ss ON ss.[schema_id] = st.[schema_id]
       WHERE s.id > 100
             AND s.indid > 0
             AND s.rowcnt >= 500
    ORDER BY SchemaName, TableName, IndexName'
    
    SELECT * FROM #TResult
    DROP TABLE #TResult

END

GO
/****** Object:  StoredProcedure [dbo].[utility_INDEX_GetTopMissingIndexForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Purpose:  
-- Create Date: 08/29/2012   
-- Last Update: 08/29/2012
-- Author:      Alex Tian

CREATE PROCEDURE [dbo].[utility_INDEX_GetTopMissingIndexForAllDatabase]
   @p_IsAlert           BIT = 0
AS
BEGIN
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SELECT TOP 20
          'CREATE NONCLUSTERED INDEX '+ 
          QUOTENAME('IX_AutoGenerated_'+ 
          REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ' ', '_'), ':', '_')+ 
          '_' + CAST(d.index_handle AS VARCHAR(22)))+ ' ON ' + d.[statement] +
          '('+ CASE
               WHEN d.equality_columns IS NULL THEN d.inequality_columns
               WHEN d.inequality_columns IS NULL THEN d.equality_columns
               ELSE d.equality_columns + ',' + d.inequality_columns END + ')'+ 
               CASE WHEN d.included_columns IS NOT NULL THEN ' INCLUDE ( ' + d.included_columns + ')'
               ELSE '' END AS MissingIndexSQL,
           ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans),0) AS [Total Cost],
           d.[statement] AS [Table Name],
           d.equality_columns,
           d.inequality_columns,
           d.included_columns
       INTO #MissingIndexes
       FROM sys.dm_db_missing_index_groups g
            INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
   ORDER BY [Total Cost] DESC


   SELECT  MissingIndexSQL AS  MaintanceScript INTO #TScript
     FROM #MissingIndexes

  


   IF @p_IsAlert=0
      SELECT * FROM #MissingIndexes
      SELECT * FROM #TScript

   --send alert report part 
   IF EXISTS(SELECT 1 FROM #MissingIndexes WHERE @p_IsAlert=1)
   BEGIN 
  
      SELECT * INTO #TAlert FROM #MissingIndexes 
      SELECT * INTO #TAlert1 FROM #TScript

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 
      

      --for #TAlert part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Get Top Missing Index' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT
                                 
      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody                                      
                                                     
      --for  #TAlert1 part
      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Get Top Missing Index' 
      SET @l_EmailBody='' 


      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert1') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert1 AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT     

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader)  

      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                     @p_HtmlString=@l_EmailBody 
      DROP TABLE #TAlert
      DROP TABLE #TAlert1


   END
   
   DROP TABLE #MissingIndexes
   DROP TABLE #TScript

END

GO
/****** Object:  StoredProcedure [dbo].[utility_INDEX_GetUnusedIndexForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Find unused indexes for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_INDEX_GetUnusedIndexForAllDatabase] 
AS
BEGIN

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET NOCOUNT ON

   SELECT DB_NAME() AS DatabaseName,
          SCHEMA_NAME(o.Schema_ID) AS SchemaName,
          OBJECT_NAME(s.[object_id]) AS TableName,
          i.name AS IndexName,
          s.user_updates,
          s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
     INTO #TempUnusedIndexes
     FROM sys.dm_db_index_usage_stats s
          INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
          INNER JOIN sys.objects o ON i.object_id = o.object_id
    WHERE 1=2

   EXEC sp_MSforeachdb 'USE [?];
   INSERT INTO #TempUnusedIndexes
   SELECT TOP 20
          DB_NAME() AS DatabaseName,
          SCHEMA_NAME(o.Schema_ID) AS SchemaName,
          OBJECT_NAME(s.[object_id]) AS TableName,
          i.name AS IndexName,
          s.user_updates,
          s.system_seeks + s.system_scans + s.system_lookups AS [System usage]
     FROM sys.dm_db_index_usage_stats s
          INNER JOIN sys.indexes i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
          INNER JOIN sys.objects o ON i.object_id = o.object_id
    WHERE s.database_id = DB_ID()
          AND OBJECTPROPERTY(s.[object_id], ''IsMsShipped'') = 0
          AND user_seeks = 0
          AND user_scans = 0
          AND user_lookups = 0
          AND i.name IS NOT NULL
 ORDER BY user_updates DESC'

   DECLARE @DisableOrDrop INT
   DECLARE @DisableIndexesSQL NVARCHAR(MAX)
   SET @DisableOrDrop = 1
   SET @DisableIndexesSQL = ''

   SELECT CASE
             WHEN @DisableOrDrop = 1
               THEN CHAR(10) + 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON '
                    + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                    + QUOTENAME(TableName) + ' DISABLE;'
               ELSE CHAR(10) + 'DROP INDEX ' + QUOTENAME(IndexName) + ' ON '
                    + QUOTENAME(DatabaseName) + '.'+ QUOTENAME(SchemaName) + '.'
                    + QUOTENAME(TableName) 
          END AS MaintanceScript INTO #TMS
     FROM #TempUnusedIndexes



   SELECT * FROM #TempUnusedIndexes
   SELECT * FROM #TMS

   DROP TABLE #TempUnusedIndexes
   DROP TABLE #TMS

END

GO
/****** Object:  StoredProcedure [dbo].[utility_IO_GetIOStallAtFileLevelForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Finding IO stall at file level
-- =============================================
CREATE PROCEDURE [dbo].[utility_IO_GetIOStallAtFileLevelForAllDatabase] 
AS
BEGIN

   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
   SET NOCOUNT ON

   SELECT DB_NAME(database_id) AS [DatabaseName],
          file_id,
          SUM(CAST(io_stall / 1000.0 AS DECIMAL(20,2))) AS [IO stall (secs)],
          SUM(CAST(num_of_bytes_read / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [IO read (MB)],
          SUM(CAST(num_of_bytes_written / 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [IO written (MB)],
          SUM(CAST((num_of_bytes_read + num_of_bytes_written)/ 1024.0 / 1024.0 AS DECIMAL(20,2))) AS [TotalIO (MB)]
     FROM sys.dm_io_virtual_file_stats(NULL, NULL)
 GROUP BY database_id, file_id
 ORDER BY [IO stall (secs)] DESC

END

GO
/****** Object:  StoredProcedure [dbo].[utility_IO_GetIOWaitAtFileLevelForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Finding database file io waits
-- =============================================
CREATE PROCEDURE [dbo].[utility_IO_GetIOWaitAtFileLevelForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT DB_NAME(database_id) AS [Database Name] ,
          file_id AS [File ID],
          io_stall_read_ms AS [Total Read Waits (ms)],
          num_of_reads AS [Number of Reads],
          CAST(io_stall_read_ms / ( 1.0 + num_of_reads ) AS NUMERIC(10, 1)) AS [Average Read Wait (ms)] ,
          io_stall_write_ms AS [Total Write Waits (ms)],
          num_of_writes AS [Number of Writes],
          CAST(io_stall_write_ms / ( 1.0 + num_of_writes ) AS NUMERIC(10, 1)) AS [Average Write Wait (ms)] ,
          io_stall_read_ms + io_stall_write_ms AS [Total I/O Waits (ms)] ,
          num_of_reads + num_of_writes AS [Number of I/O Operations] ,
          CAST(( io_stall_read_ms + io_stall_write_ms ) / ( 1.0 + num_of_reads + num_of_writes)AS NUMERIC(10,1)) AS [Average I/O Wait (ms)]
     FROM sys.dm_io_virtual_file_stats(NULL, NULL)
  ORDER BY [Average I/O Wait (ms)] DESC ;

END

GO
/****** Object:  StoredProcedure [dbo].[utility_IO_GetTotalReadWriteTimesForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Finding the total reads and writes for each database
-- =============================================
CREATE PROCEDURE [dbo].[utility_IO_GetTotalReadWriteTimesForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT DB_NAME(qt.dbid) AS DatabaseName,
          SUM(qs.total_logical_reads) AS [Total Reads],
          SUM(qs.total_logical_writes) AS [Total Writes]
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
 GROUP BY DB_NAME(qt.dbid)
 ORDER BY [Total Reads] DESC,[Total Writes] DESC

END

GO
/****** Object:  StoredProcedure [dbo].[utility_JOB_GetJobSummaryInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Purpose: get job summary list    
-- Create Date: 08/29/2012     
-- Last Update: 08/29/2012  
-- Author:      Alex Tian  
CREATE PROCEDURE [dbo].[utility_JOB_GetJobSummaryInfo] 
   @p_IsAlert             BIT=0, --Report need more restrict
   @p_JobName             VARCHAR(255) = NULL,  -- Optional job name filter  
   @p_ShowDisabled        BIT = 0, -- Include disabled jobs?  
   @p_ShowUnscheduled     BIT = 0, -- Include Unscheduled jobs?  
   @p_JobThresholdSec     INT = 0, -- If positive, show only the jobs with LAST duration above this.  
   @p_AvgExecThresholdSec INT = 0  -- If positive, show only the jobs with AVERAGE duration above this.  

AS 
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT * INTO #TJob
     FROM ( SELECT JobName, 
                   ISNULL(LastStep,'') LastStep,
                   CASE WHEN StartDate IS NOT NULL AND FinishDate IS NULL THEN 'Running' 
                        WHEN Enabled = 0 THEN 'Disabled' 
                        WHEN StepCount = 0 THEN 'No steps' 
                        WHEN RunStatus IS NOT NULL THEN RunStatus 
                        WHEN ScheduleCount = 0 THEN 'Not scheduled' 
                        ELSE 'UNKNOWN' END Info,
                   DatabaseName, 
                   Enabled, 
                   ScheduleCount, 
                   StepCount, 
                   StartDate, 
                   FinishDate, 
                   DurationSec, 
                   RIGHT('0'+convert(varchar(5),DurationSec/3600),2)+':'+
                   RIGHT('0'+convert(varchar(5),DurationSec%3600/60),2)+':'+ 
                   RIGHT('0'+convert(varchar(5),(DurationSec%60)),2) DurationSecFormatted, 
                   avgDurationSec,
                   RIGHT('0'+convert(varchar(5),avgDurationSec/3600),2)+':'+
                   RIGHT('0'+convert(varchar(5),avgDurationSec%3600/60),2)+':'+ 
                   RIGHT('0'+convert(varchar(5),(avgDurationSec%60)),2) avgDurationSecFormatted, 
                   CASE WHEN (DurationSec IS NULL OR ISNULL(avgDurationSec, 0) = 0) THEN 0 
                        ELSE CONVERT(DECIMAL(18,2), (100*CAST(DurationSec AS DECIMAL)) / CAST (avgDurationSec as DECIMAL)) END AS DurationRatio, 
                   NextRunDate, 
                   StepCommand, 
                   HistoryMessage 
              FROM ( SELECT j.name JobName,
                            j.enabled Enabled, 
                            (SELECT COUNT(1) FROM msdb..sysjobschedules jss WHERE jss.job_id = j.job_id) ScheduleCount, 
                            (SELECT COUNT(1) FROM msdb..sysjobsteps jps WHERE jps.job_id = j.job_id) StepCount, 
                            ls1.job_history_id HistoryID, 
                            ls1.start_execution_date StartDate, 
                            ls1.stop_execution_date FinishDate, 
                            ls1.last_executed_step_id LastStepID, 
                            DATEDIFF(SECOND, 
                                     ls1.start_execution_date, 
                                     CASE WHEN ls1.stop_execution_date IS NULL THEN GETDATE() 
                                     ELSE ls1.stop_execution_date END) DurationSec, 
                            ISNULL(avgSec, 0) avgDurationSec, 
                            ls1.next_scheduled_run_date NextRunDate, 
                            st.step_name LastStep, 
                            st.command StepCommand, 
                            st.database_name DatabaseName, 
                            h.message HistoryMessage, 
                            CASE WHEN h.job_id IS NULL THEN 'Never Run' 
                            ELSE CASE h.run_status WHEN 0 THEN 'Failed' 
                                                   WHEN 1 THEN 'Succeeded' 
                                                   WHEN 2 THEN 'Retry' 
                                                   WHEN 3 THEN 'Canceled' END END RunStatus, 
                            h.run_date rawRunDate, 
                            h.run_time rawRunTime, 
                            h.run_duration rawRunDuration 
                       FROM msdb..sysjobactivity ls1 (NOLOCK) 
                            INNER JOIN msdb..sysjobs j (NOLOCK) ON ls1.job_id = j.job_id 
                            INNER JOIN (SELECT job_id JobID, 
                                               MAX(session_id) LastSessionID 
                                          FROM msdb..sysjobactivity (NOLOCK) 
                                      GROUP BY job_id ) ls2 ON ls1.job_id = ls2.JobID 
                                                               AND ls1.session_id = ls2.LastSessionID 
                            LEFT OUTER JOIN msdb..sysjobsteps st (NOLOCK) ON st.job_id = j.job_id 
                                                                     AND ls1.last_executed_step_id = st.step_id 
                            LEFT OUTER JOIN msdb..sysjobhistory h (NOLOCK) ON h.instance_id = ls1.job_history_id 
                            LEFT OUTER JOIN ( SELECT j.job_id JobID, 
                                                     SUM(h.avgSecs) avgSec 
                                                FROM msdb..sysjobs j (NOLOCK) 
                                                     INNER JOIN ( SELECT job_id, 
                                                                         step_id, 
                                                                         AVG(run_duration/10000*3600 + 
                                                                             run_duration%10000/100*60 + 
                                                                             run_duration%100) avgSecs 
                                                                    FROM msdb..sysjobhistory 
                                                                   WHERE step_id > 0 
                                                                 AND run_status = 1 
                                                               GROUP BY job_id,
                                                                        step_id ) h ON j.job_id = h.job_id 
                                             GROUP BY j.job_id ) jobavg ON jobavg.JobID = j.job_id )jj 
             WHERE (@p_ShowDisabled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR Enabled = 1) 
                   AND (@p_JobName IS NULL OR JobName = @p_JobName) 
                   AND (@p_ShowUnscheduled = 1 OR (StartDate IS NOT NULL AND FinishDate IS NULL) OR ScheduleCount > 0) 
                   AND (@p_JobThresholdSec = 0 OR DurationSec >= @p_JobThresholdSec) 
                   AND (@p_AvgExecThresholdSec = 0 OR avgDurationSec >= @p_AvgExecThresholdSec))x 
 ORDER BY CASE Info 
          WHEN 'Running' THEN 0 
          WHEN 'Failed' THEN 1 
          WHEN 'Retry' THEN 2
          WHEN 'Succeeded' THEN 3
          WHEN 'Canceled' THEN 4
          WHEN 'No steps' THEN 5 
          WHEN 'Not scheduled' THEN 6
          WHEN 'Disabled' THEN 7
          WHEN 'Never Run' THEN 8
          WHEN 'UNKNOWN' THEN -1
          ELSE -2 END, 
          NextRunDate, 
          JobName


   IF @p_IsAlert=0
      SELECT * FROM #TJob

   --send alert report part 
   IF EXISTS(SELECT 1 FROM #TJob WHERE @p_IsAlert=1 AND Info IN ('Failed','UNKNOWN','Never Run','Disabled','No steps','Canceled') )
   BEGIN 
      SELECT * INTO #TAlert FROM #TJob WHERE Info IN ('Failed','UNKNOWN','Never Run','Disabled','No steps','Canceled')

      DECLARE @l_Html NVARCHAR(max) 
      DECLARE @l_HHeader NVARCHAR(max) 
      DECLARE @l_QColumn NVARCHAR(max) 
      DECLARE @l_TColumn NVARCHAR(max) 
      DECLARE @l_TQuery NVARCHAR(max) 
      DECLARE @l_EmailSubject NVARCHAR(200) 
      DECLARE @l_EmailBody NVARCHAR(max) 

      SET @l_Html ='' 
      SET @l_HHeader ='' 
      SET @l_QColumn ='' 
      SET @l_TColumn ='' 
      SET @l_TQuery ='' 
      SET @l_EmailSubject='Schedule Job Summary' 
      SET @l_EmailBody='' 

      SELECT @l_TColumn = @l_TColumn + name + ',' 
        FROM tempdb.sys.columns 
       WHERE object_id = object_id('tempdb..#TAlert') 

      SET @l_TColumn=LEFT(@l_TColumn, LEN(@l_TColumn) - 1) 
      SET @l_QColumn=dbo.fn_SplitStringToQueryColumn(@l_TColumn, ',') 
      SET @l_HHeader=dbo.fn_SplitStringToHtmlHeader(@l_TColumn, ',') 
      SET @l_TQuery = 'SET @Html = ( SELECT ' + @l_QColumn + 
                                     ' FROM #TAlert AS TR 
                                        FOR XML RAW(''TR'') ,ROOT(''TABLE''), ELEMENTS )'

      EXECUTE sp_executesql @l_TQuery, 
                            N'@Html NVARCHAR(MAX) OUTPUT', 
                            @l_Html OUTPUT 

      SET @l_EmailBody=dbo.fn_FormatHtmlTable(@l_Html, @l_HHeader) 
      EXECUTE dbo.utility_EmailHtmlStringToHtmlTable @p_Subject=@l_EmailSubject, 
                                                       @p_HtmlString=@l_EmailBody  

      DROP TABLE #TAlert
   END

   DROP TABLE #TJob
END

GO
/****** Object:  StoredProcedure [dbo].[utility_LOG_GetCustomSQLErrorLog]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get error log from application
-- =============================================

CREATE PROCEDURE [dbo].[utility_LOG_GetCustomSQLErrorLog]
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_TimeStart DATETIME 
   SET @l_TimeStart=DATEADD(DAY,-1,GETDATE());

   SELECT * 
     FROM dbo.ErrorLog
    WHERE DATEDIFF(DAY,Time,GETDATE())<=1
 ORDER BY DBName,Time DESC

END
GO
/****** Object:  StoredProcedure [dbo].[utility_LOG_GetSQLAgentLog]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get sql server error log
-- 1.Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc... 
-- 2.Log file type: 1 or NULL = error log, 2 = SQL Agent log 
-- 3.Search string 1: String one you want to search for 
-- 4.Search string 2: String two you want to search for to further refine the results
-- =============================================

CREATE PROCEDURE [dbo].[utility_LOG_GetSQLAgentLog]
(
   @p_p1     INT = 0, 
   @p_p2     INT = 2, 
   @p_p3     VARCHAR(255) = NULL, 
   @p_p4     VARCHAR(255) = NULL
) 
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_TimeStart DATETIME 
   DECLARE @l_TimeEnd   DATETIME
   
   SET @l_TimeStart=DATEADD(DAY,-1,GETDATE());
   SET @l_TimeEnd=GETDATE();



   IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1) 
   BEGIN 
      RAISERROR(15003,-1,-1, N'securityadmin') 
      RETURN (1) 
   END 
    
   IF (@p_p2 IS NULL) 
       EXEC master.sys.xp_readerrorlog @p_p1 
   ELSE 
       EXEC master.sys.xp_readerrorlog @p_p1,@p_p2,@p_p3,@p_p4,@l_TimeStart,@l_TimeEnd
END


GO
/****** Object:  StoredProcedure [dbo].[utility_LOG_GetSQLServerErrorLog]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get sql server error log
-- 1.Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc... 
-- 2.Log file type: 1 or NULL = error log, 2 = SQL Agent log 
-- 3.Search string 1: String one you want to search for 
-- 4.Search string 2: String two you want to search for to further refine the results
-- =============================================

CREATE PROCEDURE [dbo].[utility_LOG_GetSQLServerErrorLog]
(
   @p_p1     INT = 0, 
   @p_p2     INT = 1, 
   @p_p3     VARCHAR(255) = NULL, 
   @p_p4     VARCHAR(255) = NULL
) 
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_TimeStart DATETIME 
   DECLARE @l_TimeEnd   DATETIME
   
   SET @l_TimeStart=DATEADD(DAY,-1,GETDATE());
   SET @l_TimeEnd=GETDATE();



   IF (NOT IS_SRVROLEMEMBER(N'securityadmin') = 1) 
   BEGIN 
      RAISERROR(15003,-1,-1, N'securityadmin') 
      RETURN (1) 
   END 
    
   IF (@p_p2 IS NULL) 
       EXEC master.sys.xp_readerrorlog @p_p1 
   ELSE 
       EXEC master.sys.xp_readerrorlog @p_p1,@p_p2,@p_p3,@p_p4,@l_TimeStart,@l_TimeEnd
END


GO
/****** Object:  StoredProcedure [dbo].[utility_LOG_GetWeeklyEventLog]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get the latest event log
-- =============================================

CREATE PROCEDURE [dbo].[utility_LOG_GetWeeklyEventLog]
AS
BEGIN
   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   DECLARE @l_BeginDate  SMALLDATETIME
   DECLARE @l_EndDate    SMALLDATETIME
   DECLARE @l_DateNow    SMALLDATETIME
   
   SET @l_DateNow = CONVERT(VARCHAR(10), GETDATE(), 120)
   SET @l_BeginDate = DATEADD(d, - DATEPART(dw, @l_DateNow) - 7, @l_DateNow)
   SET @l_EndDate   = DATEADD(d, - DATEPART(dw, @l_DateNow), @l_DateNow)

   
   SELECT EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(128)') AS DatabaseName,
          EventData.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(128)') AS EventType,
          EventData.value('(/EVENT_INSTANCE/UserName)[1]', 'varchar(128)') AS UserName,
          EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)') AS LoginName,
          EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS PostTime,
          EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') AS CommandText
     FROM dbo.EventLog
    WHERE EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') >= @l_BeginDate
          AND EventData.value('(/EVENT_INSTANCE/PostTime)[1]', 'smalldatetime') < @l_EndDate 
 

END
GO
/****** Object:  StoredProcedure [dbo].[utility_MEMORY_GetBufferCacheHitRatio]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get the buffer cache hit ratio
-- =============================================
CREATE PROCEDURE [dbo].[utility_MEMORY_GetBufferCacheHitRatio] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT (CAST(SUM(CASE LTRIM(RTRIM(counter_name)) 
                    WHEN 'Buffer cache hit ratio' 
                    THEN CAST(cntr_value AS INTEGER) 
                    ELSE NULL END) AS FLOAT) / 
           CAST(SUM(CASE LTRIM(RTRIM(counter_name))
                    WHEN 'Buffer cache hit ratio base' 
                    THEN CAST(cntr_value AS INTEGER)
                    ELSE NULL END) AS FLOAT)) * 100 AS BufferCacheHitRatio
     FROM sys.dm_os_performance_counters WITH (NOLOCK)
    WHERE [object_name] LIKE 'SQLServer:Buffer Manager%'
          AND [counter_name] LIKE 'Buffer cache hit ratio%'

END

GO
/****** Object:  StoredProcedure [dbo].[utility_MEMORY_GetMemoryUsedAtDatabaseLevelForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get memory used for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_MEMORY_GetMemoryUsedAtDatabaseLevelForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName,
          CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2)) AS [Size (MB)]
     FROM sys.dm_os_buffer_descriptors
 GROUP BY database_id
 ORDER BY [Size (MB)] DESC ,DatabaseName


END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetExtendedWhoProcedure]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Execute extended who procedure
-- Some Sample Usage:
-- EXEC dbo.query_GetExtendedWhoProcedure;
-- EXEC dbo.query_GetExtendedWhoProcedure 'active';
-- EXEC dbo.query_GetExtendedWhoProcedure 'active',@IncludeSQL = 1,@Brief = 1;
-- EXEC dbo.query_GetExtendedWhoProcedure 'active', @IncludeSelf = 1;
-- EXEC dbo.query_GetExtendedWhoProcedure @Loginame = 'sa';
-- EXEC dbo.query_GetExtendedWhoProcedure @OrderBy = 'CPU_Time';
-- EXEC dbo.query_GetExtendedWhoProcedure @IncludeSystemSPIDs = 1;
-- EXEC dbo.query_GetExtendedWhoProcedure @ShowBlockersOnly = 1;
-- EXEC dbo.query_GetExtendedWhoProcedure @SearchSPID = 58;
-- EXEC dbo.query_GetExtendedWhoProcedure @SearchDBName = 'SomeDatabaseName';
-- EXEC dbo.query_GetExtendedWhoProcedure @IncludeSQL = 1,@SearchCommand = '%update%sometable%';
-- =============================================

CREATE PROCEDURE [dbo].[utility_QUERY_GetExtendedWhoProcedure]

    -- only show logins with this name
    
    @Loginame            VARCHAR(255) = 'active', 

    -- ShowBlockersOnly will reduce resultset to
    -- those that are blocking or being blocked
    
    @ShowBlockersOnly        BIT = 0,
    
    -- optional search conditions
    
    @SearchSPID        INT = NULL,
    @SearchDBName        NVARCHAR(255) = '%',
    @SearchHostName        NVARCHAR(255) = '%',
    @SearchCommand        NVARCHAR(255) = '%',
    @SearchIP            VARCHAR(16)   = '%',
    @SearchProgramName        NVARCHAR(255) = '%',
    
    -- unlikely you want to see yourself,
    -- but stranger things can happen
    
    @IncludeSelf        BIT = 0,
    
    -- show system SPIDs?
    
    @IncludeSystemSPIDs        BIT = 0,
    
    -- augment exec_sql info with DBCC INPUTBUFFER
    
    @IncludeSQL        BIT = 1,
    
    -- displays only the first 255 characters of SQL
    
    @Brief            BIT = 0,

    -- optional sorting... allowed values:
    -- 'Elapsed_Time', 'CPU_Time', 
    -- 'Logical_Reads', 'Reads', 'Writes'
    
    @OrderBy            VARCHAR(32) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET ANSI_WARNINGS OFF;

    DECLARE
        @spid        INT,
        @sql        NVARCHAR(MAX),
        @briefsize        SMALLINT;
    
    -- used in conjunction with @Brief and @IncludeSQL
    SET @briefsize = 255;
    
    -- override @IncludeSQL otherwise nothing to search
    IF @SearchCommand != '%'
        SET @IncludeSQL = 1;

    SELECT 
    
        [Spid] = s.[session_id],

        [Status] = MAX(UPPER(COALESCE
            (
                r.[status],
                tt.[task_state],
                s.[status],
                ''
            ))),
        
        [Command] = MAX(COALESCE
            (
                r.[command],
                r.[wait_type],
                wt.[wait_type],
                r.[last_wait_type], 
                ''
            )),

        [Blocked_By] = MAX(CONVERT(VARCHAR(12), COALESCE
            (
                RTRIM(NULLIF(r.[blocking_session_id], 0)),
                '  .'
            ))),

        [Database_Name] = MAX(DB_NAME(COALESCE
            (
                tl.[database_id],
                r.[database_id],
                t.[database_id], 
                ''
            ))),

        [Logical_Reads] = MAX(COALESCE
            (
                NULLIF(r.[logical_reads], 0),
                s.[logical_reads],
                0
            )),
            
        [Reads] = MAX(COALESCE
            (
                NULLIF(r.[reads], 0),
                NULLIF(s.[reads], 0),
                c.[num_reads],
                0
            )),
        
        [Writes] = MAX(COALESCE
            (
                NULLIF(r.[writes], 0),
                NULLIF(s.[writes], 0),
                c.[num_writes],
                0
            )),

        [CPU_Time] = MAX(COALESCE
            (
                NULLIF(tt.[CPU_Time], 0),
                NULLIF(r.[cpu_time], 0), 
                NULLIF(s.[cpu_time], 0),
                s.[total_scheduled_time], 
                0
            )),

        [Elapsed_Time] = MAX(COALESCE
            (
                r.[total_elapsed_time],
                s.[total_elapsed_time]
            )),

        [Row_Count] = MAX(s.[row_count]),
        
        [Memory_In_Pages] = MAX(COALESCE
            (
                NULLIF(r.[granted_query_memory], 0),
                s.[memory_usage],
                0
            )),

        [Tran_Count] = MAX(COALESCE
            (
                t.[trancount], 
                0
            )),
        
        [Lock_Count] = MAX(COALESCE
            (
                tl.[lockcount], 
                0
            )),
        
        [Login_Name] = s.[login_name],

        [Host_Name] = MAX(COALESCE
            (
                s.[host_name],
                '  .'
            )),

        [IP_Address] = MAX(COALESCE
            (
                c.[client_net_address], 
                '  .'
            )),

        [Program_Name] = MAX(COALESCE
            (
                s.[program_name], 
                ''
            )),

        [Login_Time] = MAX(COALESCE
            (
                s.[login_time],
                c.[connect_time]
            )),
        
        [Last_Request] = MAX(COALESCE
            (
                r.[start_time],
                s.[last_request_start_time]
            )),
            
        [Handle] = MAX(COALESCE
            (
                r.[sql_handle],
                c.[most_recent_sql_handle]
            )),

        [Exec_SQL] = CONVERT(NVARCHAR(MAX), N'')
    INTO
        #spids

    FROM
        sys.dm_exec_sessions s

    LEFT OUTER JOIN
        sys.dm_exec_connections c
        ON c.[session_id] = s.[session_id]

    LEFT OUTER JOIN 
        sys.dm_exec_requests r
        ON s.[session_id] = r.[session_id]

    LEFT OUTER JOIN
    (
        SELECT
            [session_id],
            [database_id] = MAX([database_id]),
            [trancount] = COUNT(*)
        FROM
            sys.dm_tran_session_transactions t
        INNER JOIN
            sys.dm_tran_database_transactions dt
        ON
            t.[transaction_id] = dt.[transaction_id]
        GROUP BY
            [session_id]
    ) t
        ON s.[session_id] = t.[session_id]

    LEFT OUTER JOIN
    (
        SELECT 
            [request_session_id],
            [database_id] = MAX([resource_database_id]),
            [lockcount] = COUNT(*)
        FROM
            sys.dm_tran_locks WITH (NOLOCK)
        GROUP BY
            [request_session_id]
    ) tl
    ON
        s.[session_id] = tl.[request_session_id]

    LEFT OUTER JOIN
        sys.dm_os_waiting_tasks wt
    ON 
        s.[session_id] = wt.[session_id]

    LEFT OUTER JOIN
    (
        SELECT
            ot.[session_id],
            ot.[task_state],
            [CPU_Time] = MAX(oth.[usermode_time])
        FROM
            sys.dm_os_tasks ot
        INNER JOIN
            sys.dm_os_workers ow
        ON
            ot.[worker_address] = ow.[worker_address]
        INNER JOIN
            sys.dm_os_threads oth
        ON
            ow.[thread_address] = oth.[thread_address]
        GROUP BY
            ot.[session_id],
            ot.[task_state]
    ) tt
    ON
        s.[session_id] = tt.[session_id]

    WHERE
        s.[login_name] = COALESCE
        (
            NULLIF(@Loginame, 'active'),
            s.[login_name]
        )
    
    GROUP BY
        s.[session_id],
        s.[login_name];


    -- delete rows we're not interested in
    
    IF LOWER(@Loginame) = 'active'
    BEGIN
        DELETE #spids 
            WHERE UPPER([Status]) = 'SLEEPING'
            OR UPPER([Command]) = 'AWAITING COMMAND';
    END    

    IF @ShowBlockersOnly = 1
    BEGIN
        DELETE s1
            FROM #spids s1
            WHERE s1.[Blocked_By] = '  .'
            AND NOT EXISTS
            (
                SELECT 1
                FROM
                    #spids
                WHERE
                    [Blocked_By] = RTRIM(s1.[Spid])
            );
    END
    
    IF @SearchSPID IS NOT NULL
    BEGIN
        DELETE #spids
        WHERE [Spid] != @SearchSPID;
    END
    
    IF @SearchDBName != '%'
    BEGIN
        DELETE #spids
        WHERE [Database_Name] IS NULL
        OR [Database_Name] NOT LIKE @SearchDBName;
    END
    
    IF @SearchHostName != '%' 
        OR @SearchProgramName != '%'
        OR @SearchIP != '%'
    BEGIN
        DELETE #spids
        WHERE [Host_Name] NOT LIKE @SearchHostName
        OR [Program_Name] NOT LIKE @SearchProgramName
        OR [IP_Address] NOT LIKE @SearchIP;
    END

    IF @IncludeSelf = 0
    BEGIN
        DELETE #spids
        WHERE [Spid] = @@SPID;
    END
    
    IF @IncludeSystemSPIDs = 0
    BEGIN
        DELETE #spids
        WHERE [Spid] <= 50;
    END

    CREATE TABLE #dbcc
    (
        a NVARCHAR(500),
        b NVARCHAR(500),
        [Input_Buffer] NVARCHAR(MAX),
        [Spid] INT NULL
    );

    IF @IncludeSQL = 1
    BEGIN
        UPDATE #spids
            SET [Exec_SQL] = 
            (
                SELECT [text] 
                FROM sys.dm_exec_sql_text([Handle])
            );
        
        DECLARE dbcc_cursor CURSOR 
            LOCAL FORWARD_ONLY STATIC READ_ONLY
        FOR 
            SELECT [Spid]
                FROM #spids;

        OPEN dbcc_cursor;
        
        FETCH NEXT FROM dbcc_cursor INTO @spid;
            
        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @sql = 'DBCC INPUTBUFFER('
                +RTRIM(@spid)+') 
                WITH NO_INFOMSGS;';

            INSERT #dbcc(a,b,[Input_Buffer]) 
                EXEC sp_executesql @sql;

            UPDATE #dbcc
                SET [Spid] = @spid
                WHERE [Spid] IS NULL;

            FETCH NEXT FROM dbcc_cursor INTO @spid;
        END
            
        CLOSE dbcc_cursor;
        DEALLOCATE dbcc_cursor;

        IF @SearchCommand != '%'
        BEGIN
            DELETE #dbcc 
            WHERE COALESCE([Input_Buffer], '') 
            NOT LIKE @SearchCommand;
            
            DELETE #spids
            WHERE COALESCE([Exec_SQL], '')
            NOT LIKE @SearchCommand;
        END

        IF @Brief = 1
        BEGIN
            UPDATE #dbcc 
            SET [Input_Buffer] = 
            COALESCE(LEFT([Input_Buffer], @briefsize), '');
                
            UPDATE #spids 
            SET [Exec_SQL] = 
            COALESCE(LEFT([Exec_SQL], @briefsize), '');
        END
    END
        
    SELECT
        s.[Spid],
        s.[Status],
        s.[Command],
        s.[Blocked_By],
        s.[Database_Name],
        s.[Logical_Reads],
        s.[Reads],
        s.[Writes],
        s.[CPU_Time],
        s.[Elapsed_Time],
        s.[Row_Count],
        s.[Memory_In_Pages],
        s.[Tran_Count],
        s.[Lock_Count],
        s.[Login_Name],
        s.[Host_Name],
        s.[IP_Address],
        s.[Program_Name],
        s.[Login_Time],
        s.[Last_Request],
        s.[Exec_SQL], 
        [Input_Buffer] = COALESCE(d.[Input_Buffer], '')
    FROM
        #spids s
    LEFT OUTER JOIN
        #dbcc d
    ON
        s.[Spid] = d.[Spid]
    ORDER BY
        CASE @OrderBy
            WHEN 'Elapsed_Time'    THEN s.[Elapsed_Time]
            WHEN 'CPU_Time'    THEN s.[CPU_Time] 
            WHEN 'Logical_Reads'    THEN s.[Logical_Reads] 
            WHEN 'Reads'    THEN s.[Reads] 
            WHEN 'Writes'    THEN s.[Writes] 
        END DESC,
        s.[Spid];
    
    DROP TABLE #dbcc, #spids;
END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetTopLongestBlockedQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    The queries spend the longest time being blocked for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetTopLongestBlockedQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT TOP 20
          CAST((qs.total_elapsed_time - qs.total_worker_time) /1000000.0 AS DECIMAL(28,2)) AS [Total time blocked (s)],
          CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],
          CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],
          qs.execution_count,
          CAST((qs.total_elapsed_time - qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [Blocking average (s)],
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                      ELSE qs.statement_end_offset
                                                                  END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName,
          qp.query_plan
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
 ORDER BY [Total time blocked (s)] DESC
END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetTopLongestTimeQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    The queries that take the longest time to run for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetTopLongestTimeQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT TOP 20
          CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))AS [Total Duration (s)],
          CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28, 2))AS [% CPU],
          CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],
          qs.execution_count,
          CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count AS DECIMAL(28, 2))AS [Average Duration (s)],
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName,
          qp.query_plan
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
 ORDER BY qs.total_elapsed_time DESC
END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetTopMostCPUCostQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get the queries that use the most CPU for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetTopMostCPUCostQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED



   SELECT TOP 20
          CAST((qs.total_worker_time) / 1000000.0AS DECIMAL(28,2)) AS [Total CPU time (s)],
          CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time AS DECIMAL(28,2)) AS [% CPU],
          CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 /qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting],
          qs.execution_count,
          CAST((qs.total_worker_time) / 1000000.0/ qs.execution_count AS DECIMAL(28, 2)) AS [CPU time average (s)],
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName,
          qp.query_plan
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    WHERE qs.total_elapsed_time > 0
 ORDER BY [Total CPU time (s)] DESC

END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetTopMostIOCostQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Get the queries that use the most I/O
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetTopMostIOCostQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT TOP 20
          [Total IO] = (qs.total_logical_reads + qs.total_logical_writes),
          [Average IO] = (qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count,
          qs.execution_count,
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName,
          qp.query_plan
      FROM sys.dm_exec_query_stats qs
           CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
           CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
   ORDER BY [Total IO] DESC

END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetTopMostOftenExecutedQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    The queries that have been executed the most often
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetTopMostOftenExecutedQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


   SELECT TOP 20
          qs.execution_count,
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid) AS DatabaseName,
          qp.query_plan
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
          CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
 ORDER BY qs.execution_count DESC;

END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetTopMostRecompiledQueryForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description:    Determining your most-recompiled queries
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetTopMostRecompiledQueryForAllDatabase] 
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT TOP 20
          qs.plan_generation_num,
          qs.total_elapsed_time,
          qs.execution_count,
          SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,((CASE WHEN qs.statement_end_offset = -1
                                                                      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                 ELSE qs.statement_end_offset
                                                                 END - qs.statement_start_offset)/2) + 1) AS [Individual Query],
          qt.text AS [Parent Query],
          DB_NAME(qt.dbid),
          qs.creation_time,
          qs.last_execution_time
     FROM sys.dm_exec_query_stats qs
          CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
 ORDER BY plan_generation_num DESC

END

GO
/****** Object:  StoredProcedure [dbo].[utility_QUERY_GetWhatProcessIsRunningForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Get what process is running for all database
-- =============================================
CREATE PROCEDURE [dbo].[utility_QUERY_GetWhatProcessIsRunningForAllDatabase]
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT  der.session_id AS SessionID,
           der.status AS Status,
           des.login_name AS Login,
           des.[host_name] AS HostName,
           DB_NAME(der.database_id) AS DatabaseName,
           des.[program_name] AS Program,
           der.command AS Command,
           dest.text AS CommandText ,
           des.cpu_time AS CPUTime,
           des.memory_usage*8 AS MemoryUsage_KB,
           des.reads AS Reads,
           des.writes AS Writes,
           des.logical_reads AS LogicalReads,
           des.total_scheduled_time AS TotalScheduleTime,
           des.login_time AS LoginTime,
           dec.client_net_address AS ClientNetAddress,
           des.client_interface_name AS ClientInterfaceName
      FROM sys.dm_exec_requests der
           INNER JOIN sys.dm_exec_connections dec ON der.session_id = dec.session_id
           INNER JOIN sys.dm_exec_sessions des ON des.session_id = der.session_id
           CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
  ORDER BY CPUTime DESC ,MemoryUsage_KB DESC

END
GO
/****** Object:  StoredProcedure [dbo].[utility_SPACE_GetDiskSpaceForServer]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Get disk space
-- =============================================
CREATE PROCEDURE [dbo].[utility_SPACE_GetDiskSpaceForServer] 
AS
BEGIN
  SET NOCOUNT ON
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

  IF @@microsoftversion / power(2, 24) >= 9
   BEGIN
      EXEC sp_configure 'show advanced options', 1
      RECONFIGURE WITH OVERRIDE
      EXEC sp_configure 'xp_cmdshell', 1
      RECONFIGURE WITH OVERRIDE
   END

   DECLARE @hr int
   DECLARE @fso int
   DECLARE @drive char(1)
   DECLARE @odrive int
   DECLARE @TotalSize varchar(20)
   DECLARE @MB bigint ; SET @MB = 1048576

   CREATE TABLE #drives (drive char(1) PRIMARY KEY,
                         FreeSpace int NULL,
                         TotalSize int NULL)

   INSERT #drives(drive,FreeSpace) 
   EXEC master.dbo.xp_fixeddrives

   EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
   IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

   DECLARE dcur CURSOR LOCAL FAST_FORWARD
   FOR SELECT drive FROM #drives ORDER BY drive

   OPEN dcur
   FETCH NEXT FROM dcur INTO @drive
   WHILE @@FETCH_STATUS=0
   BEGIN
      EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
      IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
        
      EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
      IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
                        
      UPDATE #drives
         SET TotalSize=@TotalSize/@MB
       WHERE drive=@drive
        
      FETCH NEXT FROM dcur INTO @drive
   END

   CLOSE dcur
   DEALLOCATE dcur

   EXEC @hr=sp_OADestroy @fso
   IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

   SELECT drive,
          FreeSpace as 'Free(MB)',
          TotalSize as 'Total(MB)',
          CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
   FROM #drives ORDER BY drive

   DROP TABLE #drives

   IF @@microsoftversion / power(2, 24) >= 9
   BEGIN
      EXEC sp_configure 'xp_cmdshell', 0
      RECONFIGURE WITH OVERRIDE

      EXEC sp_configure 'show advanced options', 0
      RECONFIGURE WITH OVERRIDE
   END
END




      
          

GO
/****** Object:  StoredProcedure [dbo].[utility_SPACE_GetLogSizeForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Get database log size for each database
-- =============================================
create PROCEDURE [dbo].[utility_SPACE_GetLogSizeForAllDatabase]
AS
BEGIN

SET NOCOUNT ON
--Displaying log space information for all databases
--The following example displays LOGSPACE information for all databases contained in the instance of SQL Server
DBCC SQLPERF(LOGSPACE);

END

GO
/****** Object:  StoredProcedure [dbo].[utility_SPACE_GetSizeAtFileLevelForAllDatabase]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Get database size for each database
-- =============================================
CREATE PROCEDURE [dbo].[utility_SPACE_GetSizeAtFileLevelForAllDatabase]
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   -- Check to see the temp table exists
   IF EXISTS ( SELECT name
                 FROM tempdb..sysobjects
                WHERE name LIKE '#HoldforEachDBSize%' )
       DROP TABLE #HoldforEachDBSize

   CREATE TABLE #HoldforEachDBSize
   (
      DatabaseName NVARCHAR(75) NOT NULL,
      Size         DECIMAL      NOT NULL,
      Name         NVARCHAR(75) NOT NULL,
      Filename     NVARCHAR(90) NOT NULL)


   IF EXISTS ( SELECT name
                 FROM tempdb..sysobjects
                WHERE name LIKE '#FixedDrives%' )
      DROP TABLE #FixedDrives

   CREATE TABLE #FixedDrives
   (
      Drive  CHAR(1) NOT NULL,
      MBFree DECIMAL NOT NULL)


   -- Insert rows from sp_MSForEachDB into temp table
   INSERT INTO #HoldforEachDBSize
          EXEC sp_MSforeachdb 'SELECT ''?''                  AS DatabaseName,
                                   CASE WHEN [?]..sysfiles.size * 8 / 1024 = 0 THEN 1
                                        ELSE [?]..sysfiles.size * 8 / 1024
                                   END                    AS size,
                                   [?]..sysfiles.name     AS Name,
                                   [?]..sysfiles.filename AS Filename
                              FROM [?]..sysfiles'

   INSERT INTO #FixedDrives
          EXEC xp_fixeddrives


     SELECT RTRIM(CAST(DatabaseName AS VARCHAR(75))) AS DatabaseName,
            Drive                                    AS Drive,
            Filename                                 AS Filename,
            CAST(Size AS INT)                        AS Size,
            CAST(MBFree AS VARCHAR(10))              AS MB_Free
       FROM #HoldforEachDBSize
            INNER JOIN #FixedDrives ON LEFT(#HoldforEachDBSize.Filename, 1) = #FixedDrives.Drive
   GROUP BY DatabaseName,
            Drive,
            MBFree,
            Filename,
            CAST(Size AS INT)
   ORDER BY Drive,
            Size DESC


  SELECT Drive AS [Total Data Space Used],
         CAST(SUM(Size) AS VARCHAR(10)) AS [Total Size],
         CAST(MBFree AS VARCHAR(10)) AS MB_Free
    FROM #HoldforEachDBSize
         INNER JOIN #FixedDrives ON LEFT(#HoldforEachDBSize.Filename, 1) = #FixedDrives.Drive
GROUP BY Drive,
         MBFree



   SELECT COUNT(DISTINCT RTRIM(CAST(DatabaseName AS VARCHAR(75)))) AS Database_Count
     FROM #HoldforEachDBSize 



   DROP TABLE #FixedDrives
   DROP TABLE #HoldforEachDBSize

END

GO
/****** Object:  StoredProcedure [dbo].[utility_SendInitDBEmail]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: In order to modify configure easily , we init sp_send_dbmail first  
-- =============================================
CREATE PROCEDURE [dbo].[utility_SendInitDBEmail]
(
   @p_Subject   NVARCHAR(200),
   @p_Body      NVARCHAR(MAX)
)
AS
BEGIN

   SET @p_Subject= '[SQL Auto Email - '+ @@SERVERNAME + '] - '+ISNULL(@p_Subject,'')
   
   EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Mail Public Profile',
                                @recipients = 'alex.tian@morningstar.com;jason.liu@morningstar.com;Kenny.Chen@morningstar.com',
                                @subject = @p_Subject,
                                @body = @p_Body,
                                @body_format = 'HTML'

END

GO
/****** Object:  StoredProcedure [dbo].[utility_TEMPDB_GetTempdbSpaceInfo]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-01
-- Description: Get tempdb total space usage by object type
-- =============================================
CREATE PROCEDURE [dbo].[utility_TEMPDB_GetTempdbSpaceInfo]
AS
BEGIN

   SET NOCOUNT ON
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

   SELECT SUM (user_object_reserved_page_count) * (8.0/1024.0) AS [User Objects (MB)],
          SUM (internal_object_reserved_page_count) * (8.0/1024.0) AS [Internal Objects (MB)],
          SUM (version_store_reserved_page_count) * (8.0/1024.0) AS [Version Store (MB)],
          SUM (mixed_extent_page_count)* (8.0/1024.0) AS [Mixed Extent (MB)],
          SUM (unallocated_extent_page_count)* (8.0/1024.0) AS [Unallocated (MB)]
     FROM sys.dm_db_file_space_usage

END

GO
/****** Object:  UserDefinedFunction [dbo].[fn_FormatHtmlTable]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[fn_FormatHtmlTable]
(
  @p_Html     VARCHAR(MAX),
  @p_HtmlHeader VARCHAR(MAX)
) 
RETURNS VARCHAR(MAX) AS
BEGIN

   DECLARE @l_CSS       NVARCHAR(MAX)
   DECLARE @l_Output    VARCHAR(MAX)
   SET @l_Output='' 
   SET @l_CSS= '<style type="text/css">
                   table.gridtable {
                      font-family: verdana,arial,sans-serif;
                      font-size:11px;
                      color:#333333;
                      border- 1px;
                      border-color: #666666;
                      border-collapse: collapse; }

                   table.gridtable th {
                      border- 1px;
                      padding: 8px;
                      border-style: solid;
                      border-color: #666666;
                      background-color: #dedede;}

                   table.gridtable td {
                      border- 1px;
                      padding: 8px;
                      border-style: solid;
                      border-color: #666666;
                      background-color: #ffffff;}
                  </style>'



   
   SET  @l_Output = @l_CSS + REPLACE(@p_Html,'<TABLE>' ,'<TABLE  class="gridtable">' + @p_HtmlHeader)

  RETURN @l_Output

END

GO
/****** Object:  UserDefinedFunction [dbo].[fn_GetSQLBySPID]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-03
-- Description:    Get sql scirpt by SPID 
-- =============================================
CREATE FUNCTION [dbo].[fn_GetSQLBySPID]
(
  @p_SPID     INT
) 
RETURNS VARCHAR(MAX) AS
BEGIN

   DECLARE @l_Output VARCHAR(MAX)
   SET @l_Output=''

    
 
    DECLARE @sql_handle binary(20), @handle_found bit
    DECLARE @stmt_start int, @stmt_end int

    SELECT @sql_handle = sql_handle,
           @stmt_start = stmt_start/2,
           @stmt_end = CASE WHEN stmt_end = -1 THEN -1 ELSE stmt_end/2 END
      FROM master.dbo.sysprocesses
     WHERE spid = @p_SPID
           AND ecid = 0
  
    SET @l_Output = (SELECT SUBSTRING( text,
                           COALESCE(NULLIF(@stmt_start, 0), 1),
                              CASE @stmt_end WHEN -1 THEN DATALENGTH(text) 
                              ELSE (@stmt_end - @stmt_start) 
                              END) 
                      FROM ::fn_get_sql(@sql_handle))
          
  RETURN @l_Output

END

GO
/****** Object:  UserDefinedFunction [dbo].[fn_SplitStringToHtmlHeader]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_SplitStringToHtmlHeader]
(
  @p_Input     VARCHAR(MAX),
  @p_Delimeter CHAR(1) = ','
) 
RETURNS VARCHAR(MAX) AS
BEGIN

DECLARE @l_Output VARCHAR(MAX)
SET @l_Output=''

 SELECT @l_Output = @l_Output + '<TH>' +  Value + '</TH>' 
     FROM dbo.fn_SplitStringToTable(@p_Input,',')

  SET @l_Output = '<TR>' + @l_Output  + '</TR>'

  RETURN @l_Output

END

GO
/****** Object:  UserDefinedFunction [dbo].[fn_SplitStringToQueryColumn]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-03
-- Description:    split string to sql query column
-- =============================================
CREATE FUNCTION [dbo].[fn_SplitStringToQueryColumn]
(
  @p_Input     VARCHAR(MAX),
  @p_Delimeter CHAR(1) = ','
) 
RETURNS VARCHAR(MAX) AS
BEGIN

   DECLARE @l_Output VARCHAR(MAX)
   SET @l_Output=''

   SELECT @l_Output = @l_Output + 'ISNULL(' + 'CAST(['+Value +'] AS NVARCHAR(MAX))' +' ,'''')' + ' AS TD, ' 
     FROM dbo.fn_SplitStringToTable(@p_Input,',')

   RETURN LEFT(@l_Output,LEN(@l_Output)-1)

END

GO
/****** Object:  UserDefinedFunction [dbo].[fn_SplitStringToTable]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Alex Tian
-- Create date: 2012-09-03
-- Description:    split string as a table format
-- =============================================

CREATE FUNCTION [dbo].[fn_SplitStringToTable]
(
   @p_Input     VARCHAR(MAX),
   @p_Delimeter CHAR(1) = ','
)
RETURNS @l_Table TABLE
(
   Id       INT IDENTITY(1, 1),
   Value    VARCHAR(511),
   Position INT,
   Length   INT
)
BEGIN
   DECLARE @l_Position INT,
           @l_Value    VARCHAR(256)
   
   SELECT @l_Position = 1,
          @l_Value = ''
   
   IF RIGHT(@p_Input, LEN(@p_Delimeter)) <> @p_Delimeter
      SET @p_Input = @p_Input + @p_Delimeter
   
   WHILE SUBSTRING(@p_Input, @l_Position, 1) = CHAR(10) OR
         SUBSTRING(@p_Input, @l_Position, 1) = CHAR(13)
   BEGIN
      SELECT @l_Position = @l_Position + 1
   END
   
   WHILE @l_Position <= CHARINDEX(@p_Delimeter, @p_Input, @l_Position)
   BEGIN
      SET @l_Value = RTRIM ( LTRIM ( SUBSTRING ( @p_Input,
                                                 @l_Position,
                                                 CHARINDEX ( @p_Delimeter,
                                                             @p_Input,
                                                             @l_Position) - @l_Position ) ) )
      
      IF NOT EXISTS ( SELECT 1
                        FROM @l_Table
                       WHERE Value = @l_Value )
      BEGIN
         INSERT INTO @l_Table ( Value, Position, Length )
              VALUES ( @l_Value, @l_Position, CHARINDEX ( @p_Delimeter,
                                                          @p_Input,
                                                          @l_Position ) - @l_Position )
      END
      
      SELECT @l_Position = CHARINDEX(@p_Delimeter, @p_Input, @l_Position) + 1
      
      WHILE SUBSTRING(@p_Input, @l_Position, 1) = CHAR(10) OR
         SUBSTRING(@p_Input, @l_Position, 1) = CHAR(13)
      BEGIN
         SELECT @l_Position = @l_Position + 1
      END
   END
   RETURN
END

GO
/****** Object:  Table [dbo].[AvailableDiskSpace]    Script Date: 2012/9/7 9:36:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AvailableDiskSpace](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [LastUpdate] [smalldatetime] NOT NULL,
    [DriveLetter] [char](1) NOT NULL,
    [FreeMB] [int] NOT NULL,
 CONSTRAINT [PK_AvailableDiskSpace] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NutsAndBolts_Data]
) ON [NutsAndBolts_Data]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ErrorLog]    Script Date: 2012/9/7 9:36:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ErrorLog](
    [Time] [datetime] NOT NULL,
    [Err] [int] NOT NULL,
    [Msg] [varchar](500) NOT NULL,
    [Id] [varchar](50) NOT NULL,
    [DBName] [varchar](30) NOT NULL,
    [ProcName] [varchar](50) NOT NULL,
    [DBUser] [varchar](20) NOT NULL,
    [HostName] [varchar](20) NOT NULL,
    [Application] [varchar](30) NOT NULL,
    [Checked] [bit] NOT NULL
) ON [NutsAndBolts_Data]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[EventLog]    Script Date: 2012/9/7 9:36:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EventLog](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [EventData] [xml] NOT NULL,
 CONSTRAINT [PK_EventLog] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NutsAndBolts_Data]
) ON [NutsAndBolts_Data] TEXTIMAGE_ON [NutsAndBolts_Data]

GO
/****** Object:  Table [dbo].[ProcUsage]    Script Date: 2012/9/7 9:36:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ProcUsage](
    [TrackType] [tinyint] NULL,
    [DBName] [varchar](30) NOT NULL,
    [ProcName] [varchar](50) NOT NULL,
    [Id] [varchar](15) NULL,
    [LastAccess] [datetime] NOT NULL,
    [AccessCount] [int] NOT NULL,
    [DBUser] [varchar](20) NOT NULL,
    [HostName] [varchar](20) NOT NULL,
    [AppName] [varchar](50) NOT NULL,
    [Checked] [bit] NOT NULL
) ON [NutsAndBolts_Data]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[AvailableDiskSpace] ADD  CONSTRAINT [DF_AvailableDiskSpace_LastUpdate]  DEFAULT (getdate()) FOR [LastUpdate]
GO
ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_Time]  DEFAULT (getdate()) FOR [Time]
GO
ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_DBName]  DEFAULT (left(db_name(),(30))) FOR [DBName]
GO
ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_DBUser]  DEFAULT (left(suser_sname(),(20))) FOR [DBUser]
GO
ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_HostName]  DEFAULT (left(host_name(),(20))) FOR [HostName]
GO
ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_Application]  DEFAULT (left(rtrim(ltrim(app_name())),(30))) FOR [Application]
GO
ALTER TABLE [dbo].[ErrorLog] ADD  CONSTRAINT [DF_ErrorLog_Checked]  DEFAULT ((0)) FOR [Checked]
GO
ALTER TABLE [dbo].[ProcUsage] ADD  CONSTRAINT [DF_ProcUsage_LastAccess]  DEFAULT (getdate()) FOR [LastAccess]
GO
ALTER TABLE [dbo].[ProcUsage] ADD  CONSTRAINT [DF_ProcUsage_AccessCount]  DEFAULT ((1)) FOR [AccessCount]
GO
ALTER TABLE [dbo].[ProcUsage] ADD  CONSTRAINT [DF_ProcUsage_DBUser]  DEFAULT (left(rtrim(ltrim(suser_sname())),(20))) FOR [DBUser]
GO
ALTER TABLE [dbo].[ProcUsage] ADD  CONSTRAINT [DF_ProcUsage_HostName]  DEFAULT (left(rtrim(ltrim(host_name())),(20))) FOR [HostName]
GO
ALTER TABLE [dbo].[ProcUsage] ADD  CONSTRAINT [DF_ProcUsage_AppName]  DEFAULT (left(rtrim(ltrim(app_name())),(30))) FOR [AppName]
GO
ALTER TABLE [dbo].[ProcUsage] ADD  CONSTRAINT [DF_ProcUsage_Checked]  DEFAULT ((0)) FOR [Checked]
GO