Rafraîchir les métadonnées des modules SQL

Publié le par

Après un certain temps de vie, ou après que la structure d’une base de données de développement ait été copiée depuis celle de production puis modifiée, il arrive que lorsqu’on recherche les dépendances d’un objet, dans les tables systèmes ou en choisissant l’option Dépendances du menu contextuel proposé par le clic-droit sur un module SQL (une procédure stockée, une fonction, un trigger, ou une vue définis par l’utilisateur), les données ou l’affichage soient incorrects, bien que l’on soit pourtant certain que l’objet que l’on vient de modifier dépend bien d’un autre.
Voici comment remédier à ce désagrément …

Il suffit pour cela de rafraîchir les métadonnées, avec la procédure stockée système sp_refreshsqlmodule, disponible sous SQL Server 2005 (SP2) et ultérieurs.
On peut faire cela pour un module SQL bien particulier, mais a y être, autant le faire pour tous les modules de la base de données en cours :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
---------------------------------
-- Nicolas SOUQUET - 16/01/2009 -
---------------------------------
DECLARE @nomModule SYSNAME
 
-- Recherche du nom de toutes les procédures stockées, fonction scalaires, fonctions table en ligne,  
-- fonction table et vues définies dans le contexte de base de données en cours
DECLARE curToRefresh CURSOR FOR
  SELECT name
  FROM sys.objects
  WHERE type IN ('P', 'FN', 'IF', 'TF', 'V')  
  AND name NOT LIKE 'dt%'
  AND name NOT LIKE 'sp%'
  ORDER BY name
FOR READ ONLY
 
-- Recherche des dépendances pour chacun de ces objets
OPEN curToRefresh
FETCH NEXT FROM curToRefresh INTO @nomModule
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    PRINT 'Procédure : ' + @nomModule
    BEGIN TRAN
      EXEC sys.sp_refreshsqlmodule @nomModule
    COMMIT TRAN
  END TRY
  BEGIN CATCH
    PRINT '  |---------------> ERREUR : ' + ERROR_MESSAGE()
    ROLLBACK TRAN
  END CATCH
  FETCH NEXT FROM curToRefresh INTO @nomModule
END
 
DEALLOCATE curToRefresh

Dès lors vous constaterez que les données contenues dans la vue système sys.sql_dependencies sous SQL Server 2005, ou dans sys.sysdepends dans SQL Server 2000, sont toutes fraîches !

Laisser un commentaire