Stairway to Exploring Database Metadata

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


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 ' '+ 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 = '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(,'NOT INCLUDED') AS In_Sys_Triggers,


       coalesce(,'NOT INCLUDED') AS In_Sys_Objects






  FROM sys.system_views AS TheView


    INNER JOIN sys.system_columns AS TheCol


      ON TheView.object_ID=TheCol.Object_ID


  WHERE = 'triggers') trigger_column






  FROM sys.system_views AS TheView


    INNER JOIN sys.system_columns AS TheCol


      ON TheView.object_ID=TheCol.Object_ID


  WHERE = 'objects') object_column




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 parent stairway Stairway to Exploring Database Metadata


5 (1)




5 (1)