Wednesday, July 22, 2009

Find a text inside stored procedure, trigger, view, etc.

Sometimes, you need to find a specific text in your stored procedures, this script may be useful to help you.


SELECT DISTINCT t2.name [Object Name],
CASE t2.type
WHEN'U' THEN 'User Table'
WHEN'S' THEN 'System Table'
WHEN 'V' THEN 'View'
WHEN 'TR' THEN 'Trigger'
WHEN 'P' THEN 'Stored Procedure'
WHEN 'C' THEN 'Constraint - Check'
WHEN 'D' THEN 'Default'
WHEN 'K' THEN 'Primary Key'
WHEN 'F' THEN 'Foreign Key'
WHEN 'L' THEN 'Log'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication Filter stp'
ELSE 'Other: ''' + t2.type+ ''''
END [Object Type],
t2.crdate [Created Date]
FROM syscomments t1
INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.[text] LIKE '%Your text need searching%'

No comments:

Post a Comment

Oracle Query