Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.
Find All Tables Without 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
- LEFT 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 tr.TableName IS NULL
- AND TABLE_TYPE ='BASE TABLE'
- ORDER BY t.TABLE_NAME
- SELECT s1.name FROM sysobjects s1 LEFT JOIN sysobjects s2 ON
- s1.id =s2.parent_obj
- AND s2.xtype = 'TR'
- WHERE s2.name IS NULL
- AND s1.xtype = 'U'
- ORDER BY s1.name
Contributed by: --SQLDenis 20:41, 29 May 2008 (GMT)
Part of SQL Server Programming Hacks and SQL Server Admin Hacks
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.