Come trovare rapidamente oggetti e dati su un database SQL Server

Una delle funzionalità più usate dagli sviluppatori e dai DBA è quella di ricercare dati ed oggetti all’interno di database. Non esiste nativamente una combinazione di tasti che ci consenta una rapida ricerca, come potrebbe essere il Ctrl+F, né su SQL Server Management Studio, né su Visual Studio. Tuttavia esistono varie opzioni:

Cercare dati su tabelle e viste

Utilizzare T-SQL per ricercare un dato specifico in tutte le tabelle ed in tutte le colonne di un database non è di certo una soluzione ottimale. Ci sono tanti script con approcci differenti che cercano di assolvere questo compito, ad esempio, il seguente:

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;

Gli svantaggi di tale soluzione sono tanti. L’utilizzo dei cursori, generalmente non efficienti, la difficoltà dello script, il tempo per l’esecuzione, anche su database di piccole dimensioni. In aggiunta, funziona solamente per i dati di tipo testuale. Per ricercare altri tipi di dato come TIME e DATETIME è necessario scrivere nuove righe di codice.

Ricercare oggetti

Ricercare per nome oggetti all’interno di un database è leggermente più semplice rispetto all’esempio precedente. Ci sono vari metodi disponibili, anche se tutti coinvolgono l’utilizzo di oggetti di sistema. L’esempio seguente cerca in base ad un testo specifico (@StartProdictID) dall’elenco delle stored procedure di un database. Per ricercare altri tipi di oggetti come tipi utente, funzioni, trigger, colonne, ecc. è necessario cambiare il codice in base al tipo di ricerca.

INFORMATION_SCHEMA.ROUTINES

La vista INFORMATION_SCHEMA.ROUTINES contiene le informazioni su tutte le stored procedure e funzioni del nostro database. La colonna ROUTINE_DEFINITION contiene:

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

Il risultato della query è il seguente:

Non è tuttavia raccomandato ricercare nelle viste INFORMATION_SCHEMA l’informazione ROUTINE_SCHEMA. Potrebbe essere errato.

Vista sys.syscomment

La vista sys.syscomments contiene le informazioni relative ad ogni stored procedure, vista, regola, default, trigger, constraint di CHECK e DEFAULT di un database. La seguente query ricerca un testo specifico nella colonna text, la quale contiene la DDL dell’oggetto

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

Il risultato è il seguente:

Anche questo metodo non è consigliato, poiché la sys.comments verrà rimossa a partire dalle future versioni di SQL Server.

Vista sys.sql_modules

La vista sys.sql_modules contiene il nome, il tipo e la definizione di ogni modulo in un database.

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

Anche qui, stesso risultato:

Altre viste dello schema sys

Parliamo di sys.schemas e sys.objects. La prima contiene una riga per ogni schema creato a database. La seconda contiene una riga per ogni oggetto creato dall’utente. Non contiene informazioni sui trigger, per i quali risulta necessario usare la 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;

Il risultato è il seguente:

Il principale svantaggio di tutti questi metodi è che per ogni cambiamento nei parametri di ricerca è necessario cambiare il T-SQL. Questo significa che è altresì necessario conoscere il linguaggio e padroneggiare l’utilizzo degli oggetti di sistema. Inoltre, cercare per tipi di oggetto differenti aggiunge complessità agli script e anche logiche tramite le quali includere o escludere alcuni tipi di informazione. E questo espone chi scrive ad errori che riducono la produttività.

Soprattutto se non si ha esperienza, è meglio affidarsi a strumenti semplici e che ci allontanano dai suddetti errori. Per operazioni di ricerca di questo tipo utilizzare ApexSQL Search.

ApexSQL Search è un SQL search add-in for SSMS and Visual Studio e per Visual Studio. Ricerca per testo all’interno dei nostri database (nomi oggetto compresi), dati in tabelle e viste (anche quelli crittografati) e ripete le ricerche effettuate precedentemente con un singolo click.

Ecco come procedere per cercare tabelle e viste:

1. Nel menu di SQL Server Management Studio o di Visual Studio, selezionare ApexSQL Search

2. Selezionare Database text search…:

   

3. Nel campo di testo Search, immettere il valore da ricercare
4. Dal menu a tendina Database, selezionare il database di destinazione
5. Nell’albero Select objects to search, selezionare le tabelle e le viste in cui cercare, oppure lasciare tutto selezionato
6. Indicare se si vuole ricercare nelle viste, i tipi numerici, testuali, gli uniqueidentifier e le date, utilizzando le checkbox relative. È possibile anche forzare una ricerca esatta. Nel caso in cui si scelga la ricerca delle date, indicarne anche il formato
   

   

7. Selezionare l’opzione Find now. La griglia dei risultati verrà popolata con le tabelle e le viste del database:

   

8. Aprire i dettagli da Column value con il bottone relative per aprire la seguente form:
   

   

Ecco come muoversi per ricercare oggetti:

1. Nel menu principale di SQL Server Management Studio o di Visual Studi selezionare ApexSQL, e quindi ApexSQL Search.

2. Selezionare Database object search…:

   

3. Nel campo di testo Search, immettere il valore da ricercare (ad esempio un nome di variabile)
4. Dal menu a tendina Database, selezionare il database di destinazione
5. Nell’albero Select objects to search, selezionare gli oggetti in cui cercare, oppure lasciare tutto selezionato
6. Indicare se si vuole ricercare negli oggetti, nei nomi di indici, nel corpo di definizione, negli oggetti di sistema, utilizzando le checkbox relative. È possibile anche forzare una ricerca esatta o specificare i caratteri di escape
7. Premere Find now:
   

   

   La griglia verrà popolata con i risultati di ricerca.

8. Fare doppio click sulla griglia Database object search e l’oggetto corrispondente verrà selezionato sull’Esplora oggetti
   

   

SQL Server Management Studio e Visual Studio non forniscono tool nativi di ricerca in grado di navigare gli oggetti e le loro definizioni su SQL Server. Tantomeno è possibile ricercare nei dati in maniera veloce. Le query che servirebbero per ottenere risultati sono complesse e hanno come requisito la conoscenza degli oggetti di sistema. Con ApexSQL Search possiamo scavare a fondo nei nostri database e ricercare gli oggetti di cui abbiamo bisogno con semplicità.

Traduttore Alessandro Alpi