Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
exec sp_helptext 'sys.database_mirroring'. This isn't going to help you though. You still won't be able to create triggers on the source objects in the sys schema. –  Martin Smith Dec 26 '11 at 16:11
    

3 Answers 3

up vote 3 down vote accepted

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:

SELECT TABLE_NAME as ViewName, VIEW_DEFINITION as ViewDefinition
FROM 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'
share|improve this answer
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 Douglas Dec 26 '11 at 16:51
    
Good point about the link going stale, @JackDouglas. Edit completed. –  BryceAtNetwork23 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. –  BryceAtNetwork23 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

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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