Jak znaleźć tekst w procedurach / wyzwalaczach SQL Server?


173

Mam połączony serwer, który się zmieni. Niektóre procedury wywołać serwer połączony takiego: [10.10.100.50].dbo.SPROCEDURE_EXAMPLE. Mamy również wyzwalacze wykonujące tego rodzaju pracę. Musimy znaleźć wszystkie miejsca, w których [10.10.100.50]można to zmienić.

W SQL Server Management Studio Express nie znalazłem funkcji typu „znajdź w całej bazie danych” w programie Visual Studio. Czy specjalny sys-select może pomóc mi znaleźć to, czego potrzebuję?

Odpowiedzi:


310

oto część procedury, której używam w moim systemie, aby znaleźć tekst ...

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

1
Dodatkowo możesz dodać to do swojego zestawu wyników, aby szybko zobaczyć tekst zawierający wartość, której szukasz. , podciąg (m.definition, charindex (@Search, m.definition), 100)
Chris Rodriguez,

2
@ChrisRodriguez, dobry pomysł, ale pamiętaj, że będzie to tylko pierwsze dopasowanie z możliwie wielu w ramach każdej procedury / wyzwalacza / funkcji
KM.

Nie dotyczy ograniczeń ( type = 'C')?
Kiquenet,

18

Możesz to znaleźć jak

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

Wyświetli listę różnych nazw procedur składowanych, które zawierają tekst, taki jak „Użytkownik” wewnątrz procedury składowanej. Więcej informacji


8
Pamiętaj tylko, że syscommentstabela przechowuje wartości w fragmentach po 8000 znaków, więc jeśli masz pecha, aby tekst, którego szukasz, był podzielony na jedną z tych granic, nie znajdziesz go za pomocą tej metody.
ErikE

17

[Spóźniona odpowiedź, ale miejmy nadzieję, użyteczna]

Używanie tabel systemowych nie zawsze daje w 100% poprawne wyniki, ponieważ może istnieć możliwość, że niektóre procedury składowane i / lub widoki są zaszyfrowane, w takim przypadku będziesz musiał użyć połączenia DAC, aby uzyskać potrzebne dane.

Polecam skorzystanie z narzędzia innej firmy, takiego jak ApexSQL Search, które z łatwością radzi sobie z zaszyfrowanymi obiektami.

Tabela systemowa Syscomments poda wartość zerową dla kolumny tekstowej w przypadku zaszyfrowania obiektu.


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

1
Nie obejmuje to wyzwalaczy, takich jak zadane pytanie
Enkode

Czy dotyczy widoków, procedur składowanych, tabel zdefiniowanych przez użytkownika ? a dla wyzwalaczy, funkcji, ograniczeń, reguł, wartości domyślnych ?
Kiquenet,

5

To zadziała dla Ciebie:

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

Nie dotyczy ograniczeń ( type = 'C')?
Kiquenet,

4

Istnieją znacznie lepsze rozwiązania niż modyfikowanie tekstu procedur składowanych, funkcji i widoków za każdym razem, gdy zmienia się połączony serwer. Oto kilka opcji:

  1. Zaktualizuj połączony serwer. Zamiast korzystać z połączonego serwera o nazwie z jego adresem IP, należy utworzyć nowy serwer połączony z nazwą zasobu takiego jak Financelub DataLinkProdczy coś takiego. Następnie, gdy musisz zmienić osiągnięty serwer, zaktualizuj połączony serwer, aby wskazywał nowy serwer (lub upuść go i utwórz ponownie).

  2. Chociaż niestety nie możesz tworzyć synonimów dla połączonych serwerów lub schematów, MOŻESZ tworzyć synonimy dla obiektów, które znajdują się na połączonych serwerach. Na przykład Twoja procedura [10.10.100.50].dbo.SPROCEDURE_EXAMPLEmoże zostać utworzona przez alias. Może utworzyć schemat datalinkprod, a następnie CREATE SYNONYM datalinkprod.dbo_SPROCEDURE_EXAMPLE FOR [10.10.100.50].dbo.SPROCEDURE_EXAMPLE;. Następnie napisz procedurę składowaną, która akceptuje nazwę serwera połączonego, który wysyła zapytania do wszystkich potencjalnych obiektów ze zdalnej bazy danych i (ponownie) tworzy dla nich synonimy. Wszystkie Twoje SP i funkcje są przepisywane tylko raz, aby używać nazw synonimów zaczynających się od datalinkprodi zawsze później, aby zmienić jeden serwer połączony na inny, który po prostu robisz, EXEC dbo.SwitchLinkedServer '[10.10.100.51]';aw ułamku sekundy używasz innego serwera połączonego.

