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?
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 '%'[email protected]+'%' ORDER BY 2,1
Read more... Read less...
[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 connection to get the data you need.
I'd recommend using a third party tool such as ApexSQL Search that can deal with encrypted objects easily.
Syscomments system table will give null value for text column in case object is encrypted.
-- 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' 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 '%'[email protected]+'%' AND (ROUTINE_TYPE = 'function' OR ROUTINE_TYPE = 'procedure');
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
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:
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
DataLinkProdor 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).
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_EXAMPLEcould by aliased. Perhaps create a schema
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.