I needed to find custom triggers or views which referenced a certain table and although I thought I had a script which would find text in a SQL object like a trigger, view or stored procedure, I couldn't find one when I searched my site the other day.
It only took me a few minutes to write one; the first highlighted text is the text to search for and the second a limitation on the name of the SQL objects to check.
/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
DECLARE @Search varchar(255)
SET @Search='PM00200'
SELECT DISTINCT
['SQL Objects'].name AS Object_Name
,['SQL Objects'].type_desc
FROM
sys.sql_modules AS ['SQL Modules']
INNER JOIN
sys.objects AS ['SQL Objects']
ON
['SQL Objects'].object_id=['SQL Modules'].object_id
WHERE
['SQL Objects'].name LIKE 'u%_AZRCRV_%'
AND
['SQL Modules'].definition LIKE '%'+@Search+'%'
ORDER BY
['SQL Objects'].name
,['SQL Objects'].type_desc
Can ISC Software Solutions help?
ISC Software Solutions are UK and Ireland based experts on Microsoft Dynamics GP. If you'd like to speak to someone about support, consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else Dynamics GP related, you can use the form below.