Serveur d'impression

Comment trouver rapidement des objets et des données dans une base de données SQL Server – Bien choisir son serveur d impression

Le 12 octobre 2019 - 9 minutes de lecture

Les développeurs et les administrateurs ont souvent besoin de rechercher des bases de données, des objets ou des données. Si vous aviez déjà recherché une fonction de base de données contenant une colonne de table spécifique ou un nom de variable, ou une table contenant des données spécifiques, vous avez constaté qu'il n'existait aucune solution facilement accessible, telle que Ctrl + F.

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

Recherche de données dans des tables et des vues

L'utilisation de SQL pour rechercher des données spécifiques dans toutes les tables et les colonnes d'une base de données est loin d'être optimale. Divers scripts SQL avec différentes approches peuvent être utilisés pour obtenir ces informations. Ils ont en commun d'utiliser des curseurs et des objets système.

DÉCLARÉ
   @SearchText Varchar(200),
   @Table Varchar(100),
   @TableID int,
   @Nom de colonne Varchar(100),
   @Chaîne Varchar(1000);
ENSEMBLE @SearchText = & # 39; John & # 39;;
DÉCLARÉ CursorSearch LE CURSEUR
        POUR SÉLECTIONNER Nom, object_id
        DE sys.objets
         type = & # 39; U & # 39;;
OUVERT CursorSearch;
FETCH SUIVANT DE CursorSearch DANS @Table, @TableID;
TANDIS QUE
        @@ FETCH_STATUS
        =
       0
        COMMENCER
        DÉCLARÉ CursorColumns LE CURSEUR
        POUR SÉLECTIONNER Nom
        DE sys.Colonnes
        
        object_id
        =
                       @TableID ET system_type_id DANS(167, 175, 231, 239);
               
       
OUVERT CursorColumns;
        FETCH SUIVANT DE CursorColumns DANS @Nom de colonne;
        TANDIS QUE
        @@ FETCH_STATUS
        =
               0
        COMMENCER
        ENSEMBLE @Chaîne = & # 39; SI EXISTE (SELECT * FROM & # 39;
        + @Table
        + & # 39; WHERE & # 39;
        + @Nom de colonne
        + Comme # 39; & # 39;% & # 39;
        + @SearchText
        + & # 39;% & # 39; & # 39;) PRINT & # 39; & # 39; & # 39;
        + @Table
        + & # 39;, & # 39;
        + @Nom de colonne
        + & # 39; & # 39; & # 39; & # 39;;
        RÉALISÉ (@Chaîne);
        FETCH SUIVANT DE CursorColumns DANS @Nom de colonne;
        FIN;
        PROCHE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH SUIVANT DE CursorSearch DANS @Table, @TableID;
        FIN;
PROCHE CursorSearch;
DEALLOCATE CursorSearch;

L'inconvénient de cette solution est d'utiliser des curseurs, généralement inefficaces, très complexes et nécessitant beaucoup de temps, même sur de petites bases de données. Un autre inconvénient est qu'il ne peut être utilisé que pour rechercher des données de texte. Pour rechercher d'autres types de données, telles que l'heure et la date / heure, vous devez écrire un nouveau code.

Rechercher des 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 d'interroger 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'objets de base de données – fonctions, déclencheurs, colonnes, etc., ou dans plusieurs types d'objets de base de données simultanément, le code SQL suivant doit être modifié en conséquence

INFORMATION_SCHEMA.ROUTINES

Utilisez des instructions SQL qui interrogent 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 et fonctions stockées dans une base de données. La colonne ROUTINE_DEFINITION contient les instructions source lors de la création de la fonction ou de la procédure stockée.

SÉLECTIONNER nom_routine, définition_routine
        DE INFORMATION_SCHEMA.ROUTINE 
         définition_routine COMME % @% StartproductID & # 39; 
        ET ROUTINE_TYPE=& # 39; PROCEDURE & # 39;

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 plutôt la vue catalogue sys.objects

Vue Sys.syscomments

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

SÉLECTIONNER OBJECT_NAME(id)
  DE SYSCOMMENTS
   texte COMME & # 39;% @% StartProductID & # 39; ET OBJECTPROPERTY(id , & # 39; IsProcedure & # 39;) = 1
  GROUPE PAR 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 sys.sql_modules contenant le nom, le type et la définition de chaque module d'une base de données.

