HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlMinor

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

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
sp_blitzacrosshowdatabasessp_whoisactiveserversmultipleversionscheck

Problem

I have a bunch of SQL Servers registered within my SSMS, lots of different SQL versions, editions and DB compatibilities.

I want to check out if any of those servers have any of the sp_Blitz or sp_WhoIsActive stored procedures installed, and which versions they are.

I know I can right-click on my Registered Servers group and open one query window that connects to all the servers in the group, but is there a reliable way to check which SProcs and versions I have?

Solution

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...

Edit: added Adam Machanic's sp_WhoIsActive as an example of how to add to this scanner script.

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;

Code Snippets

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;

Context

StackExchange Database Administrators Q#197789, answer score: 4

Revisions (0)

No revisions yet.