Comment rechercher rapidement des objets et données dans une base de données SQL Server

Fréquemment, les développeurs et les administrateurs ont besoin de rechercher dans les bases de données, des objets ou des données. Si vous avait déjà cherché une fonction de base de données qui contient une colonne de table spécifique ou un nom de variable, ou une table qui contient des données spécifiques, vous avait constaté qu’il n’y a aucune solution accessible simplement, tel que Ctrl + F

Comme il n’y a aucune solution native dans SQL Server Management Studio, ou dans Visual Studio, voici quelques options que vous pouvez utiliser :

La recherche de données dans les tables et les vues

L‘utilisation de SQL pour rechercher des données spécifiques dans toutes les tables et toutes les colonnes d’une base de données est loin d’être une solution optimale. Il existe divers scripts SQL avec des approches différentes qui peuvent être utilisés pour obtenir cette information, ce qu’ils ont en commun est qu’ils utilisent tous des curseurs et des objets système.

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;

Les inconvénients de cette solution est d’utiliser des curseurs, qui sont généralement inefficaces, d’une grande complexité, qui nécessite beaucoup de temps à l’exécution, même sur les petites bases de données. Un autre inconvénient est qu’elle ne peut être utilisée que pour rechercher des données de texte. Pour chercher d’autres types de données, telles que time et datetime, vous devez écrire un nouveau code

La recherche d’objets

La recherche d’un nom d’objet de base de données ou la définition d’un objet est un peu plus facile que la recherche de textes particuliers. Il existe plusieurs méthodes que vous pouvez utiliser. Cependant, toutes ces méthodes impliquent l’interrogation des objets système.

Les exemples SQL suivants recherchent le texte spécifié – la variable @StartProductID – dans les procédures stockées. Lorsque vous recherchez des objets dans d’autres types d’objet de base de données – fonctions, déclencheurs, colonnes, etc., ou dans plusieurs types d’objets de base de données en même temps, le SQL ci-dessous devra être modifié en conséquence

INFORMATION_SCHEMA.ROUTINES

Utilisez des instructions SQL qui interroge la vue INFORMATION_SCHEMA.ROUTINES pour rechercher un paramètre spécifique dans toutes les procédures. La vue INFORMATION_SCHEMA.ROUTINES contient des informations sur toutes les procédures stockées et des fonctions dans une base de données. La colonne ROUTINE_DEFINITION contient les instructions source lorsque la fonction ou la procédure stockée a été créée.

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

Et le résultat est le suivant :

Il n’est pas recommandé d’utiliser les vues INFORMATION_SCHEMA pour rechercher des schémas d’objet stockés dans la colonne ROUTINE_SCHEMA. Utilisez la vue du catalogue sys.objects à la place

Vue sys.syscomments

Interrogez la vue sys.syscomments, qui contient des informations sur chaque procédure stockée, vue, contrainte CHECK et DEFAULT contenue dans une base de données. La requête recherche un texte spécifique dans la colonne de type text, qui contient l’objet DDL

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

Et le résultat est le suivant :

Cette méthode n’est pas recommandée car la table sys.comments sera supprimée dans les futures versions de SQL Server.

Vue sys.sql_modules

Interrogez la vue de sys.sql_modules qui contient le nom, le type et la définition de chaque module dans une base de données.

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

Les résultats sont les mêmes que pour la méthode précédente.

Autres vues système relatives au schéma

Interrogez les vues sys.comments, sys.schemas et sys.objects. La vue sys.schemas contient une ligne pour chaque schéma de base de données. La vue sys.objects contient une ligne pour chaque objet défini par l’utilisateur, figurant dans les schémas d’une base de données. Notez qu’elle ne contient pas les informations sur les déclencheurs, donc vous devez utiliser sys.triggers pour rechercher les noms ou les définitions des déclencheurs.

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;

Les résultats retournés sont les suivants :

Le principal inconvénient de ces méthodes est que pour chaque changement dans les types d’objet recherchés, vous devez modifier le SQL. Pour être en mesure de le faire, vous devez être familier avec la structure d’objets système, afin que vous puissiez les modifier. La recherche pour plusieurs types d’objets et l’ajout de critères de recherche supplémentaires, telle que les inclusions/exclusions des noms d’objets et de leurs contenues, ou l’utilisation des caractères d’échappement, apporte encore plus de complexité au SQL, qui est sujet à erreurs si non testé et qui est chronophage.

