how can I identify ALL triggers that call a specific stored procedure?

Question

I am having an issue that I need to move some stored procedures to a different database. Those SPs are run from the same and other databases as well.

Specially they are run from inside triggers.

In the script below, I can identify all the triggers that run a stored procedure called applicationCommission from the database I currently am.

what I am failing and want to achieve is:

I want to find all triggers in all databases in the current server that call that stored procedure.

What changes can I do on the script below to achieve this? or

How can I achieve this?

    --============================================================================
    -- the trigger definition query
    -- marcelo miorelli
    -- 20-july-2017
    --=============================================================================
    --SELECT 
    --    QUOTENAME(SCHEMA_NAME(schema_id)) AS schema_name,
    --    QUOTENAME(OBJECT_NAME(parent_object_id)) AS table_name,
    --  QUOTENAME(so.name) AS trigger_name, 
    --    OBJECT_DEFINITION(object_id) AS trigger_definition
    --FROM sys.objects so
    --WHERE so.type = 'TR' 

    --===============================================================================
    -- find what triggers run a stored procedure
    -- marcelo miorelli
    -- 20-july-2017
    --===============================================================================

    SET NOCOUNT ON
    SET DEADLOCK_PRIORITY LOW
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    DECLARE @SQL NVARCHAR(max)
-- THE PROCEDURE THAT I AM LOOKING FOR
           ,@spName VARCHAR(100) = 'applicationCommission' 


    SELECT @SQL = STUFF((
                SELECT CHAR(10) + ' UNION ALL '                    + CHAR(10) +  
    ' SELECT ' + quotename(s.NAME, '''') + ' AS DB_NAME '          + CHAR(10) + 
    '         ,QUOTENAME(OBJECT_SCHEMA_NAME(so.schema_id,db_id()))  
                         AS THE_SCHEMA ' + CHAR(10) + 

    '         ,QUOTENAME(OBJECT_NAME(parent_object_id,db_id())) 
                          AS table_name'          + CHAR(10) +

    '         , so.name  COLLATE Latin1_General_CI_AS 
                          AS TRIGGER_NAME ' + CHAR(10) + 

    '         ,OBJECT_DEFINITION(object_id) 
                         AS trigger_definition' + CHAR(10) + 

    '  FROM ' + quotename(s.NAME) + '.sys.objects so ' + CHAR(10) +   
    ' WHERE 1=1                                        '  + CHAR(10) +   
    --'   AND s.name LIKE @spName '                       + CHAR(10) +  
    ' AND so.[type] = ''TR'''
                FROM sys.databases s
               WHERE 1=1
                 AND S.state = 0
                 AND s.name NOT IN ('master', 'model', 
                                    'msdb', 'tempdb', 'distribution')
                ORDER BY s.NAME
                FOR XML PATH('')
                    ,TYPE
                ).value('.', 'NVARCHAR(MAX)'), 1, 11, '')

    --PRINT @SQL


    IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL
       BEGIN 
          DROP TABLE #RADHE
       END 

    CREATE TABLE #Radhe(
        [database_name] sysname NOT NULL,
        [schema_name] sysname NULL,
        [table_name] sysname NULL,
        [trigger_name] sysname NOT NULL,
        [trigger_definition] NVARCHAR(MAX)
    );

    INSERT INTO #Radhe
    EXECUTE sp_executeSQL @SQL
        --,N'@spName varchar(100)'
        --,@spName

    SELECT *
    FROM #Radhe
    WHERE trigger_definition LIKE '%' + @spName + '%'

EDIT:

This is how I ended up this case:

--==============================================================================================
-- version using cursor
-- in all databases - get all triggers that fire a particular stored procedure (accept wildcards %)
-- marcelo miorelli
-- 27-july-2017

-- https://stackoverflow.com/questions/1409965/t-sql-a-proper-way-to-close-deallocate-cursor-in-the-update-trigger
-- https://stackoverflow.com/questions/21225324/open-and-close-cursors-inside-or-outside-a-transaction-and-how-to-close-a-cursor
--==============================================================================================

