1

Actually,I need to catch each query (update/delete) and rows affected by it of a stored procedure in order to keep track. You can assume that that stored procedure contains only delete and update commands.

I have created following function

CREATE Function [dbo].[CURRENT_Query] (@SPID int)
Returns nvarchar(max) 
Begin 
Declare @CURRENT_Query  nvarchar(max)
SET @CURRENT_Query =(SELECT t.text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE session_id =  @SPID   )
Return @CURRENT_Query
End

and then fired following queries

declare @row_count int 
Update t set T_id =20
from table_name t
where t_id in ('10','11','12')

set @row_count =(select @@rowcount)

select dbo.CURRENT_Query(@@spid),@row_count

now I want to catch query and count of rows affected by it. I have also tried using after update trigger on that table using 

EXEC sp_executesql N'DBCC INPUTBUFFER(@@spid) WITH NO_INFOMSGS'

but I was unable to catch row count affected by queries.

I tried using following query also

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 104
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)

Please suggest optimised solution so that I can track each query and row count on a table in a stored procedure

Kiran Dhamale
  • 27
  • 7
  • uhm, doesn't have sql-server have logging functions for that? – Tschallacka Apr 04 '18 at 14:04
  • From you question looks like you just need **Yes** or **No** as the answer. An attempt from you is required for us to help better – Sunil Apr 04 '18 at 14:04
  • On what? Delete / Insert on certain Tables? Or on all tables of one DB? And tracking ALL inserts / deletes in a database: what about sys tables receiving inserts / deletes? Please provide a more detailed example and some info on your DB structure. – Tyron78 Apr 04 '18 at 14:06
  • Well....the only question is if you can create a function to do that. We can't actually answer that. It is possible but only you can decide if you are able to. Unless you provide some details about what you actually want to do this question is entirely too vague. – Sean Lange Apr 04 '18 at 14:08
  • 1
    I can't for the life of me understand why somebody would give this an upvote. Seems that voting on questions both negative and positive have nearly nothing to do with the actual quality of the question. – Sean Lange Apr 04 '18 at 14:09
  • I find myself wondering if the upvote on @SeanLange's comment is just there for irony... – Eric Brandt Apr 04 '18 at 15:47

1 Answers1

0

This is the example for checking the top 1000 sqls ran recently. Hope it can help you.

SELECT TOP 1000 
QS.creation_time, 
SUBSTRING(ST.text,(QS.statement_start_offset/2)+1, 
((CASE QS.statement_end_offset WHEN -1 THEN DATALENGTH(st.text) 
ELSE QS.statement_end_offset END - QS.statement_start_offset)/2) + 1 
) AS statement_text, 
ST.text, 
QS.total_worker_time, 
QS.last_worker_time, 
QS.max_worker_time, 
QS.min_worker_time 
FROM 
sys.dm_exec_query_stats QS 
CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST 
ORDER BY 
QS.creation_time DESC
Parker
  • 1
  • 1