Si vous n’êtes pas un développeur expérimenté et si vous n’êtes pas familier avec les objets système qui contiennent des informations DDL, vous préférerez une solution testée et sans erreurs pour rechercher des données et des objets SQL manuellement. Pour cela, utilisez ApexSQL Search.

ApexSQL Search est un complément de recherche SQL pour SSMS et pour Visual Studio. Il peut rechercher du texte dans les objets de base de données (y compris les noms d’objets), les données stockées dans les tables et les vues (même cryptées) et peut répéter les recherches précédentes en un seul clic.

Pour rechercher des données dans les tables et les vues :

Dans le menu principal de SQL Server Management Studio ou de Visual Studio, cliquez sur ApexSQL Search
Sélectionner l’option Database text search… :
Dans le champ de texte à rechercher, entrez la valeur que vous souhaitez rechercher
Dans la liste déroulante Database, sélectionnez la base de données
Dans l’arborescence Select objects to search, sélectionnez les tables et les vues à parcourir ou les laisser tous activés
Sélectionnez s’il faut rechercher dans les vues, les colonnes numérique, type texte, uniqueidentifier et date, en sélectionnant les cases à cocher correspondantes et s’il faut rechercher une correspondance exacte. Si la recherche concerne des colonnes de type date, spécifiez le format de date:
Cliquez sur l’option Find now. La grille est remplie avec les tables de la base de données et les vues qui contiennent la valeur entrée :
Cliquez sur le bouton de sélection Column value pour afficher les détails de l’objet trouvé :

Pour rechercher des objets :

Dans le menu principal de SQL Server Management Studio ou de Visual Studio, dans le menu ApexSQL, cliquez sur ApexSQL Search.
Sélectionnez l’option Database object search…:
Dans le champ Search text, saisissez le texte que vous souhaitez rechercher (par exemple un nom de variable)
Dans la liste déroulante Database, sélectionnez la base de données à rechercher
Dans la liste déroulante Objects, sélectionnez les types d’objet à parcourir, ou les laisser tous activés
Indiquez si vous souhaitez rechercher dans les noms d’objet, colonne, index, corps de l’objet, objets système, en sélectionnant les cases à cocher correspondantes, s’il faut rechercher une correspondance exacte et quel caractère d’échappement à utiliser
Cliquez sur l’option Find now

La grille est remplie avec les objets de base de données qui contient l’objet spécifié.
Double-cliquez sur l’objet dans la grille Database object search, et celui-ci va être mis en évidence dans l’Explorateur d’objets

SQL Server Management Studio et Visual Studio ne fournissent pas d’options de recherche pour un nom d’objet de base de données, une définition d’objet et des données. Les requêtes SQL qui recherchent cela sont complexes, lentes et nécessitent la connaissance des objets système SQL Server. Utilisez ApexSQL Search pour fouiller dans vos bases de données et trouver des données et les objets dont vous avez besoin.

Article traduit par Philippe Geiger
Consultant certifié MCSE Data Platform et Business Intelligence et formateur certifié MCT.
Blog : http://blog.pgeiger.net
Twitter : @pgeiger

October 20, 2014

Database administration
Automate critical tasks for database administration

Database development
Integrate database changes and automate deployments

Comment rechercher rapidement des objets et données dans une base de données SQL Server

La recherche de données dans les tables et les vues

La recherche d’objets

INFORMATION_SCHEMA.ROUTINES

Vue sys.syscomments

Vue sys.sql_modules

Autres vues système relatives au schéma

Database administrationAutomate critical tasks for database administration

Database developmentIntegrate database changes and automate deployments

La recherche de données dans les tables et les vues

La recherche d’objets

INFORMATION_SCHEMA.ROUTINES

Vue sys.syscomments

Vue sys.sql_modules

Autres vues système relatives au schéma

Related posts:

Database administration
Automate critical tasks for database administration

Database development
Integrate database changes and automate deployments