Revisions to How do I check my sp_Blitz/sp_WhoIsActive versions across multiple servers/databases?

make code fit page width

Source Link

edited Jul 5, 2023 at 10:19

1.6k
1
10
21

I managed to get quite a usable list by sniffing out the versions within the SProcs' source code, as
as stored within the sys.comments tables.

A healthy amount of assumptions was made, so
so test this code on a solitary test instance first, before
before going wild on your multitude of Production servers...

USE [master];

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,REPLACE(REPLACE(
        SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15)
        ,'', @'' , '''')
        ,''Version = '' , '''')
        AS VersionString
    ,REPLACE(REPLACE(
        CASE
        WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
        THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
        ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
        END
        ,''VersionDate = '' , '''')
        ,'''''''' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING(
        [text],
        ,CHARINDEX(''Who Is Active? v'', [text], 1) + 15,
        ,7
        ) AS VersionString
    ,REPLACE(
        SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10)
        ,''-'' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY DBName, SProcName;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

I managed to get quite a usable list by sniffing out the versions within the SProcs' source code, as stored within the sys.comments tables.

A healthy amount of assumptions was made, so test this code on a solitary test instance first, before going wild on your multitude of Production servers...

USE [master];

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,REPLACE(REPLACE(
        SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15)
        ,'', @'' , '''')
        ,''Version = '' , '''')
        AS VersionString
    ,REPLACE(REPLACE(
        CASE
        WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
        THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
        ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
        END
        ,''VersionDate = '' , '''')
        ,'''''''' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,REPLACE(
        SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10)
        ,''-'' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY DBName, SProcName;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

I managed to get quite a usable list by sniffing out the versions within the SProcs' source code,
as stored within the sys.comments tables.

A healthy amount of assumptions was made,
so test this code on a solitary test instance first,
before going wild on your multitude of Production servers...

USE [master];

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,REPLACE(REPLACE(
        SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15)
        ,'', @'' , '''')
        ,''Version = '' , '''')
        AS VersionString
    ,REPLACE(REPLACE(
        CASE
        WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
        THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
        ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
        END
        ,''VersionDate = '' , '''')
        ,'''''''' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING(
        [text]
        ,CHARINDEX(''Who Is Active? v'', [text], 1) + 15
        ,7
        ) AS VersionString
    ,REPLACE(
        SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10)
        ,''-'' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY DBName, SProcName;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

clean up version and versiondate strings a bit

Source Link

edited Sep 27, 2019 at 14:34

Oreo

1.6k
1
10
21

USE [master];

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,REPLACE(REPLACE(
        SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15)
        ,'', @'' , '''')
        ,''Version = '' , '''')
        AS VersionString
    ,REPLACE(REPLACE(
        CASE
        WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
        THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
        ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
        END
        ,''VersionDate = '' , '''')
        ,'''''''' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,REPLACE(
        SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10)
        ,''-'' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY 1DBName, SProcName;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15) AS VersionString
    ,CASE
     WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
     THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
     ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
     END AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10) AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY 1

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

USE [master];

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,REPLACE(REPLACE(
        SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15)
        ,'', @'' , '''')
        ,''Version = '' , '''')
        AS VersionString
    ,REPLACE(REPLACE(
        CASE
        WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
        THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
        ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
        END
        ,''VersionDate = '' , '''')
        ,'''''''' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,REPLACE(
        SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10)
        ,''-'' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY DBName, SProcName;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

fix formatting for new and old Blitz versions

Source Link

edited Sep 17, 2019 at 14:16

Oreo

1.6k
1
10
21

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15) AS VersionString
    ,CASE
     WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
     THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
     ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
     END AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10) AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY 1

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
        )) AS SProcName
    ,SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15) AS VersionString
    ,SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24) AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10) AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY 1

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

CREATE TABLE ##DBA_SProcs (
     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL
    );

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'
USE[¬];

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,LTRIM(RTRIM(
        REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,'']'','''')
            ,''('','''')
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
            ,''@He'','''')
            ,''@Ch'','''')
        )) AS SProcName
    ,SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15) AS VersionString
    ,CASE
     WHEN CHARINDEX('', @VersionD'', [text], 1) > 0
     THEN SUBSTRING([text], CHARINDEX('', @VersionD'', [text], 1) + 3, 24)
     ELSE SUBSTRING([text], CHARINDEX(''T @VersionD'', [text], 1) + 3, 24)
     END AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

INSERT INTO ##DBA_SProcs
SELECT
     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 15,  7) AS VersionString
    ,SUBSTRING([text], CHARINDEX(''Who Is Active? v'', [text], 1) + 23, 10) AS VersionDateString
FROM sys.syscomments sc
WHERE sc.[text] LIKE ''%sp_WhoIsActive%''
  AND sc.[text] LIKE ''%Who Is Active? v%'';
';

SELECT *
FROM ##DBA_SProcs
ORDER BY 1

IF OBJECT_ID('tempdb..##DBA_SProcs') IS NOT NULL
    DROP TABLE ##DBA_SProcs;

fix WHERE clause to catch older (2015?!) versions of Blitz scripts.

Source Link

edited Sep 17, 2019 at 12:07

Oreo

1.6k
1
10
21

Loading

add Adam's sp_WhoIsActive SProc

Source Link

edited Feb 16, 2018 at 13:13

Oreo

1.6k
1
10
21

Loading

Source Link

created Feb 13, 2018 at 14:31

Oreo

1.6k
1
10
21

Loading

Stack Exchange Network

Return to Answer