I want to create a trigger on SYS.database_mirroring as it is not a real table, just a view... I can't do it.

I want to know were the field mirroring_state_desc is coming from so that I can create a trigger on that table when it is changed.

How can I know which tables are used to create that view?


Here's a link to an article that shows a few different ways to show VIEW code. Hope this helps.

Basically, (as @Martin Smith mentioned) you can do a "exec sp_helptext [view_name]"; obviously exchanging [view_name] with the name of your view.

From with SQL Server Management Studio, you can locate the view in the "views" node, right-click on it, and obtain the code behind it by clicking "CREATE TO" and then a location.

Lastly, you can run the following SQL against INFORMATION_SCHEMA.Views:


Of course, given the limit of 4000 characters, a better option would be:

SELECT name, object_definition(object_id)
FROM sys.objects
WHERE [type] 'v'
  • 1
    Thanks for this helpful contribution. Please would you consider fleshing out the answer with a brief summary of the link to make it even more helpful here, especially if the link rots at some point in the future :-) – Jack says try topanswers.xyz Dec 26 '11 at 16:51
  • Good point about the link going stale, @JackDouglas. Edit completed. – Aaron Dec 26 '11 at 17:09
  • -1 for information_schema views. – gbn Dec 26 '11 at 19:09
  • @gbn I see...so if the CREATE VIEW statement was larger than 4000 characters you won't get it all with this. – Aaron Dec 26 '11 at 19:36
  • Multiple lines. The main issue is searching for references on the 4000 char limit: you'll miss them – gbn Dec 26 '11 at 20:15

To take some action when a database mirroring state change occurs, you have several options including WMI and Event Notifications (using the DATABASE_MIRRORING_STATE_CHANGE event):

Altering on Database Mirroring Events

If you are doing any sort of work with mirroring, I can recommend (MVP & MCM) Robert Davis' book:

Pro SQL Server 2008 Mirroring


You should use

You don't use

  • INFORMATION_SCHEMA because the VIEW_DEFINITION column is nvarchar(4000) and you may not get all data
  • sp_helptext because you get multiple lines of nvarchar(255)

But you won't be able to set up a trigger on the sys schema

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.