Monday, November 13, 2006

Find tables with triggers

sql 2005 +
select 
schemas.name as schema_name,
tables.name as table_name,
triggers.name as trigger_name
from 
sys.objects tables
inner join sys.objects  triggers
on triggers.parent_object_id = tables.object_id
inner join sys.schemas schemas
on tables.schema_id = schemas.schema_id
where triggers.type_desc = 'SQL_TRIGGER'

sql 2000
select
       object_name(parent_obj) as tablename
      ,name as triggername
from sysobjects
where type = 'tr'
order by 1, 2

No comments: