1

I have two schemas on a database named DBO and Switch. I have created this DDL trigger that logs DDL changes on DBO to a table. I then check if the objects affected by the DDL are partitioned or not and if yes I run the same DDL on the other schema Switch. This approach works fine unless the user doesn't specify the schema name in the DDl statement.

I am looking for a way to check the issued DDl for the presence of schema name, and if not present then issue the DDL by default on SWitch schema. Any ideas on how can I achieve this? I am new to TSQL coding and would appreciate anyone helping me out with this one.

AdamL
  • 12,421
  • 5
  • 50
  • 74
sdave
  • 11
  • 2

1 Answers1

0

In EVENTDATA available to use in DDL Trigger, there are following fields (among others) available:

<EVENT_INSTANCE>
  <DatabaseName>RT-BOOKS</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>Books_PL$ID</ObjectName>
  <TSQLCommand>
    <CommandText>
    </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

If <SchemaName> is not enough for you (if understood correctly, you want to check if user explicitly used schema name in a query), then try to parse <CommandText> using <SchemaName> and <ObjectName>.

AdamL
  • 12,421
  • 5
  • 50
  • 74