Jak szybko odszukać obiekty i dane w bazie danych SQL Server’a

Programiści i administratorzy baz danych często szukają w bazie konkretnych obiektów lub danych. Jeżeli zdarzyło ci się kiedykolwiek szukać funkcji, która zawiera określoną kolumnę tabeli lub nazwę zmiennej, lub przeszukiwać tabelę, w celu znalezienia określonych danych, to zdajesz sobie sprawę, że nie istnieje prosty sposób typu skrót klawiaturowy Ctrl+F.

Ponieważ SQL Server Management Studio albo Visual Studio nie dają takich możliwości, postanowiliśmy zebrać kilka wskazówek, które opisują jak ułatwić sobie to zadanie.

Odszukiwanie danych w tabelach i widokach

Jedną z możliwości odszukiwania danych we wszystkich tabelach i wszystkich kolumnach bazy danych jest wykorzystanie języka SQL, co jest jednak metodą daleką od doskonałości. Mamy do dyspozycji wiele skryptów SQL, które można w takiej sytuacji wykorzystać. Ich wspólną cechą jest korzystanie z kursorów i obiektów systemowych.

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int,
   @ColumnName varchar(100),
   @String varchar(1000);
--modify the variable, specify the text to search for SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
    FOR SELECT name, object_id
        FROM sys.objects
      WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
