Find text in any SQL object

Microsoft SQL ServerI 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

What should we write about next?





Your Name (required) –
Your Email (required) –

Leave a Reply

Your email address will not be published. Required fields are marked *