Find Triggers and Associated Tables
-
Posted on March 28, 2012 by Derek Dieter
-
0
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:
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]
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]
You can also use only sysobjects:
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'
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'
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.
Post a comment
- Comments (RSS)
- Trackback
- Permalink
