Retrieving code from a trigger in the database

I have inherited a database and with it a set of triggers that are 'apparently' installed. However I have a number of different 'versions' for a specific update trigger and it is not clear from the files which is the latest, hence I am unsure of what 'version' of the trigger is installed in the database itself.

I am trying to retrieve the trigger code from the databse so that i can correctly determine which one of my trigger files is the latest version.

I don't want to end up in a situation where an earlier version of the update trigger is installed as this could wreck the underlying data.

I have tried using the syscomments.text column that relates to the trigger but it only gives me a concatenated version of the sql code and not enough to determine which file version was used for the trigger.

Is there a way of retreiving the entire SQL code that was entered rather than just the first few lines?

Hi,

use the INFORMATION_SCHEMA.Routines for that.

SELECT Routine_definition
FROM INFORMATION_SCHEMA.Routines
Where Routine_Name = 'SomeTriggerName'

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de
|||You can use sp_helptext to get the text for a trigger and other objects.
exec sp_helptext thenameofyourtrigger;|||

Hi,

this may sound dumb (first post here :) ), but why can't you just right-click the table in EM and go All Tasks -> Manage triggers? It gives you a nice syntax-highlighted view of all triggers assigned to a table.

|||Here is a simple code to get the trigger text in a nice format. One thing you need to do is to export the results to text. To do this right click in your sql pane (SQL 2005) and Results to ---> Results to text.

SELECT text FROM syscomments a INNER JOIN sysobjects b ON b.id = a.id and b.type = 'TR' AND b.name = <your trigger name in single quotes>

You can enhace the above to query to loop through all the triggers in a given table.

Hope this helps
Sri Kondeti

Retrieve list of tables without triggers

I need to do some db cleaning, how can I get a list of tables within the
database that do not have any triggers.
Thanks in advanceHi scuba79,
Try the following on your database:
select name from sysobjects
where xtype = 'U'
and id not in
(
select parent_obj from sysobjects
where xtype = 'TR'
)
xtype = 'U' will return all the user tables within your database
xtype = 'TR' are trigger objects.
Every trigger belongs to a table/parent and the parent object id is store in
sysobjects.parent_obj. By using this info we can filter out all the user
tables that have triggers.
Hope this help.
"scuba79" wrote:

> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance|||Here is one way
SELECT * FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
FROM
sysobjects o
WHERE
OBJECTPROPERTY(o.[id], 'IsTrigger') = 1
) tr ON t.TABLE_NAME= tr.TableName
WHERE tr.TableName IS NULL
http://sqlservercode.blogspot.com/|||Little correction I forgot to filter out the views
SELECT t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES t
LEFT JOIN(
SELECT
TableName = OBJECT_NAME(o.parent_obj)
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'
http://sqlservercode.blogspot.com/|||assuming sql2000:
select user_name(uid) as table_schema, name as table_name
from sysobjects so
where xtype='U'
and objectproperty(id,'IsMSShipped')=0
and not exists (
select *
from sysobjects
where xtype='TR'
and parent_obj = so.id
)
order by table_name
scuba79 wrote:
> I need to do some db cleaning, how can I get a list of tables within the
> database that do not have any triggers.
> Thanks in advance