USE master;
GO


IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL 
   BEGIN
       DROP TABLE #Radhe 
   END 

CREATE TABLE #Radhe ( 
        [DB]                  NVARCHAR(128)                        NULL,
        [THE_SCHEMA]          NVARCHAR(258)                        NULL,
        [table_name]          NVARCHAR(258)                        NULL,
        [TRIGGER_NAME]        NVARCHAR(128)                    NOT NULL,
        [trigger_definition]  NVARCHAR(max)                         NULL
)



DECLARE @name sysname;
DECLARE @SQL nvarchar(max);
DECLARE @theSQL nvarchar(max);
DECLARE @spName VARCHAR(100) = '%PropInsert%' -- THE PROCEDURE THAT I AM LOOKING FOR

    SELECT @SQL =
     '-------------------------------------------------------------------------------------------------------------------
                  SELECT 
                         DB = DB_NAME()
                         ,QUOTENAME(OBJECT_SCHEMA_NAME(so.schema_id,db_id()))  AS THE_SCHEMA 
                         ,QUOTENAME(OBJECT_NAME(parent_object_id,db_id())) AS table_name
                         , so.name  COLLATE Latin1_General_CI_AS AS TRIGGER_NAME 
                         ,OBJECT_DEFINITION(object_id) AS trigger_definition
                  FROM sys.objects so 

                 WHERE 1=1                                        
                 AND so.[type] = ''TR'' -- get only the triggers
     -------------------------------------------------------------------------------------------------------------------';

-- PRINT @SQL

BEGIN TRY

            DECLARE THE_DBS CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
            FOR
            SELECT s.name 
            FROM sys.databases s
            WHERE 1=1
              AND s.state = 0
              AND s.name NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')
              AND s.name NOT LIKE 'ReportServer%'


            OPEN THE_DBS;
            FETCH NEXT FROM THE_DBS INTO @name;

            WHILE @@FETCH_STATUS = 0
            BEGIN

                SET @theSQL = 'EXEC ' + QUOTENAME(@name) + 
                                    '.sys.sp_executesql @SQL'     + CHAR(10) +
                                    ',N''@spName varchar(100)'',' + CHAR(10) + 
                                    '''' + @spName + '''' +';'    + CHAR(10)

                -------------------------------------------                        
                --do the insert here
                -------------------------------------------
                INSERT INTO #Radhe([DB],[THE_SCHEMA],[table_name],[TRIGGER_NAME],[trigger_definition] )
                EXEC sys.sp_executesql @theSQL
                    , N'@SQL nvarchar(max)'
                    , @SQL

                FETCH NEXT FROM THE_DBS INTO @name; 

            END

            ------------------------------------------- 
            BEGIN TRY
                --clean it up    
                CLOSE THE_DBS;
                DEALLOCATE THE_DBS;
            END TRY
            BEGIN CATCH
                --do nothing
            END CATCH
            ------------------------------------------- 

END TRY

BEGIN CATCH

            ------------------------------------------- 
            BEGIN TRY
                --clean it up    
                CLOSE THE_DBS;
                DEALLOCATE THE_DBS;
            END TRY
            BEGIN CATCH
                --do nothing
            END CATCH
            ------------------------------------------- 

    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY   INT,
            @ERRORNUMBER     INT,
            @ERRORSTATE      INT,
            @ERRORPROCEDURE  SYSNAME,
            @ERRORLINE       INT,
            @XASTATE         INT

    SELECT
            @ERRORMESSAGE     = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER      = ERROR_NUMBER(),
            @ERRORSTATE       = ERROR_STATE(),
            @ERRORPROCEDURE   = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT                    CHAR(13) +
      'Message:'         +    SPACE(1) + @ErrorMessage                           + SPACE(2) + CHAR(13) +
      'Error:'           +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber)      + SPACE(1) + CHAR(13) +
      'Severity:'        +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity)    + SPACE(1) + CHAR(13) +
      'State:'           +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState)       + SPACE(1) + CHAR(13) +
      'Routine_Name:'    +    SPACE(1) + COALESCE(@ErrorProcedure,'')            + SPACE(1) + CHAR(13) +
      'Line:'            +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine)        + SPACE(1) + CHAR(13) +
      'Executed As:'     +    SPACE(1) + SYSTEM_USER + SPACE(1)                             + CHAR(13) +
      'Database:'        +    SPACE(1) + DB_NAME() + SPACE(1)                               + CHAR(13) +
      'OSTime:'          +    SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121)        + CHAR(13) 
    )

    --We can also save the error details to a table for later reference here.
    RAISERROR (@ERRORMESSAGE,16,1)

