July 27, 2010 at 6:39 am
Hi,
Does anyone know how can i export/query all the triggers' DDLs of certain database to grid/file (Server 2000)?
(basically to get them into sql file so i could run that sql on another server and reproduce the triggers).
Best Regards,
Silver
July 27, 2010 at 7:53 am
this will give you the trigger name and the definition for everything in a given database:
--SQL 2000:
select
sysobjects.name,
syscomments.text
from syscomments
inner join sysobjects on syscomments.id=sysobjects.id
where sysobjects.xtype='TR'
--SQL 2005/2008
select
object_name(object_id) as TriggerName,
definition from sys.sql_modules
where object_id IN(select object_id from sys.objects where type='TR')
Lowell
July 27, 2010 at 9:09 am
You can script everything with a right click/Generate script.
But I think Lowell's method might be the quickest way to do this and easier to edit if you need to.
July 28, 2010 at 12:17 am
Thanks a lot, that was what i was looking for.
Silver.
September 7, 2015 at 9:46 am
This is the script I use. It retains formatting and won't encounter problems with lines splitting mid-command, plus it's easy to filter by trigger type.
DECLARE @t VARCHAR (MAX)
SET @t = ''
SELECT @t = @t + 'IF EXISTS (SELECT 1 FROM sys.triggers WHERE object_id = OBJECT_ID(N''' + s.name + '.' + o.name +'''))
DROP TRIGGER ' + s.name + '.' + o.name + '
GO
' + OBJECT_DEFINITION (OBJECT_ID( s.name + '.' + o.name )) +'
GO
'
FROM sys.objects o
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN sys.objects o2 ON o.parent_object_id = o2.object_id
WHERE o. [type] = 'TR'
AND (
OBJECTPROPERTY ( o.object_id , 'ExecIsInsertTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsUpdateTrigger' ) = 1
OR
OBJECTPROPERTY ( o.object_id , 'ExecIsDeleteTrigger' ) = 1
)
SELECT @t AS [processing-instruction(x)] FOR XML PATH ('')
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy