Find All Tables With Triggers In SQL Server
From Wiki
Sometimes you want to change tables by adding columns, dropping or changing column types You want to make sure that there aren't any tables that have triggers on them so that stuff doesn't start to break after you make these changes I present two ways to accomplish that, the first way is by joining the INFORMATION_SCHEMA.TABLES view with the sysobjects system table. The second way is to do a self join on the sysobjects system table. Both of these queries will return the same result
- SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
- JOIN(SELECT OBJECT_NAME(o.parent_obj) AS TableName
- FROM sysobjects o
- WHERE OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
- ) tr ON t.TABLE_NAME= tr.TableName
- WHERE TABLE_TYPE ='BASE TABLE'
- ORDER BY t.TABLE_NAME
- SELECT s1.name FROM sysobjects s1
- JOIN sysobjects s2 ON
- s1.id =s2.parent_obj
- AND s2.xtype = 'TR'
- WHERE s1.xtype = 'U'
- ORDER BY s1.name
Contributed by: --SQLDenis 16:17, 9 June 2008 (GMT)
Part of SQL Server Admin Hacks
