CODEWITHSUNDEEP
sqlsql-serverdatabasetriggerssql-server-2012
Brave Soul
Brave Soul

Reputation: 3620

List All Triggers from all databases in one server

I went through many posts on SO. I figured out that we can list down all triggers in one database.

What I want is regardless of databases I want all triggers on the given tables accgrp and portinfo

Here is what I tried 

SELECT 
    sysobjects.name AS trigger_name,
    USER_NAME(sysobjects.uid) AS trigger_owner,
    s.name AS table_schema,
    OBJECT_NAME(parent_obj) AS table_name,
    OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate,
    OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete,
    OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert,
    OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter,
    OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof,
    OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM
    sysobjects 
INNER JOIN 
    sysusers ON sysobjects.uid = sysusers.uid 
INNER JOIN 
    sys.tables t ON sysobjects.parent_obj = t.object_id 
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id 
WHERE 
    sysobjects.type = 'TR' 
    AND OBJECT_NAME(parent_obj) IN ('accgrp', 'portinfo')

I am not able to loop through each database and get list of triggers on these two tables. I have like 50 to 70 databases so each time I have to go through them manually. can anyone please suggest something on this.

Upvotes: 2

Views: 4308

Answers (4)

Juozas
Juozas

Reputation: 935

My query works with diff db collations. Please add more columns to select, if required:

DECLARE @str        NVARCHAR(MAX)   = SPACE(0);
DECLARE @collation  NVARCHAR(MAX)   = (SELECT CONVERT(NVARCHAR(MAX), SERVERPROPERTY('COLLATION')));
DECLARE @rnd        NVARCHAR(MAX)   = NEWID();

SELECT 
     @str +=  CHAR(13) + 'SELECT [trg_name] = [name] COLLATE ' + @collation + ',  [db_name] = ' + CHAR(39) + [name] + CHAR(39) + ' FROM [' + [name] + '].[sys].[triggers] ' + CHAR(13) + ' UNION ALL'
FROM 
    [sys].[databases];

SET @str += @rnd;
SET @str =  REPLACE(@str, 'UNION ALL' + @rnd, SPACE(0));

EXEC(@str);

Upvotes: 3

High Plains Grifter
High Plains Grifter

Reputation: 1571

Here is a possible method, using a cursor - I have added your script verbatim to the dynamic sql that is run at each iteration of the cursor.

SET NOCOUNT ON;
DECLARE @GET_Databases CURSOR;
DECLARE
     @Current_Database  NVARCHAR(250)
    ,@SQL               NVARCHAR(MAX) 
    ,@Param_Declare     NVARCHAR(500)
    ,@ErrorMsg          NVARCHAR(MAX)
    ,@LoopCount         INT
    ,@RowCount          NVARCHAR(5);

SET @SQL = ''
SET @Param_Declare = N'@Err NVARCHAR(MAX) OUTPUT,@Rows VARCHAR(5) OUTPUT'
SET @LoopCount = 1
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results;
CREATE TABLE #Results
( Id            INT IDENTITY(1,1)   NOT NULL
 ,DatabaseName  NVARCHAR(250)       NOT NULL
 ,trigger_name  NVARCHAR(250)       NOT NULL
 ,trigger_owner NVARCHAR(250)       NOT NULL
 ,table_schema  NVARCHAR(50)        NOT NULL
 ,table_name    NVARCHAR(250)       NOT NULL
 ,isupdate      INT                 NOT NULL
 ,isdelete      INT                 NOT NULL
 ,isinsert      INT                 NOT NULL
 ,isafter       INT                 NOT NULL
 ,isinsteadof   INT                 NOT NULL
 ,[disabled]    INT                 NOT NULL
 ,Error         NVARCHAR(MAX)           NULL
);

SET @GET_Databases = CURSOR FOR
SELECT name
FROM sys.databases
ORDER BY name;

OPEN @GET_Databases;

FETCH NEXT FROM @GET_Databases INTO @Current_Database;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = '';
SET @SQL = @SQL + 
----------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------  
'USE ' + QUOTENAME(@Current_Database) + ';
';

----------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------  
    SET @SQL = @SQL + '
BEGIN TRY
    ';
    SET @SQL = @SQL +

