SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using SQL Server Dynamic Online Catalog to Solve Problems: Stairway to Exploring Database Metadata Level 2

By Phil Factor,

The Series

This article is part of the Stairway Series: Stairway to Exploring Database Metadata

In addition to the data that our clients and customers store in a database, there is a tremendous amount of meta data, 'data about data', that describes how the database is set up, configured, and what the properties are for the various objects. This stairway aims to demystify and explain how you can query and use this meta data to more effectively manage your SQL Server Databases.

This stairway is intended to appeal to a range of expertise and knowledge in SQL Server. Some of it will seem obvious to you but I aim to add information that will be new to even experienced DBAs or database developers: After all, some of the information surprised me. I will, however, be assuming a reasonable working knowledge of database objects and other types of metadata; in other words what a view, table, function, procedure, schema, constraint and so on actually is.

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.

SELECT schema_name(schema_ID)+'.'+ name

  FROM sys.system_views WHERE name LIKE '%trigger%'










(6 row(s) affected)

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

SELECT Thecol.name+ ' '+ Type_name(TheCol.system_type_id)

  + CASE WHEN TheCol.is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.system_views AS TheView

  INNER JOIN sys.system_columns AS TheCol

    ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers'

  ORDER BY column_ID;

Here are my results:



name nvarchar NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar NULL

parent_id int NOT NULL

type char NOT NULL

type_desc nvarchar NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

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.


  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( N'SELECT * FROM sys.triggers;', NULL, 0) AS f

  ORDER BY column_ordinal;

Here are my results:



name nvarchar(128) NOT NULL

object_id int NOT NULL

parent_class tinyint NOT NULL

parent_class_desc nvarchar(60) NULL

parent_id int NOT NULL

type char(2) NOT NULL

type_desc nvarchar(60) NULL

create_date datetime NOT NULL

modify_date datetime NOT NULL

is_ms_shipped bit NOT NULL

is_disabled bit NOT NULL

is_not_for_replication bit NOT NULL

is_instead_of_trigger bit NOT NULL

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.

Declare @TheParamater nvarchar(255)

Select @TheParamater = 'sys.triggers'

Select @TheParamater = 'SELECT * FROM ' + @TheParamater


  name+ ' '+ system_type_name

  + CASE WHEN is_nullable=1 THEN ' NULL' ELSE ' NOT NULL' END as Column_Information

FROM sys.dm_exec_describe_first_result_set

  ( @TheParamater, NULL, 0) AS f

  ORDER BY column_ordinal;

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

SELECT coalesce(trigger_column.name,'NOT INCLUDED') AS In_Sys_Triggers,

       coalesce(object_column.name,'NOT INCLUDED') AS In_Sys_Objects


 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'triggers') trigger_column


 (SELECT Thecol.name

  FROM sys.system_views AS TheView

    INNER JOIN sys.system_columns AS TheCol

      ON TheView.object_ID=TheCol.Object_ID

  WHERE  TheView.name = 'objects') object_column

ON trigger_column.name=object_column.name

Which gives the result …

In_Sys_Triggers                In_Sys_Objects

------------------------------ ----------------------

name                           name

object_id                      object_id

NOT INCLUDED                   principal_id

NOT INCLUDED                   schema_id

NOT INCLUDED                   parent_object_id

type                           type

type_desc                      type_desc

create_date                    create_date

modify_date                    modify_date

is_ms_shipped                  is_ms_shipped

NOT INCLUDED                   is_published

NOT INCLUDED                   is_schema_published

is_not_for_replication         NOT INCLUDED

is_instead_of_trigger          NOT INCLUDED

parent_id                      NOT INCLUDED

is_disabled                    NOT INCLUDED

parent_class                   NOT INCLUDED

parent_class_desc              NOT INCLUDED

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.


  name AS TriggerName,


    +object_name(parent_ID),'Database ('+db_name()+')') AS TheParent

FROM sys.triggers;

TriggerName                    TheParent

------------------------------ ----------------------------------------

ddlDatabaseTriggerLog          Database (AdventureWorks2012)          

dEmployee                      HumanResources.Employee                

iuPerson                       Person.Person                          

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail         

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader         

iduSalesOrderDetail            Sales.SalesOrderDetail                 

