Recently someone asked my wife Deborah if she knew if my sp_helpExpandView utility procedure could also work with triggers. Great question – I rarely work with triggers, especially nowadays, so I had no idea… and finally found some time and motivation to dig into it deeper.
TL;DR – How Does sp_helpExpandView Work with Triggers?
- If you run sp_helpExpandView whose parameter IS a trigger, you’ll get information about the objects that the trigger references. However, you will not get information about the table that the trigger is associated with (which could be derived from another query).
- If you run sp_helpExpandView whose parameter is a table that HAS a trigger, you will NOT get any information about that trigger being present. You’ll have to write a different query for that.
- If you run sp_helpExpandView whose parameter is a table that is UTILIZED BY a trigger, you WILL get information about that trigger.
Digging Deeper
To dig deeper, I set up a quick prototype with two tables and a basic INSERT trigger.
USE TempDB;
GO
CREATE TABLE dbo.TableWithADMLTrigger (
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
MyValue VARCHAR(50),
MyTimestamp DATETIME DEFAULT(GETDATE())
);
CREATE TABLE dbo.TriggerAuditTable (
RecID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
InsertedRecID INT,
MyValue VARCHAR(50),
MyTimestamp DATETIME
);
GO
CREATE OR ALTER TRIGGER dbo.tr_InsertTriggerForAuditing
ON dbo.TableWithADMLTrigger
AFTER INSERT
AS
BEGIN
INSERT INTO dbo.TriggerAuditTable (
InsertedRecID, MyValue, MyTimeStamp
)
SELECT inserted.RecID, inserted.MyValue, inserted.MyTimestamp
FROM inserted
END
GO
INSERT INTO dbo.TableWithADMLTrigger (MyValue) VALUES ('alpha');
GO 3
SELECT *
FROM dbo.TableWithADMLTrigger;
SELECT *
FROM dbo.TriggerAuditTable;
GONow, let’s try running sp_helpExpandView against each entity.
EXEC sp_helpExpandView 'dbo.TableWithADMLTrigger';
GO
EXEC sp_helpExpandView 'dbo.TriggerAuditTable';
GO
EXEC sp_helpExpandView 'dbo.tr_InsertTriggerForAuditing';
GO
The first two commands will yield nothing. Here’s the output of the third, against the trigger itself. Note that it shows you the dbo.TriggerAuditTable that is used by the trigger, but not the table that the trigger itself is defined on – dbo.TableWithADMLTrigger.
The reason for this has to do with the underlying code’s use of sys.dm_sql_referenced_entities(). There are actually two of these DMFs in SQL Server, sys.dm_sql_referenced_entities() and sys.dm_sql_referencing_entities(). Note the critical difference, referencED and referencING.
Confused? Me too – which is why I have to re-explain the difference to myself every time I revisit this topic. Hopefully this summary helps you.
- Referenced Entities: Return all objects that are referenced by the object parameter passed in.
ex: dbo.MyStoredProcedure that has a SELECT * FROM dbo.MyTable, dbo.MyTable is referenced by the stored procedure and would appear in the resultset from a query against sys.dm_sql_referenced_entities() - Referencing Entities: Returns all objects that are referencing the object parameter passed in.
ex: If dbo.MyTable is passed, then dbo.MyStoredProcedure would appear in the resultset from a query against sys.dm_sql_referencing_entities()
Interestingly enough, not all objects are supported by both DMFs. See the Remarks section of the documentation of either DMF for a full table. But the consequence for sp_helpExpandView is that if you use sp_helpExpandView against tables that are both referenced by or referencing a trigger, it will not yield any output.
Partial Solution via sys.dm_sql_referencing_entities()
You can use sys.dm_sql_referencing_entities() to see triggers that reference a table. Here’s more example code.
DECLARE @TableName NVARCHAR(256) = 'dbo.TriggerAuditTable';
SELECT-- DISTINCT
DB_NAME() AS database_name,
@TableName AS table_name,
COALESCE(schemas.name, dm_sql_referencing_entities.referencing_schema_name) + '.' + dm_sql_referencing_entities.referencing_entity_name AS object_referencing_this_table,
COALESCE(schemas.type, NULL) AS referencing_object_type,
dm_sql_referencing_entities.referencing_id AS referencing_object_id
FROM sys.dm_sql_referencing_entities(@TableName, 'OBJECT')
LEFT OUTER JOIN (
SELECT objects.object_id, schemas.name, objects.type
FROM sys.objects
INNER JOIN sys.schemas
ON objects.schema_id = schemas.schema_id
) schemas
ON dm_sql_referencing_entities.referencing_id = schemas.object_id;
You can combine the above plus code that returns all triggers defined on a given table, to give you a bigger picture of trigger impact and relationships. Someday I may try to integrate this into sp_helpExpandView. Or I would absolutely welcome and credit anyone who wants to modify the code and issue a Pull Request on Github.
Thanks for reading.
Every Byte Counts 