----------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------  
'INSERT #Results (DatabaseName, trigger_name, trigger_owner, table_schema, table_name, isupdate, isdelete, isinsert, isafter, isinsteadof, disabled)
SELECT
    ''' + @Current_Database + ''',
    sysobjects.name AS trigger_name,
    USER_NAME(sysobjects.uid) AS trigger_owner,
    s.name AS table_schema,
    OBJECT_NAME(parent_obj) AS table_name,
    OBJECTPROPERTY( id, ''ExecIsUpdateTrigger'') AS isupdate,
    OBJECTPROPERTY( id, ''ExecIsDeleteTrigger'') AS isdelete,
    OBJECTPROPERTY( id, ''ExecIsInsertTrigger'') AS isinsert,
    OBJECTPROPERTY( id, ''ExecIsAfterTrigger'') AS isafter,
    OBJECTPROPERTY( id, ''ExecIsInsteadOfTrigger'') AS isinsteadof,
    OBJECTPROPERTY(id, ''ExecIsTriggerDisabled'') AS [disabled] 
FROM
    sysobjects 
INNER JOIN 
    sysusers ON sysobjects.uid = sysusers.uid 
INNER JOIN 
    sys.tables t ON sysobjects.parent_obj = t.object_id 
INNER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id 
WHERE 
    sysobjects.type = ''TR'' 
    AND OBJECT_NAME(parent_obj) IN (''accgrp'', ''portinfo'')'
----------------------------------------------------------------------------------------------------------------------------------------------  
----------------------------------------------------------------------------------------------------------------------------------------------  
    SET @SQL = @SQL + '
    SET @Rows = CONVERT(NVARCHAR(5),@@ROWCOUNT)
END TRY
BEGIN CATCH
    SET @Err = ERROR_MESSAGE();
END CATCH';

    IF @LoopCount = 1
    BEGIN
        PRINT 'Example SQL Script:';
        PRINT @SQL;
    END

    PRINT @Current_Database;

    EXECUTE sp_executesql
         @SQL
        ,@Param_Declare
        ,@Err = @ErrorMsg OUTPUT
        ,@Rows = @RowCount OUTPUT;

    IF @ErrorMsg IS NOT NULL
    BEGIN 
        PRINT @ErrorMsg + '
';
        SET @ErrorMsg = NULL;
    END
    ELSE PRINT '    ' + @Rowcount + ' Rows

';
    SET @LoopCount = @LoopCount + 1;
    FETCH NEXT FROM @GET_Databases INTO @Current_Database;
END

CLOSE @GET_Databases;
DEALLOCATE @GET_Databases;

SET NOCOUNT OFF;

SELECT * FROM #Results

DROP TABLE #Results

Upvotes: 1

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

This will give what you want to get

DECLARE @QRY VARCHAR(MAX) ='';

SELECT @QRY =@QRY + ' select  s.name AS table_schema 
    ,OBJECT_NAME(TR.parent_id) AS table_name 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsUpdateTrigger'') AS isupdate 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsDeleteTrigger'') AS isdelete 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsInsertTrigger'') AS isinsert 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsAfterTrigger'') AS isafter 
    ,OBJECTPROPERTY( SO.object_id, ''ExecIsInsteadOfTrigger'') AS isinsteadof 
    ,OBJECTPROPERTY(SO.object_id, ''ExecIsTriggerDisabled'') AS [disabled]
FROM ['+name+'].SYS.TRIGGERS TR
INNER JOIN ['+name+'].sys.tables t     ON TR.parent_id = t.object_id 
INNER JOIN ['+name+'].sys.objects SO ON TR.object_id = SO.object_id
INNER JOIN ['+name+'].sys.schemas s     ON t.schema_id = s.schema_id 
WHERE OBJECT_NAME(parent_id) IN (''accgrp'',''portinfo'')
UNION ALL
' 
FROM SYS.DATABASES
WHERE name not IN ('master', 'model', 'msdb', 'tempdb', 'resource',
       'distribution' , 'reportserver', 'reportservertempdb','jiradb')

SELECT @QRY = SUBSTRING(@QRY,1,LEN(@QRY)-12)

EXEC( @QRY)

What I have done?

  1. Queried sys.databases Table to get user created Databases.

  2. Appended the query by taking Database name column of sys.databases as prefix of tables. and UNION ALL as suffix of the Query

  3. Stored it in a variable @QRY by internal looping with SELECT @QRY =@QRY +

  4. Removed last UNION ALL with Substring

  5. Executed the dynamic code.

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

Command sysusers table join and get result : 

 SELECT 
 sysobjects.name AS trigger_name 
,USER_NAME(sysobjects.uid) AS trigger_owner 
,s.name AS table_schema 
,OBJECT_NAME(parent_obj) AS table_name 
,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 
/*
 INNER JOIN sysusers ON sysobjects.uid = sysusers.uid 
*/  
 INNER JOIN sys.tables t ON sysobjects.parent_obj = t.object_id  
 INNER JOIN sys.schemas s ON t.schema_id = s.schema_id 
 WHERE sysobjects.type = 'TR'

Upvotes: 1

Related Questions