I am a SQL Server DBA. I would like to write a procedure which I can provide to rest of my team where they can view the text for currently running queries on the server (Similar to how we view in sp_who2) but with all the values masked. Examples:

CodePudding user response：

If I understand correctly your issue. You can use this query:

select 
    r.session_id,
    r.status,
    r.command,
    r.cpu_time,
    r.total_elapsed_time,
    t.text
from sys.dm_exec_requests as r
cross apply sys.dm_exec_sql_text(r.sql_handle) as t

e.g. I run it on my SQL server right now:

(@P1 nvarchar(5),@P2 bigint,@P3 int,@P4 numeric(28, 12),@P5 nvarchar(5),@P6 datetime,@P7 datetime)
SELECT SUM(A.SETTLEAMOUNTCUR) FROM CUSTSETTLEMENT A,CUSTTRANS B WHERE ((A.DATAAREAID=@P1) AND (((A.TRANSRECID=@P2) AND (A.CANBEREVERSED=@P3)) AND (A.SETTLEAMOUNTCUR<>@P4))) AND ((B.DATAAREAID=@P5) AND (((B.RECID=A.OFFSETRECID) AND (B.TRANSDATE>=@P6)) AND (B.TRANSDATE<=@P7)))

All variables are hidden.

CodePudding user response：

You could try some XML-trickery to handle the strings. First replace all single quotes with an empty tag <X/> to get a XML that looks like this.

INSERT INTO Customers (CustomerName, ContactName) 
  VALUES (<X />Cardinal<X />, <X />Tom B. Erichsen<X />);

Then you shred the xml to get the text nodes and the node numbers where mod 2 is 0 is the ones you want to mask.

After that you can rebuild your query string using the mask values.

I have not found a way to deal with numbers other then removing all numbers from the query using Translate or nested replace and that will of course also remove numbers from table names and column names as well.

You could try something like this.

declare @S nvarchar(max);
declare @X xml;

set @S = N'UPDATE Customers SET ContactName = ''Alfred Schmidt'' WHERE CustomerID = 1;';

set @X = replace(@S, '''', '<X/>');

with C as
(
  select T.X.value('.', 'nvarchar(max)') as V, 
         row_number() over(order by T.X) as RN
  from @X.nodes('text()') as T(X)
)
select @S = (
            select case when C.RN % 2 = 0 then '''*****''' else C.V end
            from C
            order by C.RN
            for xml path(''), type
            ).value('text()[1]', 'nvarchar(max)');

set @S = translate(@S, '0123456789', '**********')

print @S;

Result:

UPDATE Customers SET ContactName = '*****' WHERE CustomerID = *;

Note: Just realized that this solution does not handle the cases where the string values contains single quotes but I think this is something that possibly can inspire more robust solution so I will leave it here.