Sunday, April 29, 2007

Which tables have a trigger/s?

I have been recently helping my fried on very large database ,which has hundred od tables to determine which table has a trigger. So I came out with the below script that may help others to identify triggers define on the tables.

--SQL Server 2000(SP3)

SELECT
name
, CASE WHEN instrig <> 0 then OBJECT_NAME (instrig) ELSE 'None' END as
'Insert Trigger'
, CASE WHEN updtrig <> 0 then OBJECT_NAME (updtrig) ELSE 'None' END as
'Update Trigger'
, CASE WHEN deltrig <> 0 then OBJECT_NAME (deltrig) ELSE 'None' END as
'Delete Trigger'
FROM
sysobjects
WHERE
type = 'U'
AND
( instrig <> 0
OR
updtrig <> 0
OR
deltrig <> 0
)

1 comment:

aiya said...
This comment has been removed by a blog administrator.