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?

3 Answers 3


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.


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.

screenshot of T-SQL results

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;

     DBName             NVARCHAR(100) NULL
    ,SProcName          NVARCHAR (50) NULL
    ,VersionString      NVARCHAR (50) NULL
    ,VersionDateString  NVARCHAR (50) NULL

EXEC sys.sp_MSforeachdb
     @replacechar = N'¬'
    ,@command1 = N'

     DB_NAME() AS DBName
            SUBSTRING([text], CHARINDEX(''sp_Blitz'', [text], 1), 18)
            ,CHAR(9)/*Tab*/,'' '')
            ,CHAR(10)/*LF*/,'' '')
            ,CHAR(13)/*CR*/,'' '')
        )) AS SProcName
        SUBSTRING([text], CHARINDEX(''T @Version'', [text], 1) + 3, 15)
        ,'', @'' , '''')
        ,''Version = '' , '''')
        AS VersionString
        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)
        ,''VersionDate = '' , '''')
        ,'''''''' , '''')
        AS VersionDateString
FROM sys.syscomments sc
WHERE sc.colid <= 1
  AND sc.[text] LIKE ''%sp_Blitz%''
  AND sc.[text] LIKE ''%T @Version%'';

     DB_NAME() AS DBName
    ,''sp_WhoIsActive'' AS SProcName
        ,CHARINDEX(''Who Is Active? v'', [text], 1) + 15
        ) 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%'';

ORDER BY DBName, SProcName;

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.