I want to run a query on each and every database that is change in whatsoever DDL change possible.

For example, if I have 10 databases, A, and B, and ... in my SQL Server instance, and if a developer creates a view in one of them, I want that query to run.

If another developer adds a column to another table in another database, I want that query to run.

Is it possible?

  • 4
    You can implement DDL triggers on the databases.
    – sticky bit
    Jan 6, 2022 at 6:54
  • @stickybit, I know about that. This means that on a server that has more than 200 databases, I need to do something 200 times. I'm looking for something that is at the server-level.
    – Ali EXE
    Jan 6, 2022 at 7:56
  • 2
    Then use event notifications. A server level EN can capture database level events. mssqltips.com/sqlservertip/2121/… Jan 6, 2022 at 7:58
  • 1
    Have you considered the default trace or a XE trace?
    – Dan Guzman
    Jan 6, 2022 at 10:50
  • 1
    Regarding your reply to SMor's original suggestion, you don't have to do something 200 times, it is possible to write code that deploys a change to all 200 databases but you only have to run such code once. That can be achieved in either pure SQL leveraging things like dynamic SQL or a better version of sp_MSforeachdb (e.g. by Aaron Bertrand) or whatever procedural programming language you prefer to iterate over each database.
    – J.D.
    Jan 6, 2022 at 12:35


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.