isAdminUsingDB

IF EXISTS (SELECT * FROM sysobjects 
           WHERE id = OBJECT_ID('dbo.isAdminUsingDB') 
           AND sysstat & 0xf = 4)
	DROP PROCEDURE dbo.isAdminUsingDB
GO

CREATE PROCEDURE isAdminUsingDB
     @dbName VARCHAR(30),
     @source VARCHAR(30)
AS
DECLARE @dbSyncTask VARCHAR(50),
        @logSyncTask VARCHAR(50),
        @maintTask VARCHAR(50),
        @val int
/*
  Procedure isAdminUsingDB checks to see if any spids except the
  spid this procedure is executing on are using the same database
  as this spid. It returns the number of other spids in the database. 

  Naming conventions in systasks are important here! Modify the 
  *Task variables to match your naming convention in systasks.

  Check for both T-SQL and CmdExec task types
*/
SET NOCOUNT ON

SELECT @myName = 'admin.isAdminUsingDB'

SELECT @dbSyncTask = "dbSync " + @dbName + "%"
SELECT @logSyncTask = "logSync " + @dbName + "%"
SELECT @maintTask = "Maintain " + @dbName + "%"
SELECT @val = (SELECT count(*) FROM master..sysprocesses 
               WHERE spid <> @@SPID
               AND program_name LIKE ("SQLEXEC - TSQL Task #%")
               AND CONVERT(INT,SUBSTRING(RTRIM(program_name), 
                   CHARINDEX("#", program_name) + 1, 
                   DATALENGTH(program_name) - CHARINDEX("#", program_name))) 
                 IN (SELECT id FROM msdb..systasks
                     WHERE (    name LIKE (@dbSyncTask)
                            AND (   CHARINDEX(@source, name) > 0
                                 OR CHARINDEX(@target, name) > 0))
                     OR    (    name LIKE (@logSyncTask)
                            AND (   CHARINDEX(@source, name) > 0
                                 OR CHARINDEX(@target, name) > 0))
                     OR name LIKE (@maintTask)))

IF (@val = 0) 
  BEGIN
    /* still could be a CMDEXEC competitor */
    IF (SELECT count(*) FROM master..sysprocesses
        WHERE program_name = ("ISQL-32")
        AND spid <> @@SPID) <> 0
      BEGIN
        /* temp table needs to allow nulls in case a spid that
           qualified for the cursor ends before the count*/ 
        CREATE TABLE #string(name VARCHAR(255) NULL)
        DECLARE CmdExec_cursor CURSOR
        FOR
        SELECT spid FROM master..sysprocesses
        WHERE program_name = "ISQL-32"
        AND spid <> @@SPID 
        OPEN CmdExec_cursor
        FETCH NEXT FROM CmdExec_cursor INTO @spid
        WHILE (@@fetch_status <> -1)
          BEGIN
            SELECT @CMD = "DBCC INPUTBUFFER(" + CONVERT(VARCHAR(6), @spid) + ")"              
            INSERT #string EXEC(@CMD)
  	    FETCH NEXT FROM CmdExec_cursor INTO @spid
          END
        CLOSE CmdExec_cursor
        DEALLOCATE CmdExec_cursor 
        SELECT @val = (SELECT count(*) FROM #string  
                       WHERE (    name LIKE (@dbSyncTask)
                              AND (   CHARINDEX(@source, name) > 0
                                   OR CHARINDEX(@target, name) > 0))
                       OR    (    name LIKE (@logSyncTask)
                              AND (   CHARINDEX(@source, name) > 0
                                   OR CHARINDEX(@target, name) > 0))
                       OR name LIKE (@maintTask))    
        DROP TABLE #string
      END
  END

IF @debug = 'true'
  BEGIN
   SELECT "stored procedure ",@myName
   SELECT "number of competing processes = ", @val
  END

RETURN @val

GO