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?

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

<pre>
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
</pre>

I've used these in the past:

* [Searching all user stored procedures for a table name][1]
* [Search and replace SQL Server data in all columns of all tables][2]

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][3] is also useful for searching object names with SQL Server Management Studio. There's a [modified version][4] available with some improvements, and another [newer version][5] also available on Codeplex with some other useful add-ins as well.

[1]:http://groups.google.com/group/comp.databases.ms-sqlserver/msg/6f53a7cfc6ae0db0
[2]:http://vyaskn.tripod.com/sql_server_search_and_replace.htm
[3]:http://jcooney.net/archive/2007/11/26/55358.aspx
[4]:http://www.karpach.com/ViewArticle.aspx?ArticleFileName=SQL-Server-Managment-Studio-Object-Explorer-Search-Add-In.htm
[5]:http://www.codeplex.com/SSMSAddins

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

This will work for you:

use [ANALYTICS] ---> put your DB name here
GO
SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
where sm.definition like '%SEARCH_WORD_HERE%' collate SQL_Latin1_General_CP1_CI_AS
ORDER BY o.type;
GO

-- Declare the text we want to search for
DECLARE @Text nvarchar(4000);
SET @Text = 'employee';

-- Get the schema name, table name, and table type for:

-- Table names
SELECT
TABLE_SCHEMA AS 'Object Schema'
,TABLE_NAME AS 'Object Name'
,TABLE_TYPE AS 'Object Type'
,'Table Name' AS 'TEXT Location'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'+@Text+'%'
UNION
--Column names
SELECT
TABLE_SCHEMA AS 'Object Schema'
,COLUMN_NAME AS 'Object Name'
,'COLUMN' AS 'Object Type'
,'Column Name' AS 'TEXT Location'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%'+@Text+'%'
UNION
-- Function or procedure bodies
SELECT
SPECIFIC_SCHEMA AS 'Object Schema'
,ROUTINE_NAME AS 'Object Name'
,ROUTINE_TYPE AS 'Object Type'
,ROUTINE_DEFINITION AS 'TEXT Location'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%'+@Text+'%'
AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure');

There are **much** better solutions than modifying the text of your stored procedures, functions, and views each time the linked server changes. Here are some options:

1. Update the linked server. Instead of using a linked server named with its IP address, create a new linked server with the name of the resource such as `Finance` or `DataLinkProd` or some such. Then when you need to change which server is reached, update the linked server to point to the new server (or drop it and recreate it).

2. While unfortunately you cannot create synonyms for linked servers or schemas, you CAN make synonyms for objects that are located on linked servers. For example, your procedure `[10.10.100.50].dbo.SPROCEDURE_EXAMPLE` could by aliased. Perhaps create a schema `datalinkprod`, then `CREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;`. Then, write a stored procedure that accepts a linked server name, which queries all the potential objects from the remote database and (re)creates synonyms for them. All your SPs and functions get rewritten just once to use the synonym names starting with `datalinkprod`, and ever after that, to change from one linked server to another you just do `EXEC dbo.SwitchLinkedServer '[10.10.100.51]';` and in a fraction of a second you're using a different linked server.

There may be even more options. I highly recommend using the superior techniques of pre-processing, configuration, or indirection rather than changing human-written scripts. Automatically updating machine-created scripts is fine, this is preprocessing. Doing things manually is awful.

<pre><code>select text
from syscomments
where text like '%your text here%'</code></pre>

*[Late answer but hopefully usefull]*

Using system tables doesn't always give 100% correct results because there might be a possibility that some stored procedures and/or views are encrypted in which case you'll need to use [DAC][2] connection to get the data you need.

I'd recommend using a third party tool such as [ApexSQL Search][1] that can deal with encrypted objects easily.

Syscomments system table will give null value for text column in case object is encrypted.


[1]:

[To see links please register here]

[2]:

[To see links please register here]

Any searching with select statement yield you only object name, where search keyword contains.
Easiest and efficient way is get script of procedure/function and then search in generated text file, I also follows this technique :) So you are exact pinpoint.