Get trigger information

Hi Freinds,
SQL 2000
I need to find out that if any of triggers on my database has a word
"dup_order" in it
is ther eany schema lime infromation_schema.column_name that I can use to
scan all triggers and find out which one contains the word?
Thanks in advance,
PatYou can search syscomments system table which has the source code for all
the user defined triggers in your database.
Anith|||SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsTrigger')=1
AND [text] LIKE '%dup[_]error%'
Because triggers that are > 8000 characters will span multiple rows, and
since the text "dup_order" might only appear in such a position that it
straddles rows, a better way might be to have all your triggers scripted out
using Enterprise Manager, and do a search within the output.
I am not sure exactly how SQL Server internally maps a trigger to belong to
a certain table (it may be embedded in the hideous ctext column somehow?).
You can certainly read/parse the ON section of the trigger, and that will
tell you, but doing so programmatically might not be 100% effective.
In SQL Server 2005 we will have more foolproof methods for scanning the
entire definition of an object, rather than deal with this 8000-character
chunk limitation.
"Patrick" <patriarck@.gmail.com> wrote in message
news:uU1sk%230oFHA.3304@.tk2msftngp13.phx.gbl...
> Hi Freinds,
> SQL 2000
> I need to find out that if any of triggers on my database has a word
> "dup_order" in it
> is ther eany schema lime infromation_schema.column_name that I can use to
> scan all triggers and find out which one contains the word?
> Thanks in advance,
> Pat
>|||To overcome the 4000 characters limitation, use the following procedure
to search for a string in all views, procedure, triggers and UDF-s:
CREATE PROCEDURE sp_findtext(@.Text nvarchar(256))
AS
SET @.Text=Replace(Replace(Replace(@.Text,
'[', '[[]'), '%', '[%]'), '_', '[_]')
SELECT name, xtype FROM (
SELECT id FROM syscomments WHERE text LIKE '%'+@.Text+'%'
UNION
SELECT c1.id FROM syscomments c1
INNER JOIN syscomments c2 ON c1.id=c2.id AND c1.colid=c2.colid-1
WHERE SUBSTRING(c1.text,3001,1000)+LEFT(c2.text,1000)
LIKE '%'+@.Text+'%'
) U INNER JOIN sysobjects o ON u.id=o.id
ORDER BY xtype, name

> I am not sure exactly how SQL Server internally maps
> a trigger to belong to a certain table
See the parent_obj column in the sysobjects table.
Razvan