Search

Showing posts with label Find all tables without Triggers in SQL Server. Show all posts
Showing posts with label Find all tables without Triggers in SQL Server. Show all posts

Monday, July 14, 2014

Find all tables without Triggers in SQL Server

Sometime you need to know all tables without trigger before adding, changing  or dropping column in a table.  Here are two ways to know that:

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

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