END CATCH

--------------------------------
-- by now the #Radhe table has been populated
--------------------------------

SELECT *
FROM #Radhe
WHERE trigger_definition LIKE @spName

Answer 1

Marcello, if you absolutely have to be able to achieve your goal with a single script, you could try iterating through the databases and running TSQL in the context of each database. An example is below with sp_executesql running sp_executesql from/in each database in the cursor loop. Note that @Tsql is static here, so it's declared outside of the cursor loop. However it could just as easily be built dynamically within the loop. This approach isn't easy. It might even seem a little convoluted. I'll let you be the judge.

/*
    Build the TSQL statement that you want to run on each database.
    Here is an example that finds database users that are members of
    specific fixed database roles and drops them from those roles.
    Note the single quotes are quadrupled.
*/
DECLARE @Tsql NVARCHAR(MAX) = '
    DECLARE @User SYSNAME, @Role SYSNAME
    DECLARE curUsers CURSOR FAST_FORWARD READ_ONLY FOR
        SELECT u.name UserName, r.name dbRole
        FROM sys.database_principals u
        JOIN sys.database_role_members rm
            ON rm.member_principal_id = u.principal_id
        JOIN sys.database_principals r
            ON r.principal_id = rm.role_principal_id
        WHERE r.name IN (''''db_accessadmin'''', ''''db_backupoperator'''', ''''db_securityadmin'''')
        AND u.name NOT IN (''''dbo'''', ''''guest'''', ''''INFORMATION_SCHEMA'''', ''''public'''', ''''sys'''')
        ORDER BY u.name, r.name

    OPEN curUsers
    FETCH NEXT FROM curUsers INTO @User, @Role

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC sp_droprolemember @Role, @User
        FETCH NEXT FROM curUsers INTO @User, @Role
    END

    CLOSE curUsers
    DEALLOCATE curUsers
    ';

DECLARE @DB SYSNAME
DECLARE curDB CURSOR FOR
    SELECT d.name
    FROM master.sys.databases d
    --Comment out/adjust this WHERE clause as needed.
    WHERE d.name NOT IN ('master','tempdb')

OPEN curDB
FETCH NEXT FROM curDB INTO @DB, @DBOwner

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @DB

    --Execute sp_executesql, which executes sp_executesql 
    --in the context of a specific database.
    SET @Tsql = '[' + @DB + ']..sp_executesql N''' + @Tsql + ''''
    EXEC sp_executesql @Tsql;

    FETCH NEXT FROM curDB INTO @DB, @DBOwner
END

CLOSE curDB
DEALLOCATE curDB 
GO

Answer 2

You may want to take a look at Red Gate's SQL Search (free Management Studio plugin) http://www.red-gate.com/products/sql-development/sql-search/

It will allow you to search for the stored procedure call in any or all databases on your server, and you can limit the search to look only in triggers if you want.

Answer 3

Pinal Dave has an excellent script identifying object dependencies across databases.

SQL SERVER – Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies

Answer 4

Have a look at using the sp_MSforeachdb stored procedure, it will iterate through all user databases easily enough for your needs.