:SetVar Procedure "AUX:ListProcesses"
:r $(DropProcedureScript_AUX)
create procedure [$(Procedure)] @DatabaseName $(dt_String_AUX)='*'
AS
/*==================================================================================================
# SQLAUX scripting library | Author: Sergei Y.Kitáev
----------------------------------------------------------------------------------------------------
#
# LISTING OF SQL PROCESSES (QUERIES):
#
# === Usage: ===
# exec [AUX:ListProcesses]                 -- all processes
# exec [AUX:ListProcesses] <Database name> -- processes in specified DB
# exec [AUX:ListProcesses] '.'             -- processes in current DB
# exec [AUX:ListProcesses] '*'             -- all processes
# exec [AUX:ListProcesses] null            -- processes without database context
-- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
# === Result-set: ===
# [Is current]         bit       not null
# [Database name]      sysname   not null
# [SPID]               int       not null
# [Status]             sysname   not null
# [Command]            sysname   not null
# [CPU]                int       not null
# [Login name]         sysname   not null
# [Application name]   sysname   not null
# [Host name]          sysname   not null
# [Login time]         datetime2 not null
# [Last batch time]    datetime2 not null
# ___ Order: ___
# : [Status] = 'runnable' — to list beginning;
# : current @@SPID — to list beginning;
# : current DB — to list beginning;
# [Database name] (heavy null-s);
# [SPID]
==================================================================================================*/
$(BEGIN_AUX)
----------------------------------------------------------------------------------------------------
declare @ErrorMessage $(dt_String_AUX), @DatabaseId int
----------------------------------------------------------------------------------------------------

if @DatabaseName is not null
begin
   set @DatabaseName=RTRIM(@DatabaseName)
   if dbo.[AUX:StrLength](@DatabaseName)=0 or LTRIM(@DatabaseName)<>@DatabaseName
      exec [AUX:RaiseIncorrectProcedureCall] '$(Procedure)'
end

----------------------------------------------------------------------------------------------------

if @DatabaseName is not null and @DatabaseName<>'*'
   if @DatabaseName = '.'
      select @DatabaseId = db.[dbid]
      from [sysprocesses] p (nolock)
      join [sysdatabases] db (nolock)
           on p.[dbid] = db.[dbid] and p.[spid] = @@SPID
   else
   begin
      select @DatabaseId = [dbid] from [sysdatabases] (nolock) where [name] = @DatabaseName
      if @@rowcount=0
      begin
         set @ErrorMessage = dbo.[AUX:LocalizedText](
            'Database not found by name: “'+@DatabaseName+'”.',
            N'База данных по имени не найдена: “'+@DatabaseName+N'”.' )
         raiserror (@ErrorMessage,11,1)
      end
   end

----------------------------------------------------------------------------------------------------

select
   CONVERT( bit,
      case when p.[spid] = @@SPID
      then 1 else 0 end )  'Is current',
   ISNULL(db.[name],'')    'Database name',
   p.[spid]                'SPID',
   case p.[status]
      when 'runnable' then UPPER(p.[status])
      else LOWER(p.[status])
   end                     'Status',
   p.[cmd]                 'Command',
   p.[cpu]                 'CPU',
   p.[loginame]            'Login name',
   p.[program_name]        'Application name',
   p.[hostname]            'Host name',
   p.[login_time]          'Login time',
   p.[last_batch]          'Last batch time'
from
   [sysprocesses] p (nolock)
left join
   [sysdatabases] db (nolock)
   on p.[dbid] = db.[dbid]
where
   @DatabaseName = '*' or
   ( @DatabaseName is not null and db.[dbid] = @DatabaseId ) or
   ( @DatabaseName is     null and db.[dbid] is null  )
order by
   case
      when p.[status] = 'runnable'
      then 0 else 1
   end,
   case
      when p.[spid] = @@SPID
      then 0 else 1
   end,
   case
      when db.[dbid] is null
      then 1 else 0
   end,
   IIF( db.[name] is null, 1, 0 ),
   db.[name],
   p.[spid]

----------------------------------------------------------------------------------------------------
$(END_AUX)
/*================================================================================================*/
GO
/*
exec [AUX:ListProcesses]
exec [AUX:ListProcesses] N'TEST'
exec [AUX:ListProcesses] '.'
exec [AUX:ListProcesses] '*'
exec [AUX:ListProcesses] null
*/
