In Level 1, I explained what interfaces are provided by SQL Server to allow you to access the information about the metadata of your database, why it was there, and how you’d use it. I ended up by showing you how to find out the names of all the various database objects in the database.

In this level we’ll show how you can go much further and find all sorts of useful information. I’ll use triggers as an example because they can cause so many problems.

So where is the information about triggers?

So how do we start to find out for ourselves rather than use an article like this? Well, the table sys.system_views is a good place to start. Let’s imagine that you want information about the way that triggers are being used in your database. This should tell you what there is in your version of SQL Server for studying triggers.

Hmm. sys,triggers looks promising. What are its columns? We can actually find out pretty easily with this query

Here are my results:

So now we have a better idea of the information that is available. Now, what we have here is a catalog of a catalog. This is a concept that might make your head swim but on the other hand, it is pretty simple in practice. We now can find out what is in the metadata the same way that we find out what is in the database. With this query, all you now need to do is to change the word ‘triggers’ to whatever system view name you wish and you have a list of columns.

In the latest versions of SQL Server from 2012 onwards, you can greatly simplify the above query using a new table-valued function, and avoid all the joins. In the following query we’re finding the columns in the sys.triggers view. You can use the same query to get the definition of any view by changing the object name in the string.

Here are my results:

The great advantage of the sys.dm_exec_describe_first_result_set function is you can see the columns of any result, not just tables, views, procedures or table-valued functions but from any query.

To find out the column information about any table or view, you can use this slightly-modified version, just changing the value in the string literal 'sys.triggers' in the second line to the name of the view or table from which you’d like the column information.

But surely a trigger is an object so it will be in sys.objects?

Before we make use of the fact that sys.triggers has the information we need, I need to explain that all objects in the database will be in sys.objectsview (see level 1 ‘Where’s my stuff’). In SQL Server, the following are considered to be objects and so will be represented by a row in sys.objects. aggregate CLR functions, check constraints, foreign key constraints, SQL scalar functions, CLR scalar-functions, CLR table-valued functions, SQL inline table-valued functions, internal tables, SQL stored procedures, CLR stored procedures, plan guides, primary key constraints, old-style (Sybase) rules, replication-filter-procedures, system base tables, synonyms, sequence objects, service queues, CLR DML triggers, SQL table-valued functions (TVFs), SQL DML triggers, table types, user-defined tables, unique constraints, views and extended stored procedures.

Triggers are objects so the basic information about triggers is held in sys.objects, and it is fine to use sys.objects. Unfortunately, we sometimes need extra information. Additional information is available in system views called object catalog views, because they refer to objects. What is this extra information?

Let’s modify the query we’ve used to find out the columns of sys.triggers. This time we’ll see what extra information is provided by sys.triggers. While we’re about it, we can see what information is in sys.objects that hasn’t been carried over to sys.triggers

Which gives the result …

This tells us that there is extra information in sys.triggers, but because it is always a child object of a table, there is irrelevant information in sys.objects that isn’t shown in the more specialised view, sys.triggers.

Now we know where to look, we’ll explore triggers.

Pesky triggers

Triggers can be useful. However, they tend to catch me out if I’m using SQL Server Management Studio because they aren’t particularly visible in the object explorer pane. OK, you may think you know all about them but there are subtle ways they can go awry, such as when some import process disables the triggers and for some reason they aren’t re-enabled.

Just so you know what we’ll be looking at, here is a brief reminder about triggers:

Triggers can be on views, tables, databases or servers. Any of these can have more than one trigger. The ordinary DML triggers can be defined to be executed instead of a data modification (INSERT, UPDATE, or DELETE ) or after a modification. Each trigger can be associated with one, and only one, view or table. DDL triggers are associated with databases or are defined at the server level, and are triggered after SQL statements such as CREATE, ALTER or DROP.

Like DML triggers, more than one DDL trigger can be created on the same Transact-SQL statement. A DDL trigger and the statement that fires it are run within the same transaction so anything other than an ALTER DATABASE can be rolled back.

Both DML and DDL triggers can be nested.

DDL triggers run only after a Transact-SQL statement is completed. DDL triggers cannot be used as INSTEAD OF triggers.

Both types of trigger are associated with events. In the case of DML triggers, these are INSERT, UPDATE, and DELETE, whereas a whole host of events can be associated with DDL triggers, as we will see in due course.

Listing the triggers in your database

So what triggers have I got? (I’ll be using AdventureWorks which unfortunately for us has no triggers on views)

All the information for this first report is in the sys.triggers catalog view.

I’m keeping the SQL simple by using metadata functions. db_name() tells me the name of the database (My connection is set to AdventureWorks2012: you can only look at the metadata that your connection entitles you to see). object_schema_name() tells you the schema of the object represented by object_ID, and object_name() tells me the object name. Here these references to an object refer to the owner of the trigger, which can be either the database itself, or the table: Server triggers have their own system view, which I’ll examine later.

The trigger that is associated with the database is, as we’ve explained, a DDL trigger but we’ve decided to include it in the list.

If we want to see all the triggers in a database, we don’t have to use sys.triggers, of course. It is fine to use the sys.objects view

Notice that the output now doesn’t include the database-level trigger. Because all the DML triggers are in the sys.objects view this will work fine for showing them, but of course you will miss out on the trigger-only details that are in the sys.triggers view.

Here are my results:

How many triggers do my tables and views have?

I wonder how many triggers each table has, and what event triggers each of them. Here we list the tables that have triggers and the number of triggers there are for each event. Each table or view can have one INSTEAD OF trigger for each triggering action which can be either UPDATE, DELETE, or INSERT. However, a table can have several AFTER triggers for each triggering action. These will show up in the following query (We have excluded views from the result.)

which give me the following results:

This is useful information, because if more than one trigger is fired by a particular event on a table, they are not fired in a guaranteed order, though it is possible to control the firing order for AFTER triggers by using the system stored procedure sp_settriggerorder. It is always worth checking whether you are introducing subtle bugs if triggers are chained on an event. You can determine which is last in a chain by using the objectpropertyex() metadata function with the parameter ‘ExecIsLastDeleteTrigger’, ‘ExecIsLastInsertTrigger’ or ‘ExecIsLastUpdateTrigger’ according to the event. To get the first in the chain, you use, as appropriate, ObjectPropertyEx() metadata function with the parameter ‘ExecIsFirstDeleteTrigger’, ‘ExecIsFirstInsertTrigger’ or ‘ExecIsFirstUpdateTrigger’.

So we now know what tables have triggers and what events are firing triggers on those tables. We used the objectpropertyex() metadata function. This functioncan return a lot of different information depending on the parameters specified..

It is always worth checking by looking at the documentation in MSDN to see if one of these will help with the metadata query you are creating.

When does the trigger fire on the event?

Let’s now view these triggers. A DML trigger can fire AFTER all the other events (the triggering action, INSTEAD OF triggers and constraints) are processed but can be made to fire INSTEAD OF the triggering action and before constraints are processed. We can now see precisely what events all these triggers are fired by, and whether they are AFTER or INSTEAD OF triggers.

Here are my results:

As you will notice, we used a FOR XML PATH(‘’) trick here to make a list of the events for each trigger to make it easier to read. These events were pulled from the sys.trigger_events view using a correlated subquery.

How long are these triggers?

Just out of interest, are these lengthy triggers? You won’t get many database people to agree on a definition of a lengthy trigger, but they’d probably find a list of triggers ordered by the length of their definition a useful way of investigating a database.

I’ve linked to the sys.SQL_modules view to see the SQL DDL for the definition of the triggers and listed them in order of size, with the biggest at the top.

Here are my results:

Hmm. I don’t like the look of that one at the top. 3666 characters long? OK, I’m probably ultra-fussy, but what is all that logic doing? In fact those top three look dodgy to me, though I always prefer to have as little logic in triggers as possible.

How many objects are these triggers accessing

How many objects (such as tables and functions) is each trigger accessing in that pesky code?

We just need to check on expression dependencies. This query uses a view that lists the ‘soft’ dependencies, the ones in code within modules/routines (such as triggers, views and functions).

Here are my results:

Heavens. A couple of triggers have seven dependencies! Can this be right? Let’s investigate Sales.iduSalesOrderDetail, the one at the top with seven dependencies.

What objects is a particular trigger accessing or writing to?

We can list all the objects that are referenced in code by the trigger

Here are my results:

What code is in that trigger?

So let’s now confirm that by checking the source code of the trigger.

Yes, our query has got it right. Scanning the source code we can see all of these dependencies. There are plenty of dependencies here for a trigger, which shows how careful one has to be with a database refactoring that involves, for example, altering the column of a base table. We’ll be talking more about dependencies later on in this stairway.

Depending on what you want to do, you might want to examine definitions from the metadata views rather than using the OBJECT_DEFINITION function.

Searching through the code of a trigger

There are always plenty of ways of using the metadata views and functions. I wonder if all these triggers are executing that uspPrintError procedure?

Here is the output.

This output gives you the name of the trigger. The text looks the same, and it has probably been pasted into each trigger from a boilerplate. The routine is showing a fixed number of characters before and after the string that was found (the ‘hit’), which is why the ‘BEGIN CATCH is missing the first character.

Eight of the references are executing that procedure. We’ve searched through all the definitions in sys.SQL_modules to find a particular string, which is slow and brute-force, but it is effective!

Searching through all objects for a string

I wonder if any other objects are calling that procedure besides the triggers? We alter the query slightly to search the sys.objects view rather than sys.triggers in order to search all the objects that have code associated with them. We also need to show the type of object

This gives the result …

From this output we can see that, other than the procedure itself where it is defined, and the triggers, only dbo.uspLogError is executing the uspPrintError procedure. (see the first column, second line down)

Listing server-level triggers and their definitions

What about server-level triggers? Can we find out anything about them via system views? Well, yes. Here is a routine that lists out every server trigger and their definition

Here again, you will only see the triggers that your permissions allow you to view

Summary

In this level we’ve talked about triggers, and about how you can find out about them, and about potential problems. At this stage, we don’t aim for a comprehensive toolkit of queries about triggers, because I’m just using triggers as an example to show some of the techniques that are possible when querying system views. We’ll come back to triggers after we’ve studied indexes, columns and parameters, and we’ve taken a look at some everyday uses for writing queries that access the system views and information schema views. Why aren’t we getting down to tables at this stage? This is because tables underlie so many aspects of the metadata. They are the parent of several types of object, and other metadata ‘things’ such as indexes are properties of tables. We are working slowly towards being able to find out all about tables, one level at a time.