Catégories : Expertise TechniquePar Tags:

Récemment je me suis retrouvé avec une erreur telle que la suivante :

error message

On se doute qu’il y a un problème dans la base de données. Malheureusement celle-ci contient plusieurs dizaines de tables et nous avons peu d’informations pour savoir où chercher ce guid…

Comme je n’avais pas spécialement envi de chercher table par table, j’ai préféré envoyer mon enquêteur :

illustration

Celui-ci sort de son chapeau une petite requête SQL et quelques secondes après le résultat sort :

requête SQL

On obtient le nom de la table et de la colonne contenant la valeur recherchée, ainsi que la requête SQL toute prête pour sélectionner la ligne recherchée au besoin :)

Fini le suspense, voici la requête :

DECLARE @searchValue uniqueidentifier = 'a2843a1e-6ed4-4045-a179-51f0743943b8'     
DECLARE @sql NVARCHAR(MAX);     
WITH cte_sql_queries(sql_query) AS (  
   SELECT 'SELECT ''' + QUOTENAME(t.TABLE_SCHEMA) + ''' schema_name '     
   + '   , ''' + QUOTENAME(t.TABLE_NAME) + ''' table_name '     
   + '   , ''' + QUOTENAME(c.COLUMN_NAME) + ''' column_name '     
   + '   , ''SELECT ' + QUOTENAME(c.COLUMN_NAME) + ', * FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' WHERE ' +  QUOTENAME(c.COLUMN_NAME) + '='''''+ CAST(@searchValue AS NVARCHAR(36)) +''''''' query '     
   + ' FROM ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)     
   + ' WHERE ' +  QUOTENAME(c.COLUMN_NAME) + '=@searchValue'     
   FROM INFORMATION_SCHEMA.Columns c     
   INNER JOIN INFORMATION_SCHEMA.Tables t     
   ON t.TABLE_NAME = c.TABLE_NAME     
   AND t.TABLE_SCHEMA = c.TABLE_SCHEMA     
   AND t.TABLE_TYPE = 'BASE TABLE'     
   AND c.DATA_TYPE = 'uniqueidentifier'     
)     
SELECT @sql = STUFF((SELECT ' UNION ALL ' + sql_query FROM cte_sql_queries FOR XML PATH('')), 1, LEN(' UNION ALL '), '')     
exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue;

Explication de la requête

La requête peut sembler barbare mais si la décompose, on se rend compte qu’elle est au final plutôt simple.

Etape 1 : Sélectionner toutes les colonnes de type uniqueidentifier

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, c.COLUMN_NAME     
FROM INFORMATION_SCHEMA.Columns c     
INNER JOIN INFORMATION_SCHEMA.Tables t     
   ON t.TABLE_NAME = c.TABLE_NAME     
   AND t.TABLE_SCHEMA = c.TABLE_SCHEMA     
   AND t.TABLE_TYPE = 'BASE TABLE'    -- Sélectionne uniquement les tables (pas les vues)     
   AND c.DATA_TYPE = 'uniqueidentifier'

table

En concaténant ces valeurs on peut créer une requête telle que

SELECT '[dbo].[Customer].[Customer_Id]' FROM [dbo].[Customer] WHERE [Customer_Id]=@searchValue

C’est en fait la requête générée par le SELECT et tous les QUOTENAME en version simplifiée bien évidemment. La CTE (Common Table Expression) crée une liste de requêtes comme ci-dessus

table 2

Etape 2 : Concaténer les requêtes avec des UNION ALL

On souhaite concaténer toutes les lignes, lignes ne contenant qu’une seule colonne. Pour cela le plus simple est d’utiliser FOR XML. Cela permet de générer une sortie au format XML.

SELECT ' UNION ALL ' + sql_query FROM cte_sql_queries FOR XML PATH('')

PATH permet de spécifier le nom de la balise XML englobant le XML d’une ligne :

SELECT 'John' as FirstName, 'Doe' as LastName FOR XML PATH     
-- <row><FirstName>John</FirstName><LastName>Doe</LastName></row>     
  
SELECT 'John' as FirstName, 'Doe' as LastName FOR XML PATH('sample')     
-- <sample><FirstName>John</FirstName><LastName>Doe</LastName></sample>     
  
SELECT 'John' as FirstName, 'Doe' as LastName FOR XML PATH('')     
-- <FirstName>John</FirstName><LastName>Doe</LastName>

En indiquant PATH (‘’) on indique en fait de ne pas englober le XML d’une ligne par une balise. Comme il n’y a qu’une seule colonne par ligne le résultat ne contient au final aucune balise XML et correspond donc à la concaténation des lignes.

Il reste cependant un petit problème à régler : il y a un UNION ALL en trop au début de la chaine concaténée. 2 solutions :

  • Soit on préfixe la requête avec un SELECT ne faisant rien, mais permettant d’obtenir une requête SQL valide
'SELECT NULL, NULL, NULL, NULL  WHERE 0=1' + (SELECT … FOR XML PATH(''))
  • L’autre solution est de supprimer ce UNION ALL de la chaine de caractère à l’aide de la fonction STUFF. Cette fonction permet de remplacer une partie d’une chaine de caractères par une autre. Dans notre cas on souhaite remplacer les 11 premiers caractères (longueur de ‘ UNION ALL ‘) par rien (chaine vide) :
STUFF((SELECT ' UNION ALL ' + sql_query FROM cte_sql_queries FOR XML PATH('')), 1, LEN(' UNION ALL '), '')

Etape 3 : Exécuter la requête construite dynamiquement

Nous avons maintenant la requête SQL à exécuter dans une chaine de caractère, il suffit donc de l’exécuter :

exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue;

Et voilà !

Ne ratez plus aucune actualité avec la newsletter mensuelle de SoftFluent

Newsletter SoftFluent