WARNING: This is resource intensive. Don't use except as a last resort.
I wrote this to be able to find if a TSQL batch is running already on a SQL Server. I use this rarely when I have to know to prevent running something twice.
DECLARE @RC int
DECLARE @SQLBatch nvarchar(255)
-- Set parameter values
EXEC @RC = [master].[dbo].[sp_RunningCheck] @SQLBatch = 'sp_who2'
If @RC returns 0 then sp_who2 is not likely to be running.
if @RC returns -1 then is running.
Note that result of 0 does NOT prove the script is not running but a result of -1 does prove it is.
Detect SQL Server Instances using reg.exe
Detect SQL Server Instance(s) using reg.exe from the resource kit, just pass the host name! (and make sure reg.exe from the resouce kit is in the ../tools/bin directory. SS2k Only.Enjoy!