WHILE
       @@FETCH_STATUS
       =
       0
    BEGIN
        DECLARE CursorColumns CURSOR
            FOR SELECT name
                  FROM sys.columns
                WHERE
                       object_id
                       =
                       @TableID AND system_type_id IN(167, 175, 231, 239);
        -- the columns that can contain textual data        
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar        
OPEN CursorColumns;
        FETCH NEXT FROM CursorColumns INTO @ColumnName;
        WHILE
               @@FETCH_STATUS
               =
               0
            BEGIN
                SET @String = 'IF EXISTS (SELECT * FROM '
                            + @Table
                            + ' WHERE '
                            + @ColumnName
                            + ' LIKE ''%'
                            + @SearchText
                            + '%'') PRINT '''
                            + @Table
                            + ', '
                            + @ColumnName
                            + '''';
                EXECUTE (@String);
                FETCH NEXT FROM CursorColumns INTO @ColumnName;
            END;
        CLOSE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
    END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

Wadą tej metody jest używanie kursorów, które co do zasady nie są zazwyczaj wystarczająco wydajne i nawet na małych bazach danych wykonywane są dość długo. Inną niedogodnością jest możliwość wykorzystania metody tylko do szukania danych tekstowych. Szukanie innych typów danych takich jak dane typu czas czy data wymaga napisania nowego kodu SQL.

Szukanie obiektów

Szukanie nazwy obiektu bazodanowego lub definicji obiektu jest trochę łatwiejsze niż szukanie określonego tekstu. Wykorzystać można kilka metod, jedną z nich jest wykonanie zapytań na obiektach systemowych.

Poniższy przykład poszukuje określonego tekstu – zmiennej @StartProductID w procedurach przechowywanych. Polecenie SQL widoczny poniżej, po zmodyfikowaniu pozwala odszukiwać obiekty typu funkcje, wyzwalacze, kolumny itp.

Widok INFORMATION_SCHEMA.ROUTINES

Użyj SQL, który wykonuje zapytanie na widoku INFORMATION_SCHEMA.ROUTINES aby odnaleźć określony parametr w procedurze. Widok INFORMATION_SCHEMA.ROUTINES zawiera informacje o wszystkich procedurach i funkcjach w bazie danych. Kolumna ROUTINE_DEFINITION zawiera polecenia źródłowe, które utworzyły funkcję lub procedurę przechowywaną.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' 
    AND ROUTINE_TYPE='PROCEDURE'

Wynik zapytania widoczny jest poniżej:

Nie zaleca się jednak wykorzystywania widoku INFORMATION_SCHEMA do odszukiwania obiektów. Lepszym wyborem jest wykorzystanie widoku katalogu sys.objects.

Widok sys.syscomments

Zapytanie wykonane na widoku sys.syscomments pozwala otrzymać informacje o każdej przechowywanej procedurze, widoku, regule, wartości domyślnej, wyzwalaczu, a także ograniczeniach typu CHECK i DEFAULT. Zapytanie wykonywane jest na kolumnie text, która zawiera definicję obiektu (DDL).

SELECT OBJECT_NAME( id )
  FROM SYSCOMMENTS
  WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1
  GROUP BY OBJECT_NAME( id );

Wynik zapytania widoczny jest poniżej:

Metoda ta także nie jest polecana, ponieważ tablica sys.syscomments będzie usunięta w kolejnych wersjach SQL Server’a.

Widok sys.sql_modules

Zapytanie wykonane na widoku sys.sql_modules daje w wyniku nazwę, typ i definicję każdego modułu w bazie danych.

SELECT OBJECT_NAME( object_id )
  FROM sys.sql_modules
WHERE
       OBJECTPROPERTY(object_id , 'IsProcedure')
       =
       1 AND definition LIKE '%@StartProductID%';

Wynik zapytania – taki sam jak w poprzedniej metodzie – widoczny jest poniżej:

Inne widoki systemowe

Zapytanie wykonane na widoku sys.schemas pozwala otrzymać jeden wiersz dla każdego schematu bazy danych. Widok sys.objects zawiera jeden wiersz dla obiektu zdefiniowanego przez użytkownika w ramach schematu. Widok sys.objects nie zawiera jednak informacji o wyzwalaczach. W tym celu, szukając nazw wyzwalaczy oraz ich definicji należy wykonać zapytanie na widoku sys.triggers.

DECLARE
 @searchString nvarchar( 50 );
SET@searchString = '@StartProductID';
SELECT DISTINCT
    s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition
FROM
     syscomments C INNER JOIN sys.objects O
                     ON
     C.id
     =
     O.object_id
                   INNER JOIN sys.schemas S
                   ON
     O.schema_id
     =
     S.schema_id
WHERE
    C.text LIKE
     '%'
   + @searchString
   + '%'
 OR O.name LIKE
     '%'
   + @searchString
   + '%'
ORDER BY
       Schema_name , Object_name;

Wynik zapytania widoczny jest poniżej:

Główną wada metod opisanych powyżej jest konieczność zmiany napisanego zapytania SQL, jeśli zmienia się typ poszukiwanego obiektu, co z kolei wymaga dokładnej wiedzy na temat struktury obiektów systemowych. Wyszukiwanie w wielu obiektów systemowych oraz dodawanie dodatkowych kryteriów wyszukiwania, takich jak włączanie w wynik zapytania pewnych obiektów lub ich wyłączanie wprowadza dodatkowy poziom skomplikowania do napisanego zapytania SQL, co czyni je podatnym na błędy i wymaga zazwyczaj czasochłonnego testowania.

Jeśli nie jesteś doświadczonym deweloperem, nie znasz dokładnie obiektów systemowych, to wskazane byłoby wykorzystanie przetestowanego, bezbłędnego narzędzia do odszukiwania obiektów i danych w bazie. Takim narzędziem jest ApexSQL Search.

ApexSQL Search jest dodatkiem do SSMS oraz Visual Studio. Umożliwia wyszukiwanie w obiektach systemowych bazy konkretnego tekstu (włączając w to nazwy obiektów), konkretnych danych przechowywanych w tablicach i widokach (nawet jeśli są zaszyfrowane) oraz powtórzenie wykonanych wcześniej wyszukiwań.

Aby znaleźć dane w tabeli lub widoku:

1. Kliknij ApexSQL Search w menu głównym SQL Server Management Studio lub Visual Studio.

2. Wybierz opcję Database text search…

   

3. W polu tekstowym Search, wpisz wartość, której szukasz.
4. Z listy rozwijalnej Database wybierz bazę danych, która ma zostać przeszukiwana.
5. W drzewie Select objects to search, wybierz tabelę i widoki, które należy przeszukać, ew. pozostaw zaznaczone wszystkie obiekty.
6. Wybierz typ przeszukiwanych obiektów, takich jak widoki, wartości numeryczne, wartości tekstowe, wartości typu uniqueidentifier czy typu data, przez zaznaczenie właściwych opcji. Dodatkowo, ustal czy wyszukujesz dokładnego wystąpienia tekstu. W przypadku przeszukiwania pól typu data, określ format daty.
   

   

7. Kliknij przycisk Find now. Okno zostanie wypełnione tabelami i widokami, które zawierają wpisaną wartość.

   

8. Kliknij przycisk elipsy kolumnie Column value aby obejrzeć szczegóły znalezionego obiektu.
   

   

Aby odnaleźć obiekty:

1. Kliknij ApexSQL Search w menu głównym SQL Server Management Studio lub Visual Studio.

2. Wybierz opcję Database text search…

   

3. W polu tekstowym Search text, wpisz tekst, który ma być odszukany (np. nazwę zmiennej).
4. Z listy rozwijalnej Database wybierz bazę danych, która ma zostać przeszukiwana.
5. Z listy rozwijalne Objects wybierz typ obiektu, który jest poszukiwany lub pozostaw zaznaczone wszystkie obiekty.
6. Wybierz, zaznaczając właściwe opcje czy żądany tekst ma zostać wyszukiwany w obiektach, w kolumnach, nazwach indeksów, obiektach systemowych oraz czy chcesz wykonać wyszukiwanie dokładne według wpisanego wzorca wyszukiwania. Dodatkowo zdefiniuj znak ucieczki.
7. Klnij przycisk Find now.
   

   

   Okno zostanie wypełnione obiektami bazodanowymi, które zawierają szukany obiekt.

8. Kliknij dwa razy obiekt w sekcji wyników (Database object search). Spowoduje to podświetlenie obiektu w Eksploratorze Obiektów.
   

   

SQL Server Management Studio i Visual Studio nie dostarczają opcji wyszukiwania w bazie danych nazw obiektów, definicji obiektów oraz danych. Zapytania SQL, które pozwalają je odszukać są skomplikowane, wolne w działaniu i wymagają znajomości obiektów systemowych SQL Server’a. Użyj ApexSQL Search do odszukiwania obiektów w bazie danych.

Tłumacz: Anna Lesniak