I would like to create a script that lists out all of the SQL code for the procedures and triggers I have created in a database.
What I want to do is save the code for each procedure/trigger as a *.SQL file without having to open up each one in Enterprise Manager and save them individually.
Listing the names is easy by looking in sysobjects, but is the SQL code stored in a system table anywhere?
Does anybody have any alternative approach to this problem?
Thanks in advance,
MarkI don't understand you problem. EM allows you to script out all the objects of any particular type(s) to separate files. There is nothing that limits you to one object at a time.
Showing posts with label triggers. Show all posts
Showing posts with label triggers. Show all posts
Friday, March 23, 2012
Monday, February 20, 2012
List all triggers for a table or an entire database
How can I get a list of all the triggers associated with a particular
table in a database. And in addition, how can I get a list of all
triggers for the entire database (meaning all triggers associated with
all tables)? Thanks!!
Regards,
Troy
Troy,
[vbcol=seagreen]
answered my own question... here's the query in case someone else is
wondering:
SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S1.deltrig
> 0 THEN 'Delete' WHEN S1.instrig > 0 THEN 'Insert' WHEN S1.updtrig > 0
THEN 'Update' END 'TriggerType' FROM sysobjects S1 JOIN sysobjects S2 ON
S1.parent_obj = S2.[id] WHERE S1.xtype='TR'
Regards,
Troy
table in a database. And in addition, how can I get a list of all
triggers for the entire database (meaning all triggers associated with
all tables)? Thanks!!
Regards,
Troy
Troy,
[vbcol=seagreen]
answered my own question... here's the query in case someone else is
wondering:
SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S1.deltrig
> 0 THEN 'Delete' WHEN S1.instrig > 0 THEN 'Insert' WHEN S1.updtrig > 0
THEN 'Update' END 'TriggerType' FROM sysobjects S1 JOIN sysobjects S2 ON
S1.parent_obj = S2.[id] WHERE S1.xtype='TR'
Regards,
Troy
Labels:
addition,
alltriggers,
associated,
database,
entire,
microsoft,
mysql,
oracle,
particulartable,
server,
sql,
table,
triggers
Subscribe to:
Posts (Atom)