uSalesOrderHeader              Sales.SalesOrderHeader                 

dVendor                        Purchasing.Vendor                      

iWorkOrder                     Production.WorkOrder                   

uWorkOrder                     Production.WorkOrder                   

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

SELECT name as TriggerName, object_schema_name(parent_object_ID)+'.'

    +object_name(parent_object_ID) AS TheParent

            FROM   sys.objects

           WHERE  OBJECTPROPERTYEX(object_id,'IsTrigger') = 1

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:

name                           TheParent

------------------------------ -------------------------------

dEmployee                      HumanResources.Employee

iuPerson                       Person.Person

iPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderDetail           Purchasing.PurchaseOrderDetail

uPurchaseOrderHeader           Purchasing.PurchaseOrderHeader

iduSalesOrderDetail            Sales.SalesOrderDetail

uSalesOrderHeader              Sales.SalesOrderHeader

dVendor                        Purchasing.Vendor

iWorkOrder                     Production.WorkOrder

uWorkOrder                     Production.WorkOrder

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.)



    +object_name(parent_ID),'Database ('+db_name()+')')) AS 'Table', triggers,[KD1] [AC2] 

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEDeleteTriggerCount')) AS 'Delete',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEInsertTriggerCount')) AS 'Insert',

convert(SMALLINT,objectpropertyex(parent_ID, N'TABLEUpdateTriggerCount')) AS 'Update'

FROM (SELECT count(*) AS triggers, parent_ID FROM sys.triggers

      WHERE objectpropertyex(parent_ID, N'IsTable') =1

         GROUP BY parent_ID


which give me the following results:

Table                            triggers    Delete Insert Update

-------------------------------- ----------- ------ ------ ------

Purchasing.Vendor                1           0      0      0

Production.WorkOrder             2           0      1      1

Purchasing.PurchaseOrderDetail   2           0      1      1

Purchasing.PurchaseOrderHeader   1           0      0      1

Sales.SalesOrderDetail           1           1      1      1

HumanResources.Employee          1           0      0      0

Sales.SalesOrderHeader           1           0      0      1

Person.Person                    1           0      1      1



(8 row(s) affected)

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.

/* list the triggers, whether they are enabled and the trigger events */


  convert(CHAR(25),name) AS triggerName,


    +object_name(parent_ID),'Database ('+db_name()+')')) AS TheParent,


       CASE WHEN is_instead_of_trigger=1 THEN 'INSTEAD OF ' ELSE 'AFTER ' END

       +Stuff (--get a list of events for each trigger

        (SELECT ', '+type_desc FROM sys.trigger_events te

           WHERE te.object_ID=sys.triggers.object_ID

         FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,2,'') AS events

 FROM sys.triggers;

Here are my results:

triggerName               TheParent                        is_disabled events

------------------------- -------------------------------- ----------- ---------


t_AB                      dbo.AB                           0           INSTEAD OF INSERT

dEmployee                 HumanResources.Employee          0           INSTEAD OF DELETE

iuPerson                  Person.Person                    0           AFTER INSERT, UPDATE

iPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER INSERT

uPurchaseOrderDetail      Purchasing.PurchaseOrderDetail   0           AFTER UPDATE

uPurchaseOrderHeader      Purchasing.PurchaseOrderHeader   0           AFTER UPDATE

iduSalesOrderDetail       Sales.SalesOrderDetail           0           AFTER INSERT, UPDATE, DELETE

uSalesOrderHeader         Sales.SalesOrderHeader           0           AFTER UPDATE

dVendor                   Purchasing.Vendor                0           INSTEAD OF DELETE

iWorkOrder                Production.WorkOrder             0           AFTER INSERT

uWorkOrder                Production.WorkOrder             0           AFTER UPDATE

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.

SELECT convert(CHAR(32),coalesce(object_schema_name(t.object_ID)+'.','')

    +name) AS TheTrigger,


    +object_name(parent_ID),'Database ('+db_name()+')')) AS theParent,

       len(definition) AS length --the length of the definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID


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:

TheTrigger                       theParent                        length

-------------------------------- -------------------------------- --------

Sales.iduSalesOrderDetail        Sales.SalesOrderDetail           3666

Sales.uSalesOrderHeader          Sales.SalesOrderHeader           2907

Purchasing.uPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   2657

Purchasing.iPurchaseOrderDetail  Purchasing.PurchaseOrderDetail   1967

Person.iuPerson                  Person.Person                    1498

ddlDatabaseTriggerLog            Database (AdventureWorks2012)    1235

Purchasing.dVendor               Purchasing.Vendor                1103

Production.uWorkOrder            Production.WorkOrder             1103

Purchasing.uPurchaseOrderHeader  Purchasing.PurchaseOrderHeader   1085

Production.iWorkOrder            Production.WorkOrder             1011

HumanResources.dEmployee         HumanResources.Employee          604


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).

SELECT coalesce(object_schema_name(parent_id)

          +'.','')+convert(CHAR(32),name) AS TheTrigger,

          count(*) AS Dependencies

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

GROUP BY name, parent_id

ORDER BY count(*) DESC;

Here are my results:

TheTrigger                               Dependencies

---------------------------------------- ------------

Sales.iduSalesOrderDetail                7

Sales.uSalesOrderHeader                  7

Purchasing.iPurchaseOrderDetail          5

Purchasing.uPurchaseOrderDetail          5

Purchasing.uPurchaseOrderHeader          3

Production.iWorkOrder                    3

Production.uWorkOrder                    3

dbo.t_AB                                 2

Purchasing.dVendor                       2

Person.iuPerson                          2

ddlDatabaseTriggerLog                    1

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


  convert(char(32),name) as TheTrigger,



     as referencedObject

FROM sys.triggers

INNER JOIN sys.SQL_Expression_dependencies

ON [referencing_id]=object_ID

WHERE name LIKE 'iduSalesOrderDetail';

Here are my results:

TheTrigger                       referencedObject

-------------------------------- --------------------------------

iduSalesOrderDetail              Sales.Customer                 

iduSalesOrderDetail              Person.Person                  

iduSalesOrderDetail              Sales.SalesOrderDetail         

iduSalesOrderDetail              Sales.SalesOrderHeader          

iduSalesOrderDetail              Production.TransactionHistory  

iduSalesOrderDetail              dbo.uspLogError                

iduSalesOrderDetail              dbo.uspPrintError

What code is in that trigger?

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

SELECT OBJECT_DEFINITION ( object_id('sales.iduSalesOrderDetail') );

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.

SELECT definition

FROM sys.SQL_modules m

  INNER JOIN sys.triggers t

    ON t.object_ID=m.object_ID

WHERE t.object_ID=object_id('sales.iduSalesOrderDetail');

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?

/* search for a string within all triggers */

 SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +name) AS TheTrigger, '...'+substring(definition, hit-20,120) +'...'


  (SELECT name, definition, t.object_ID, charindex('EXECUTE [dbo].[uspPrintError]',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.triggers t

       ON t.object_ID=m.object_ID)f

WHERE hit>0;

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

/* search for a string within all objects */

SELECT convert(CHAR(32),coalesce(object_schema_name(object_ID)+'.','')

    +object_name(object_ID)) AS TheObject, type_desc, '...'+substring(definition,hit-20,120)+'...' as TheExtract


  (SELECT  type_desc, definition, o.object_ID, charindex('uspPrintError',definition) AS hit

   FROM sys.SQL_modules m

     INNER JOIN sys.objects o

       ON o.object_ID=m.object_ID)f

WHERE hit>0;

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

/* list the server triggers and their definitions*/

SELECT name, definition

FROM sys.server_SQL_modules m

  INNER JOIN sys.server_triggers t

ON t.object_ID=m.object_ID;

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


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.


This article is part of the Stairway to Exploring Database Metadata Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 1312 | Views in the last 30 days: 12
Related Articles

Building Parent-Child Table Tree information

How many times have you wanted to know which child or grandchild records exists for a parent record?...


SSMS disable Server Objects Trigger

How do you disable Server Objects Trigger in SSMS



Get information about the objects in your database using these ANSI standard, multi-platform views.


Find all triggers in a SQL Server Database

Reading Time: 2 minutes What Are Database Triggers? Do you know what triggers lurk in your databas...


Find all triggers in a SQL Server Database

What Are Database Triggers? Do you know what triggers lurk in your database? Triggers can be imp...

stairway series