0

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?

Ali EXE
  • 31
  • 4
  • 4
    You can implement [DDL triggers](https://docs.microsoft.com/en-us/sql/relational-databases/triggers/ddl-triggers?view=sql-server-ver15) on the databases. – sticky bit Jan 06 '22 at 06: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 06 '22 at 07:56
  • 2
    Then use event notifications. A server level EN can capture database level events. https://www.mssqltips.com/sqlservertip/2121/event-notifications-in-sql-server-for-tracking-changes/ – Tibor Karaszi Jan 06 '22 at 07:58
  • 1
    Have you considered the [default trace](https://dba.stackexchange.com/questions/48052/what-event-information-can-i-get-by-default-from-sql-server) or a XE trace? – Dan Guzman Jan 06 '22 at 10:50
  • You have your answer - it is possible just not in a way that you desire. But this sounds like a big [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). Specifically you have management issues with people changing schemas without any sort of control (and documentation). A schema should be source-controlled. In addition a developer is likely to make many changes to a particular object over time as s/he refines the object and its design/usage. Surely you don't want useful notifications for that? – SMor Jan 06 '22 at 12:01
  • 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](https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/) (e.g. by Aaron Bertrand) or whatever procedural programming language you prefer to iterate over each database. – J.D. Jan 06 '22 at 12:35

0 Answers0