Post reply

Querying the sys tables to find out those triggers not for replication

  • January 5, 2007 at 7:34 am

    #681469

    select name, replinfo from sysobjects where xtype='TR'

    this will return all triggers, and a zero or one value for the replinfo.

    if replinfo is [zero], the "not for replication" is true.

    dbnametriggernamereplinfo
    msdbtrig_backupset_delete0
    msdbtrig_targetserver_insert0
    msdbtrig_notification_ins_or_upd0
    msdbtrig_notification_delete0

    edited: this will give the db name and the table the trigger is related to:

    select db_name() as db,object_name(parent_obj) as tablename,name, replinfo from sysobjects where xtype='TR'

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 8, 2007 at 10:52 am

    #681838

    hi Lowell,

    Thank you for your help.  I tested your script but all the results are shown to be 0, eve the ones that are true.  I modified the query so that it will only show those generated by the user instead of the system.

    select db_name() as db,object_name(parent_obj) as tablename,name, replinfo

    from sysobjects

    where xtype='TR' and name not like '%trg%'

    Thanks,

     

    Netania

     

  • January 8, 2007 at 12:11 pm

    #681852

    im sorry i kind of confused you; i got the vbalues inverted.

    0 in the replinfo means not replicated.

    a non zero value means it is being replicated.

    here are some of the values I know for replinfo values:

    1=transactional - table or indexed view - log based

    3=transactional - table or indexed view - log based with custom sync object

    33=transactional - table or indexed view - immediate updating

    35=transaction- table or indexed view - log based with custom sync object and custom filter

    64 - procs used by immediate updating

    128 = merge - table or indexed view

    129 =merge and transactional - table or indexed view

    512 - all other procsprocs, views, functions

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 15, 2007 at 10:51 am

    #683305

    That was great.  Thanks once again for your help.

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

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