sp_who_azsqldb

create or alter procedure dbo.sp_who_azsqldb
as

-- active sessions
create table #Connections
(
	SessionId int not null
	,BlockingId int null
	,Host nvarchar(128) null
	,Program nvarchar(128) null
	,Login nvarchar(128) null
	,Status nvarchar(32) null
	,CommandType nvarchar(32) null
	,TotalTime bigint null
	,CPUTime bigint null
	,WaitTime bigint null
	,WaitType nvarchar(60) null
	,Reads bigint null
	,Writes bigint null
	,DOP int null
	,ParallelWorkers int null
	,IsUserProcess bit null
	,SQLHandle varbinary(64) null
	,SQLStart int null
	,index IX_SessionId nonclustered (SessionId)
	,index IX_BlockingId nonclustered (BlockingId)
);
​
insert into #Connections
(
	SessionId
	,Host
	,Program
	,Login
	,IsUserProcess
	,BlockingId
	,Status
	,CommandType
	,TotalTime
	,CPUTime
	,WaitTime
	,WaitType
	,Reads
	,Writes
	,DOP
	,ParallelWorkers
	,SQLHandle
	,SQLStart
)
select
	s.session_id
	,s.host_name
	,s.program_name
	,s.login_name
	,s.is_user_process
	,r.blocking_session_id
	,r.status
	,r.command
	,r.total_elapsed_time
	,r.cpu_time
	,r.wait_time
	,r.wait_type
	,r.reads + r.logical_reads
	,r.writes
	,r.dop
	,r.parallel_worker_count
	,r.sql_handle
	,r.statement_start_offset
from
	sys.dm_exec_connections as c
	join sys.dm_exec_sessions as s
		on s.session_id = c.session_id
	join sys.dm_exec_requests as r
		on r.session_id = s.session_id
where
	-- only include the parent connection
	c.parent_connection_id is null;

-- system processes
insert into #Connections
(
	SessionId
	,BlockingId
	,Login
	,Status
	,CommandType
	,CPUTime
	,WaitTime
	,WaitType
	,IsUserProcess
	,SQLHandle
	,SQLStart
)
select
	s.spid
	,s.blocked
	,s.loginame
	,s.status
	,nullif(s.cmd, '')
	,s.cpu
	,s.waittime
	,s.lastwaittype
	,0
	,s.sql_handle
	,s.stmt_start
from
	sys.sysprocesses as s
	left join #Connections as c
		on c.SessionId = s.spid
where
	s.hostprocess = ''
	and c.SessionId is null;
​
select
	c.SessionId
	,c.BlockingId
	,c.Host
	,c.Program
	,nullif(c.Login, '') as Login
	,c.Status
	,c.TotalTime
	,c.CPUTime
	,c.WaitTime
	,c.WaitType
	,c.Reads
	,c.Writes
	,c.DOP
	,c.ParallelWorkers
	,c.CommandType
	,object_schema_name(t.objectid, t.dbid) + '.' + object_name(t.objectid, t.dbid) as ObjectName
	-- only fetch the first 1000 characters; the entire statement isn't needed
	,substring(t.text, c.SQLStart / 2, 1000) as SQLStatement
from
	#Connections as c
	outer apply sys.dm_exec_sql_text(c.SQLHandle) as t
where
	c.IsUserProcess = 1
	-- include the blocking session even if it is a system process
	or exists (select 1 from #Connections cb where cb.BlockingId = c.SessionId );