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

Question

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?

Answer 1

For sp_Blitz, there's an enhancement request filed at Github to add outputs for version, and if you'd like to influence how the work is done (or contribute code), you're welcome to leave comments over there.

For sp_WhoIsActive, the author Adam Machanic is considering adding an output to the Messages tab each time it runs.

Until then, Oreo's suggestion above of checking the code is the best one. We won't break that intentionally - we haven't changed those strings in years, and don't intend to start, heh.

Answer 2

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;

Answer 3

I'm running something like this:

create table #tmp (line varchar(max))

insert into #tmp
exec sp_helptext 'sp_Blitz'

declare @version varchar(50);

select @version = Left(line, CHARINDEX('''',line,CHARINDEX('''',line,1)+1))
from #tmp
where line like '%@Version =%'

set @version = reverse(left(REVERSE(@version),CHARINDEX('''',reverse(@version),2)))

select replace(@version,'''','')

drop table #tmp