Post reply

Find all triggers that contain FACTS in trigger name

  • November 30, 2017 at 9:33 am

    #402717

    i cant seem to write a query to find all triggers in a database that contain the word FACTS in it

  • November 30, 2017 at 9:42 am

    #1970075

    Tried this?

    SELECT * FROM sys.[triggers] AS [t]
    WHERE [t].[name] LIKE '$FACTS%';

  • November 30, 2017 at 9:42 am

    #1970076

    When you say contain the word "FACTS", where does it contain it? In the name of the object (trigger)? The name of the table it is attached to? The word "FACTS" is in the SQL it runs?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 30, 2017 at 9:47 am

    #1970081

    Or perhaps this if you are looking for FACTS in the trigger definition.

    SELECT *
    FROM
      [sys].[sql_modules] AS [sm]
      INNER JOIN [sys].[triggers] AS [t]
        ON [t].[object_id] = [sm].[object_id]
    WHERE
      [sm].[definition] LIKE '%FACTS%';

  • November 30, 2017 at 9:48 am

    #1970082

    yes in the name of the trigger there are a lot of tables and we need a list of all triggers that contain the name FACT in the title of the trigger

  • November 30, 2017 at 9:50 am

    #1970083

    My first query.

  • November 30, 2017 at 9:53 am

    #1970085

    no FACTS will just be in the trigger name

  • November 30, 2017 at 9:54 am

    #1970086

    myukas - Thursday, November 30, 2017 9:53 AM

    no FACTS will just be in the trigger name

    Yes, the query will find all trigger names that contain the word FACTS.  The FIRST query I posted, not the second one.

  • November 30, 2017 at 9:56 am

    #1970088

    Lynn Pettis - Thursday, November 30, 2017 9:54 AM

    myukas - Thursday, November 30, 2017 9:53 AM

    no FACTS will just be in the trigger name

    Yes, the query will find all trigger names that contain the word FACTS.  The FIRST query I posted, not the second one.

    Sorry, just noticed a typo:

    SELECT OBJECT_NAME([t].[parent_id]),* FROM sys.[triggers] AS [t]
    WHERE [t].[name] LIKE '%FACTS%';

  • November 30, 2017 at 9:56 am

    #1970089

    thanks

  • November 30, 2017 at 10:07 am

    #1970090

    when i run that against the database it returns nothing but my client says they do exist
    Find the tables having Triggers starting with ‘FACTS’ and dump in excel
    USALL-RS03 in GPMGlobal, CDSAllentown, CDSBasel, CDSHorsham, CDSBillingAllentown, CDSBillingBasel, CDSBillingHorsham

  • November 30, 2017 at 10:11 am

    #1970091

    If Lynn's query is returning no results, then there are no triggers with the word "FACTS" in their name in that database.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • November 30, 2017 at 10:12 am

    #1970093

    Here is an idea, drop the WHERE clause and visually inspect the name column.

Viewing 13 posts - 1 through 12 (of 12 total)

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