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

sql-server stored-procedures triggers linked-server
Victor Rodrigues picture Victor Rodrigues · Mar 23, 2009 · Viewed 314.3k times · Source

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?

Answer

KM. picture KM. · Mar 23, 2009

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

Related questions

SQL Transaction was deadlocked

Sometimes I get this kind of exception on not very busy SQL server: Transaction (Process ID 57) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Line number: 1 Error Number: 1205 Procedure: …

Read More
How To Create Generic SQL Server Stored Procedure To Perform Inserts Into Audit Table Based on Inserted and Deleted In Trigger

I have implemented an audit trail framework based on the information provided by the first answer to the following post: SQL Server history table - populate through SP or Trigger? Ultimately, the framework that I have implemented uses three triggers …

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More