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

Question

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 this feature, a "find in whole database" like in Visual Studio. Can a special sys-select help me find what I need?

Answer 1

Look in the sys.sql_modules information view. It will have the declarations for all of your stored procedures, functions, etc, etc. You can then link it to the actual type of construct through the object_id.

The definition column will contain the definition you used for the object.

Answer 2

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

@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

Answer 3

I use this one for work. leave off the []'s though in the @TEXT field, seems to want to return everything...

SET NOCOUNT ON

DECLARE @TEXT VARCHAR(250)
DECLARE @SQL VARCHAR(250)

SELECT  @TEXT='10.10.100.50'

CREATE TABLE #results (db VARCHAR(64), objectname VARCHAR(100),xtype VARCHAR(10), definition TEXT)

SELECT @TEXT as 'Search String'
DECLARE #databases CURSOR FOR SELECT NAME FROM master..sysdatabases where dbid>4
    DECLARE @c_dbname varchar(64)   
    OPEN #databases
    FETCH #databases INTO @c_dbname   
    WHILE @@FETCH_STATUS  -1
    BEGIN
     SELECT @SQL = 'INSERT INTO #results '
     SELECT @SQL = @SQL + 'SELECT ''' + @c_dbname + ''' AS db, o.name,o.xtype,m.definition '   
     SELECT @SQL = @SQL + ' FROM '+@c_dbname+'.sys.sql_modules m '   
     SELECT @SQL = @SQL + ' INNER JOIN '+@c_dbname+'..sysobjects o ON m.object_id=o.id'   
     SELECT @SQL = @SQL + ' WHERE [definition] LIKE ''%'+@TEXT+'%'''   
     EXEC(@SQL)
     FETCH #databases INTO @c_dbname
    END
    CLOSE #databases
DEALLOCATE #databases

SELECT * FROM #results order by db, xtype, objectname
DROP TABLE #results

Answer 4

I've used these in the past:

• Searching all user stored procedures for a table name
• Search and replace SQL Server data in all columns of all tables

In this particular case, where you need to replace a specific string across stored procedures, the first link is probably more relevant.

A little off-topic, the Quick Find add-in is also useful for searching object names with SQL Server Management Studio. There's a modified version available with some improvements, and another newer version also available on Codeplex with some other useful add-ins as well.