Wie finde ich einen Text in SQL Server-Prozeduren/-Triggern?

[Späte Antwort, aber hoffentlich nützlich]

Die Verwendung von Systemtabellen führt nicht immer zu 100% zu korrekten Ergebnissen, da möglicherweise gespeicherte Prozeduren und/oder Ansichten verschlüsselt werden. In diesem Fall müssen Sie DAC connection verwenden, um die Daten abzurufen brauchen.

Ich würde empfehlen, ein Drittanbieter-Tool wie ApexSQL Search zu verwenden, das mit verschlüsselten Objekten problemlos umgehen kann. 

Die Syscomments-Systemtabelle gibt für die Textspalte einen Nullwert an, falls das Objekt verschlüsselt ist. 

Sie können es gerne finden 

SELECT DISTINCT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%User%'

Es werden verschiedene Namen gespeicherter Prozeduren aufgelistet, die Text wie 'Benutzer' in der gespeicherten Prozedur enthalten. Mehr Info

-- 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');

Das wird für Sie funktionieren:

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

Es gibt viel bessere Lösungen als den Text Ihrer gespeicherten Prozeduren, Funktionen und Ansichten jedes Mal zu ändern, wenn sich der verknüpfte Server ändert. Hier sind einige Optionen:

1. Aktualisieren Sie den Verbindungsserver. Anstelle eines mit seiner IP-Adresse benannten Verbindungsservers erstellen Sie einen neuen Verbindungsserver mit dem Namen der Ressource, z. B. Finance oder DataLinkProd, oder einen solchen. Wenn Sie ändern möchten, welcher Server erreicht wird, aktualisieren Sie den Verbindungsserver so, dass er auf den neuen Server verweist (oder löschen Sie ihn und erstellen Sie ihn neu).

2. Leider können Sie keine Synonyme für verknüpfte Server oder Schemas erstellen, Sie können jedoch Synonyme für Objekte erstellen, die sich auf verknüpften Servern befinden. Zum Beispiel könnte Ihre Prozedur [10.10.100.50].dbo.SPROCEDURE_EXAMPLE von einem Aliasing abweichen. Erstellen Sie vielleicht ein Schema datalinkprod und dann CREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;. Schreiben Sie anschließend eine gespeicherte Prozedur, die einen verknüpften Servernamen akzeptiert, der alle potenziellen Objekte aus der entfernten Datenbank abfragt und (erneut) Synonyme für sie erstellt. Alle Ihre SPs und Funktionen werden nur einmal umgeschrieben, um die Synonymnamen zu verwenden, die mit datalinkprod beginnen, und danach, um von einem Verbindungsserver zu einem anderen zu wechseln, müssen Sie einfach EXEC dbo.SwitchLinkedServer '[10.10.100.51]'; und in einem Sekundenbruchteil einen anderen Verbindungsserver verwenden .

Möglicherweise gibt es noch mehr Optionen. Ich empfehle dringend, die überlegenen Techniken der Vorverarbeitung, Konfiguration oder Umleitung zu verwenden, anstatt vom Menschen geschriebene Skripts zu ändern. Das automatische Aktualisieren maschinell erstellter Skripts ist in Ordnung, dies ist eine Vorverarbeitung. Dinge manuell zu machen, ist schrecklich.

select text
from syscomments
where text like '%your text here%'

Schrieb gerade dieses für generische volle Außenkreuzreferenz

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>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>4
        and name <> @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,'''[email protected]+''', o.name,'''[email protected]+'''
        from '[email protected]+'.dbo.syscomments c
        join '[email protected]+'.dbo.sysobjects o on c.id=o.id
        where o.xtype in (''V'',''P'',''FN'',''TR'')
        and (text like ''%'[email protected]+'.%''
          or text like ''%'[email protected]+'].%'')'
        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

Bei einer Suche mit select-Anweisung erhalten Sie nur den Objektnamen, in dem das Suchwort enthalten ist. Der einfachste und effizienteste Weg ist, Skript der Prozedur/Funktion abzurufen und dann in der generierten Textdatei zu suchen. Ich folge auch dieser Technik :) So sind Sie genau festgelegt.

Ich benutze dieses für die Arbeit. Lassen Sie die [] aber im @TEXT-Feld stehen, scheint alles zurückgeben zu wollen ...

 SET NOCOUNT ON 

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

 SELECT @ TEXT = '10 .10.100.50 '

 CREATE TABLE #results (db VARCHAR (64), Objektname VARCHAR (100), xtype VARCHAR (10), Definition TEXT) 

 SELECT @TEXT als 'Search String' 
 DECLARE #databases CURSOR FOR SELECT NAME FROM master..sysdatabases, wobei dbid> 4 
 DECLARE @c_dbname varchar (64) 
 OPEN #databases 
 FETCH #databases INTO @c_dbname 
 WÄHREND @@ FETCH_STATUS -1 
 START
 SELECT @SQL = 'INSERT INTOresults' 
 SELECT @SQL = @SQL + 'SELECT' '' + @c_dbname + '' 'AS db, o.name, o.xtype, m.definition' 
 SELECT @SQL = @SQL + 'FROM'[email protected]_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 
 ENDE
 CLOSE #Datenbanken 
 DEALLOCATE #Datenbanken 

 SELECT * FROM #Ergebnisse Reihenfolge nach DB, XType, Objektname 
 DROP TABLE #Ergebnisse 

Ich habe diese in der Vergangenheit benutzt:

• Durchsuchen aller gespeicherten Prozeduren nach einem Tabellennamen
• SQL Server-Daten in allen Spalten aller Tabellen suchen und ersetzen

In diesem speziellen Fall, in dem Sie eine bestimmte Zeichenfolge in gespeicherten Prozeduren ersetzen müssen, ist die erste Verknüpfung wahrscheinlich relevanter.

Das Add-In Quick Find ist ein wenig abseits des Themas und auch für die Suche nach Objektnamen mit SQL Server Management Studio nützlich. Es gibt eine modifizierte Version mit einigen Verbesserungen und eine andere neuere Version , die auch auf Codeplex mit einigen anderen nützlichen Add-Ins verfügbar ist.

SELECT ROUTINE_TYPE, ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES 
WHERE ROUTINE_DEFINITION LIKE '%Your Text%' 

Dieses hier habe ich in SQL2008 ausprobiert, das alle Db auf einmal durchsuchen kann.

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='[email protected]
    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