Wie sucht man schnell nach SQL Server Datenbanken und Datenbankobjekten?

Es kommt ziemlich oft vor, dass Entwickler oder Datenbankadministratoren nach Datenbankobjekten oder Daten suchen müssen. Falls Sie das schon mal versucht haben, haben Sie sicherlich festgestellt, dass es nicht ganz so einfach ist.

Es gibt weder in SQL Server Management Studio noch in Visual Studio die Möglichkeit, eine einfache Suche zu starten.

Suchen nach Daten in Tabellen und Views

Die Suche in Tabellen und Views ist mit T-SQL nicht gerade einfach oder bequem. Es gibt eine Reihe an Skriptmöglichkeiten, um eine Suche zu gestalten, die fast alle cursor-basiert sind.

Beispiel:

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;

Ein Nachteil von solchen Suchtypen ist die Nutzung von Cursorn, die generell ineffizient und komplex sind und ziemlich langsam ablaufen (selbst bei kleineren Datenbanken).
Ein weiterer Nachteil ist, dass nur Text gesucht werden kann. Falls Sie andere Datentypen durchsuchen wollen, müssen Sie die Suche umbauen.

Eine Objektsuche

Die Suche nach Objekten (über Objektname oder Objektdefinition) ist etwas einfacher. Diese Suche wird meistens über Systemobjekte erledigt.

Die folgenden Beispiele suchen durch alle Prozeduren nach dem Variabel “@StartProductId”. Falls Sie durch andere Objekttypen suchen wollen, müssten Sie die Abfrage ebenfalls anpassen.

INFORMATION_SCHEMA.ROUTINES

Dieses Systemview ermöglicht eine Suche durch alle Prozeduren und Funktionen in einer Datenbank. Die Spalte “ROUTINE_DEFINITION” enthält den Quellcode der einzelne Prozeduren und Funktionen.

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

Und hier die Ergebnisse:

sys.syscomments

Dieses Systemview enthält alle Prozeduren, Views, Regeln, Defaults, Trigger sowie CHECK und DEFAULT constraints. Die Abfrage sucht in der “text” Spalte:

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

Die Ergebnisse:

Diese Methode wird nicht empfohlen, da sys.syscomments bereits von Microsoft für zukünftige Versionen von SQL Server abgekündigt wurde.

sys.sql_modules

Dieses Systemview enthält den Namen, Typen und Inhalt von allen Modulen in einer Datenbank.

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

Die Ergebnisse sind identisch mit denen von sys.syscomments

Andere sys schemaviews

Sie können sys.syscomments, sys.schemas und sys.objects zusammen abfragen. Sys.schemas enthält eine Zeile pro Schema und sys.objects enthält eine Zeile pro Datenbankobjekt. Falls Sie Trigger durchsuchen wollen, müssen Sie zusätzlich in sys.triggers suchen, denn diese Information wird getrennt gespeichert.

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;

Die Ergebnisse:

Der Hauptnachteil dieser Methoden ist die Notwendigkeit, die Abfrage jedes Mal anpassen zu müssen, falls der gesuchte Objekttyp sich ändert. Dazu müssen Sie die Systemtabellen und -views sehr gut kennen. Wenn Sie die Suche auch noch flexibler gestalten wollen, müssen Sie mit sehr komplexen Programmierstrukturen arbeiten, was die Fehleranfälligkeit erhöht.

Falls Sie die Zeit dafür nicht haben oder Ihnen die nötigen Programmierkenntnisse fehlen, können Sie ApexSQL Search anwenden.

ApexSQL Search ist eine Erweiterung für SQL Server Management Studio. ApexSQL Search kann sowohl Quellcode als auch Datensätze durchsuchen. Dabei ist es möglich, verschlüsselte Daten zu durchsuchen und eine frühere Suche sehr einfach erneut auszuführen.

Um nach Datensätzen zu suchen, brauchen Sie nur:

1. ApexSQL Search innerhalb von SQL Server Management Studio oder Visual Studio aufzurufen

2. Wählen Sie Database text search aus:

   

3. Geben Sie die Suchkritieren ein
4. Wählen Sie bei Database die gewünschte Datenbank aus
5. Wählen Sie unter Select objects to search die Tabellen/Views aus, die Sie durchsuchen wollen
6. Wählen Sie die weiteren Suchoptionen zu Datentypen und Spalten aus
   

   

7. Klicken Sie nun Find now. Die Ergebnisse werden dann im Gridviewer dargestellt:

   

8. Klicken Sie auf die Punkte in der Column Value Spalte, um detaillierte Informationen zu den Einzelergebnissen abzufragen.
   

   

Die Objektsuche:

1. Rufen Sie ApexSQL Search innerhalb von SQL Server Management Studio oder Visual Studio auf

2. Wählen Sie Database object search aus:

   

3. Geben Sie die Suchkriterien ein
4. Wählen Sie bei Database die gewünschte Datenbank aus
5. Wählen Sie unter Objects die Datenbankobjekttypen aus, die Sie durchsuchen wollen
6. Wählen Sie aus, ob Sie Spalten, Indizes, Quellcodes oder Systemobjekte durchsuchen wollen
7. Klicken Sie Find now. Die Ergebnisse werden dann im Gridviewer dargestellt:
   

   

8. Mit einem Doppel-Klick auf eine Zeile wird das gewählte Objekt im Objekt-Explorer ausgewählt:
   

   

Weder SQL Server Management Studio noch Visual Studio haben solche Suchfunktionalitäten. Handgeschriebene Suchmethoden sind oft langsam und fehleranfällig. Mit ApexSQL Search können Sie schnell und sicher Ihre Datenbanken durchsuchen.

Übersetzer: William Durkin