Post reply

How to list all triggers of all databases ?

  • June 9, 2008 at 11:50 am

    #192062

    I need know how to list all the triggers from all the databases hosted in my server. All the sp that i look run only over the current db. I use sql server 2000.

    Thanks in advance

  • June 9, 2008 at 12:02 pm

    #826241

    Try making use of sp_msforeachdb undocumented stored procedure.

    Manu

  • June 9, 2008 at 12:05 pm

    #826244

    Do you want to list their Names, or do want the listings (scripts) of all of the Triggers?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 9, 2008 at 1:12 pm

    #826289

    I need to list only the name of the triggers, the event of the trigger, and the table related with the trigger.

    thanks

  • June 9, 2008 at 1:30 pm

    #826302

    I cannot test SQL 2000, but I believe that this should work:

    Declare @sql varchar(MAX)

    Set @sql = ''

    Select @sql = @sql + 'USE ' + name + '

    select '''+name+''' as DBName, p.name as [TableName], o.name, o.deltrig, o.instrig, o.updtrig

    from sysobjects o

    left outer join sysobjects p ON p.id = o.parent_obj

    where o.type=''TR''

    '

    from master..sysdatabases

    Where DBID > 4--skip the system databases

    EXEC (@sql)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • June 9, 2008 at 2:47 pm

    #826349

    sp_msforeachdb 'use ? select db_name(), name from sysobjects where type = ''tr'''

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply