Search

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

No comments:

Post a Comment