App Muncher

Issues faced - solutions identified - shared with community.

Most of the times, I use the following scripts to get list of triggers

  1.  
         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' 
        
  2.  
        SELECT
            [so].[name] AS [trigger_name],
            USER_NAME([so].[uid]) AS [trigger_owner],
            USER_NAME([so2].[uid]) AS [table_schema],
            OBJECT_NAME([so].[parent_obj]) AS [table_name],
            OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS [isupdate],
            OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS [isdelete],
            OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS [isinsert],
            OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS [isafter],
            OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS [isinsteadof],
            OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS [disabled] 
        FROM sysobjects AS [so]
        INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
        WHERE [so].[type] = 'TR'
        
  3.  
    	    SELECT      o.[name],
                c.[text]
    		FROM        sys.objects AS o
            INNER JOIN  sys.syscomments AS c
            ON      o.object_id = c.id
            WHERE   o.[type] = 'TR'
        

No comments:

Post a Comment

| Designed by AppMuncher