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

User · Answer

This one i tried in SQL2008  which can search from all the db at one go   Create table  temp1   ServerName varchar 64   dbname varchar 64   spName varchar 128  ObjectType varchar 32   SearchString varchar 64    Declare  dbid smallint   dbname varchar 64    longstr varchar 5000  Declare  searhString VARCHAR 250   set   searhString  firstweek   declare db cursor cursor for  select dbid   name   from master  sysdatabases where  name  not in   master    model    msdb    tempdb    northwind    pubs      open db cursor fetch next from db cursor into  dbid   dbname  while    fetch status   0  begin     PRINT  DB    dbname     set  longstr    Use      dbname   char 13                     insert into  temp1     char 13               SELECT   ServerName          dbname        Name            case  when  Type     P   Then   Procedure                   when Type     V   Then   View                   when  Type      TF   Then   Table-Valued Function                    when  Type      FN   Then   Function                    when  Type      TR   Then   Trigger                    else  Type     Others                     end                  searhString      FROM   SYS   SYSCOMMEnTS          JOIN   SYS  objects ON ID   object id         WHERE TEXT LIKE         searhString           exec   longstr   fetch next from db cursor into  dbid   dbname end  close db cursor deallocate db cursor select   from  temp1 Drop table  temp1

User · Answer

This will work for you   use  ANALYTICS   --- gt  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can search within the definitions of all database objects using the following SQL   SELECT      o name       o id       c text      o type FROM      sysobjects o  RIGHT JOIN syscomments c      ON o id   c id  WHERE      c text like   text to find

User · Answer

You can find it like   SELECT DISTINCT OBJECT NAME id  FROM syscomments WHERE  text  LIKE   User     It will list distinct stored procedure names that contain text like  User  inside stored procedure  More info

User · Answer

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

User · Answer

SELECT ROUTINE TYPE  ROUTINE NAME  ROUTINE DEFINITION FROM INFORMATION SCHEMA ROUTINES  WHERE ROUTINE DEFINITION LIKE   Your Text

User · Answer

Just wrote this for generic full outer cross ref  create table  XRefDBs xtype varchar 2  SourceDB varchar 100   Object varchar 100   RefDB varchar 100    declare  sourcedbname varchar 100            searchfordbname varchar 100            sql nvarchar 4000  declare curs cursor for     select name      from sysdatabases     where dbid gt 4 open curs fetch next from curs into  sourcedbname while   fetch status 0     begin     print  sourcedbname     declare curs2 cursor for          select name          from sysdatabases         where dbid gt 4         and name  lt  gt   sourcedbname     open curs2     fetch next from curs2 into  searchfordbname     while   fetch status 0         begin         print  searchfordbname         set  sql             INSERT INTO  XRefDBs  xtype SourceDB Object  RefDB          select DISTINCT o xtype      sourcedbname      o name      searchfordbname             from    sourcedbname   dbo syscomments c         join    sourcedbname   dbo sysobjects o on c id o id         where o xtype in    V     P     FN     TR            and  text like       searchfordbname                 or text like       searchfordbname                  print  sql         exec sp executesql  sql         fetch next from curs2 into  searchfordbname         end     close curs2     deallocate curs2     fetch next from curs into  sourcedbname     end close curs deallocate curs  select   from  XRefDBs

User · Answer

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

User · Answer

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

User · Answer

select text from syscomments where text like   your text here

[sql-server] How to find a text inside SQL Server procedures / triggers?

Examples related to sql-server

Examples related to stored-procedures

Examples related to triggers

Examples related to linked-server