4

How to save result of sp_BlitzWho to a table? An option to save to table is not there compared to other procs like sp_Blitz.

7

You can declare table variable with fields fully equal to SP output and save results to it. E.g.:

    DECLARE @who_results TABLE
    ([run_date] DATETIME,
     ...
    )
    INSERT INTO @who_results EXEC sp_blitzwho

After that, you can do everything you want with data in table variable.

6

It's not a feature we have in sp_BlitzWho right now, but if you add a request for it in the Github repo, I'd rubber-stamp it with a yes, and you could implement it if you like. It's fairly easy to copy/paste the code from the other procs.

Check out the Contributing.md file in the Github repo for more information on how to contribute to the project.

In the meantime, you can use the OPENROWSET trick to create a temp table without knowing the result set definitions ahead of time.

2

On any relatively recent version of SQL Server, i.e. 2012+, you can use sys.dm_exec_describe_first_result_set to programmatically create a CREATE TABLE statement containing a correctly-defined table for the results.

You can then use the INSERT INTO ... EXEC ... syntax mentioned by @Marvin in his answer to save the results into the table.

This should do the trick:

DECLARE @tsql nvarchar(max);
DECLARE @params nvarchar(max);
DECLARE @browse_information_mode tinyint;
SET @tsql = N'EXEC dbo.sp_BlitzWho;'
SET @params = NULL;
SET @browse_information_mode = NULL;
DECLARE @cmd nvarchar(max);
SET @cmd = '';
SELECT @cmd = CASE WHEN @cmd <> N'' THEN @cmd + N'
    , ' ELSE N'' END + frs.name + N' ' + frs.system_type_name 
      + CASE WHEN frs.is_nullable = 1 THEN N' NULL' ELSE N' NOT NULL' END
FROM sys.dm_exec_describe_first_result_set(@tsql, @params, @browse_information_mode) frs;

SET @cmd = N'CREATE TABLE #BlitzOutput (
    ' + @cmd + N'
);
INSERT INTO #BlitzOutput 
' + @tsql;
PRINT @cmd;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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