Może być jeszcze więcej opcji. Zdecydowanie zalecam używanie lepszych technik przetwarzania wstępnego, konfiguracji lub pośredniego zamiast zmiany skryptów napisanych przez ludzi. Automatyczne aktualizowanie skryptów utworzonych maszynowo jest w porządku, to jest przetwarzanie wstępne. Robienie rzeczy ręcznie jest okropne.


Zgadzam się z twoją sugestią. Ale w sytuacji takiej jak opisana w OP nadal musisz znaleźć wszystkie procedury składowane zawierające adres IP serwera. A nawet jeśli musisz to zrobić tylko raz, zrobienie tego ręcznie może wymagać dużo pracy.
Paul Groke,

@PaulGroke Tak, to „dużo” pracy to dług techniczny spowodowany złymi wyborami technicznymi w zakorzenionym systemie. Potrzeba czasu, aby odzyskać od tego - spłacenie narosłego zadłużenia. Ale moja sugestia jest taka, jak budować bogactwo techniczne - spędzać więcej czasu teraz, aby być szybszym, bardziej zwinnym i bardziej niezawodnym później. Przeczytaj artykuł Big Ball of Mud, aby uzyskać kilka pomysłów na ten temat.
ErikE

Chodziło mi o to: Co jest złego w ograniczaniu pracy „spłacania długów” za pomocą jednego z oświadczeń SELECT, które inni tutaj zamieścili?
Paul Groke,

@PaulGroke Nie ma nic złego w szybkim sposobie znajdowania obiektów, które mogą odnosić się do połączonego serwera. Ale znasz to stare powiedzenie o „nauczyć człowieka łowić ryby”, a nie „dać człowiekowi rybę”? Tak. Ta rzecz.
ErikE

@ErikE Rzecz w tym, że nie uczysz go łowić, mówisz tylko, że jeśli łowi, może zdobyć jedzenie. Twoja odpowiedź jest świetną radą, ale nie pomaga OP w jej wdrożeniu. Dodanie sposobu wyszukiwania tych odniesień, aby można było je zastąpić czymś lepiej zaprojektowanym, znacznie poprawiłoby tę odpowiedź.
T. Sar,


2

Ten, który wypróbowałem w SQL2008, który może przeszukiwać wszystkie bazy danych za jednym razem.

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

0

Używam tego do pracy. zostaw jednak [] w polu @TEXT, wydaje się, że chce zwrócić wszystko ...

WŁĄCZ NOCOUNT

ZADEKLARUJ @ TEXT VARCHAR (250)
ZADEKLAROWAĆ @SQL VARCHAR (250)

SELECT @ TEXT = „10 .10.100.50”

CREATE TABLE #results (db VARCHAR (64), nazwa obiektu VARCHAR (100), xtype VARCHAR (10), definicja TEXT)

WYBIERZ @TEXT jako „Ciąg wyszukiwania”
ZADEKLAROWAĆ # bazy danych KURSOR WYBIERANIA NAZWY Z wzorca..sys bazy danych, gdzie dbid> 4
    ZADEKLAROWAĆ @c_dbname varchar (64)   
    OTWÓRZ # bazy danych
    FETCH #databases INTO @c_dbname   
    WHILE @@ FETCH_STATUS -1
    ZACZYNAĆ
        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 [definicja] LIKE' '%' + @ TEXT + '%' ''   
        EXEC (@SQL)
        FETCH #databases INTO @c_dbname
    KONIEC
    ZAMKNIJ # bazy danych
DEALLOCATE # bazy danych

SELECT * FROM #results uporządkuj według db, xtype, objectname
DROP TABLE #wyniki

0

Używałem ich w przeszłości:

W tym konkretnym przypadku, gdy trzeba zamienić określony ciąg w procedurach składowanych, prawdopodobnie bardziej odpowiednie jest pierwsze łącze.

Nieco niezwiązany z tematem dodatek Szybkie wyszukiwanie jest również przydatny do wyszukiwania nazw obiektów za pomocą programu SQL Server Management Studio. Dostępna jest zmodyfikowana wersja z pewnymi ulepszeniami, a kolejna nowsza wersja jest również dostępna w Codeplex z kilkoma innymi przydatnymi dodatkami.


0

Każde wyszukiwanie za pomocą instrukcji select daje tylko nazwę obiektu, w której zawiera słowo kluczowe wyszukiwania. Najłatwiejszym i najskuteczniejszym sposobem jest pobranie skryptu procedury / funkcji, a następnie wyszukanie w wygenerowanym pliku tekstowym, ja też stosuję tę technikę :) Więc jesteś dokładny.


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

0

Właśnie napisałem to dla ogólnego, pełnego zewnętrznego krzyża 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>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,'''+@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

-1

Możesz przeszukiwać definicje wszystkich obiektów bazy danych za pomocą następującego 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%'
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.