2

I have a script that shows commands being fired in SQL Server 2008 database as follows:

--shows who is active and executed commands
declare @SPID int=null,
    @Status varchar(max) = null,
    @LOGIN varchar(max) = null,
    @HostName varchar(max) = null,
    @DBName varchar(max) = NULL,
    @Command varchar(max) = null

declare @sql varchar(max)=''

set @sql='use master;DECLARE @Table TABLE(SPID INT,Status VARCHAR(MAX),LOGIN VARCHAR(MAX),HostName VARCHAR(MAX),BlkBy VARCHAR(MAX),DBName VARCHAR(MAX),Command VARCHAR(MAX),CPUTime INT,DiskIO INT,LastBatch VARCHAR(MAX),ProgramName VARCHAR(MAX),SPID_1 INT,REQUESTID INT);INSERT INTO @Table EXEC sp_who2;

SELECT  w.SPID,w.Status,w.LOGIN,w.HostName,w.BlkBy,w.DBName,w.Command,r.estimated_completion_time/60000.0 as remaining_minutes, r.percent_complete,r.total_elapsed_time/60000.0 as minutes,r.reads,r.writes,t.text,w.CPUTime,w.DiskIO,w.LastBatch,w.ProgramName,w.SPID_1,w.REQUESTID    FROM @Table w 
inner join sys.dm_exec_requests AS r on w.SPID=r.session_id 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t WHERE 1=1'
    if @SPID is not null
        set @sql=@sql+' and SPID='+@SPID
    if @Status is not null
        set @sql=@sql+' and upper(Status) like ''%'+upper(@Status)+'%'''
    if @LOGIN is not null
        set @sql=@sql+' and upper(LOGIN) like ''%'+upper(@LOGIN)+'%'''
    if @HostName is not null
        set @sql=@sql+' and upper(HostName) like ''%'+upper(@HostName)+'%'''
    if @DBName is not null
        set @sql=@sql+' and upper(DBName) like ''%'+upper(@DBName)+'%'''
    if @Command is not null
        set @sql=@sql+' and upper(Command) like ''%'+upper(@Command)+'%'''

exec (@sql)

I have noticed that there are DDL commands for creating a procedure, but I am not sure if the procedure is really being created or if this is just an information to tell which procedure was called.

enter image description here

Does anyone know what UPDATE really means in Command column? And the text column from sys.dm_exec_sql_text: is ist a called procedure or a procedure being altered/created?

1

The text value shows the procedure in which the statement that is being executed resides. The Command references the action of the executing statement within the procedure

To find the exact statement within the procedure that is being called, you'll need to use the statement_start_offset and statement_end_offset from dm_exec_requests.

Add the following to the list of columns selected:

,SUBSTRING(text, (statement_start_offset/2)+1,
                            ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text)
                                  ELSE statement_end_offset
                                END - statement_start_offset)/2)+1)

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.