How can I know which tables "compose" a view?

Question

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?

Answer 1

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

Answer 2

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

Essentially, you run "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'

Answer 3

You should use

• OBJECT_DEFINITION
• sys.all_sql_modules
• sys.sql_expression_dependencies
• Red Gate SQL Search (free)

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