SÉLECTIONNER OBJECT_NAME( object_id )
  DE sys.sql_modules

       OBJECTPROPERTY(object_id , & # 39; IsProcedure & # 39;)
       =
       1 ET définition COMME & # 39;% @% StartProductID & # 39;;

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

Autres vues du système de 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 dans les schémas d'une base de données. Notez qu'il ne contient pas d'informations de déclencheur. Vous devez donc utiliser sys.triggers pour rechercher des noms ou des définitions de déclencheurs.

DÉCLARÉ
 @searchString Nvarchar(50);
ENSEMBLE@searchString = & # 39; @ StartProductID & # 39;;
SÉLECTIONNER SÉPARÉ
    s.Nom COMME sCHEMA_NAME , O.Nom COMME oBJECT_NAME , CONTRE.texte COMME OBJECT_DEFINITION
DE
     syscomments C INTERNE JOINDRE sys.O objets
                     NOUS
     CONTRE.identifiant
     =
     O.object_id
                   INTERNE JOINDRE sys.schémas S
                   NOUS
     O.schema_id
     =
     S.schema_id

    CONTRE.texte COMME
     & # 39;% & # 39;
   + @searchString
   + & # 39;% & # 39;
 OR O.Nom COMME
     & # 39;% & # 39;
   + @searchString
   + & # 39;% & # 39;
ORDRE PAR
       nom_schéma , nom_objet;

Les résultats renvoyés sont les suivants:

Le principal inconvénient de ces méthodes est que, pour chaque changement de type d'objet, vous souhaitez modifier le code SQL. Pour ce faire, vous devez connaître la structure des objets système, afin de pouvoir les modifier. La recherche de plusieurs types d'objet et l'ajout de critères de recherche, tels que l'inclusion / exclusion de noms d'objet et de leur contenu, ou l'utilisation de caractères d'échappement, ajoute encore plus de complexité à SQL, qui est sujet à des erreurs s'il n'est pas testé et prend beaucoup de temps.

Si vous n'êtes pas un développeur expérimenté et que vous n'êtes pas familiarisé avec les objets système contenant des informations DDL, vous souhaiterez disposer d'une solution testée et sans erreur pour rechercher manuellement des données et des objets SQL. Pour ce faire, utilisez ApexSQL Search.

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

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

  1. Dans le menu principal de SQL Server Management Studio ou de Visual Studio, cliquez sur ApexSQL Search

  2. Sélectionnez une option Recherche textuelle dans la base de données … :

  3. Dans le champ de texte à rechercher, entrez la valeur que vous souhaitez rechercher

  4. Dans la liste déroulante Base de données, sélectionnez la base de données

  5. Dans l'arbre Sélectionner les objets à rechercher, sélectionnez les tables et les vues pour les parcourir ou les laisser toutes activées

  6. Indiquez si vous souhaitez rechercher dans les colonnes vues, numérique, texte, identificateur unique et date en sélectionnant les cases à cocher correspondantes et si vous souhaitez rechercher une correspondance exacte. Si la recherche concerne les colonnes de date, spécifiez le format de la date:

  7. Cliquez sur l'option Trouve maintenant. La grille est remplie avec les tables de la base de données et les vues contenant la valeur entrée:

  8. Cliquez sur le bouton de sélection Valeur de la colonne pour afficher les détails de l'objet trouvé:

Pour rechercher des objets:

  1. Dans le menu principal de SQL Server Management Studio ou Visual Studio, dans le menu ApexSQL, cliquer sur ApexSQL Search.

  2. Sélectionnez l'option Recherche d'objet de base de données …:

  3. Sur le terrain Recherche de texte, entrez le texte que vous souhaitez rechercher (par exemple un nom de variable)

  4. Dans la liste déroulante Base de données, sélectionnez la base de données à rechercher
  5. Dans la liste déroulante Objets, sélectionnez les types d’objets à parcourir ou laissez-les tous activés
  6. Indiquez si vous souhaitez rechercher dans les noms d'objet, les colonnes, les index, les corps d'objet, les objets système, en cochant les cases correspondantes, s'il convient de rechercher une correspondance exacte et quel caractère d'échappement utiliser.
  7. Cliquez sur l'option Trouve maintenant

    La grille est remplie avec les objets de base de données contenant l'objet spécifié.

  8. Double-cliquez sur l'objet dans la grille Recherche d'objet de base de données, et celui-ci sera mis en évidence dans le navigateur 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 cherchent cela sont complexes, lentes et nécessitent une connaissance des objets système SQL Server. Utilisation ApexSQL Search pour fouiller dans vos bases de données et trouver les 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

20 octobre 2014

Commentaires

Laisser un commentaire

Votre commentaire sera révisé par les administrateurs si besoin.