sql-serverstored-procedurestriggerslinked-server

How to find a text inside SQL Server procedures / triggers?


I have a linkedserver that will change. Some procedures call the linked server like this: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE. We have triggers also doing this kind of work. We need to find all places that uses [10.10.100.50] to change it.

In SQL Server Management Studio Express, I didn't find a feature like "find in whole database" in Visual Studio. Can a special sys-select help me find what I need?


Solution

  • here is a portion of a procedure I use on my system to find text....

    DECLARE @Search varchar(255)
    SET @Search='[10.10.100.50]'
    
    SELECT DISTINCT
        o.name AS Object_Name,o.type_desc
        FROM sys.sql_modules        m 
            INNER JOIN sys.objects  o ON m.object_id=o.object_id
        WHERE m.definition Like '%'+@Search+'%'
        ORDER BY 2,1