Find Triggers and Associated Tables

These simple snippets of code will show all triggers in the current database, along with the tables they belong to. Unfortunately the INFORMATION_SCHEMA views do not show triggers so we need to revert to using less documented options. The proper way would be to use the INFORMATION_SCHEMA.TRIGGERS view, however SQL Server has yet to create and publish this view. In the meantime we need to use one of the following workarounds:

[cc lang=”sql”]
SELECT
table_name = so.name
,trigger_name = st.name
,trigger_text = sc.text
,create_date = st.create_date
FROM sys.triggers st
JOIN sysobjects so
ON st.parent_id = so.id
JOIN syscomments sc
ON sc.id = st.[object_id]
[/cc]

You can also use only sysobjects:

[cc lang=”sql”]
SELECT
table_name = so2.name
,trigger_name = so.name
,trigger_text = sc.text
,create_date = so.crdate
FROM sysobjects so
JOIN sysobjects so2
ON so.parent_obj = so2.id
JOIN syscomments sc
ON sc.id = so.id
WHERE so.type = ‘tr’
[/cc]

I would imagine that SQL Server will provide more documented ways to query this information, until then we need to take a chance by deploying these solutions.