Evolution du schema d'une db

**Philippe Robert** · 15/02/2008, 11h13

Bonjour,

En vue de faire évoluer le schema d'une base de données, des colonnes devenues obsolètes doivent être supprimées.

Pour ce faire, je dois pouvoir rechercher toutes les requêtes qui utilisent ces colonnes afin de les adapter.

J'ai donc créé un serveur de test contenant une copie de la structure de la base de données en production.

Afin d'identifier les vues et procédures stockées, j'ai testé différentes possibilités :

1. recherche de texte dans la table système sys.sql_modules avec le nom de la colonne et le nom de la table.

Cependant, cette méthode n'est pas fiable car on peut retrouver des procédures utilisant un nom de colonne provenant d'une autre table que celle qui m'intéresse.

2. Renom de la colonne à supprimer et utilisation de la commande sp_refreshview sur toutes les vues de la DB. Ceci me permet de détermier toutes les vues qui ne fonctionneront plus après le renom de la colonne.

Là ou je cale avec cette méthode, c'est avec les procédures stockées car il ne semble pas exister d'équivalent de sp_refreshview pour les proc. stockées.

J'ai donc tenter une autre méthode consistant à réexécuter toutes les procédures avec des paramètres bidons (NULL) afin d'identifier celle qui échoueront.

Afin d'identifier les procédures en question, j'effectue le select suivant

SELECT so.Type as ObjectType,
ss.name + '.' + so.name as ObjectName,
ISNULL(np.NombreParametre,0) as NombreParametre
FROM sys.objects so
INNER JOIN sys.schemas ss ON so.schema_id = ss.schema_id
LEFT JOIN (
SELECT sp.object_id, COUNT(*) as NombreParametre
FROM sys.parameters sp
GROUP BY sp.object_id
) np ON so.object_id = np.object_id

Ensuite, je constitue une chaine avec le nom de la procédure et autant de NULL qu'il y a de paramètres.

Cependant, cette méthode ne me plait pas car cela nécessite la réexécution de toutes les procédures.

Avez-vous d'autres idées afin que je puisse identifier les procédures stockées à adapter a la suite de la suppression d'une colonne de table ?

**ylarvor** · 15/02/2008, 17h42

1 génération d'un script des procédures stockées à l'aide du manager. ( c'est quasi instantannée ).

2 Utilisation d'un outil de recherche de texte ( ultraedit-32 me parrait bien,visual studio par defaut ) vous permettant de rechercher les occurrences de mot.

3 Suppression des procedures.

4 Passage du script de creation des procedures.

**SQLpro** · 16/02/2008, 19h17

Cette question est très intéressante car j'ai démontré il y a peu à Joe Celko, l'intérêt d'utiliser une norme de nommage dans une base de données.

En effet, avec la norme de nommage que je préconise (et que Rudi déteste) cette opération devient triviale.

Quelle est-elle ?
1) toute table constitué d'un nom formaté somme suit :
T_[?_]!!!!!!!_@@@
ou :
? est un éventuel deuxième suffixe (A pour table Administrative, R pour table de référence...)
!!!... est le nom donné à la table
@@@ un trigramme (trois lettres) UNIQUE au sein de la base

Exemple :
T_CLIENT_CLI, T_A_UTILISATEUR_USR, T_R_CODE_POSTAL_CDP...

2) tout nom de colonne propre à l'entité reprend en préfixe le trigramme de la table (les colonnes de type clef étrangères découlant du MCD conservant le trigramme de la table d'origine), les noms étant censé et non vague :
CLI_ID, CLI_NOM, CLI_PRENOM, CLI_DATE_NAISSANCE
pour une table facture :
FCT_ID, FCT_DATE_EMISSION (et non FCT_DATE qui est trop vague), CLI_ID (parce que clef étrangère).

toute colonne calculée est suffixée _CLC

3) toute vue est formaté comme un nom de table avec en préfixe V_

4) les noms des fonctions commencent par F_!!!! et pour les fonctions tables :
F_[?_][!!!!!_]@@@

5) les noms des procédures sont formatées comme ceci :
P_?_[@@@_]_!!!!!!
ou ? peut contenir de 1 à 4 lettres parmi I (INSERT), U (UPDATE), D (DELETE), S (SELECT), A (Admin) ...

par exemple :
P_IU_CLI : procédure d'insertion ou update pour table ou objet T_CLIENT
P_A_MAINTINDEX :

....etc....

grâce à ceci, trouver toutes les références à une colonne d'une table dans l'ensemble des vues ou des routines devient un jeu d'enfant :

(extrait du mail à Joe Celko )

How to find all views having a table column using it ?
In fact we can have views from views from views.... from tables....
Here is the difficulties....

And the answer is :
strong namming convention + CTE =>

Searching all views of any level referencing STE_TVA_CODE_PAYS column :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
WITH V_VIEWS (VIEW_SCHEMA, VIEW_NAME, VIEW_DEFINITION, VIEW_LEVEL)
AS
(
-- views having a column nammed STE_TVA_CODE_PAYS
SELECT TABLE_SCHEMA AS VIEW_SCHEMA,
       TABLE_NAME AS VIEW_NAME,
       VIEW_DEFINITION,
       0 AS VIEW_LEVEL
FROM   INFORMATION_SCHEMA.VIEWS
WHERE  VIEW_DEFINITION LIKE '%STE_TVA_CODE_PAYS%' --> the column to find
UNION ALL
-- views having a view based on a column nammed STE_TVA_CODE_PAYS
SELECT V1.TABLE_SCHEMA AS VIEW_SCHEMA,
       V1.TABLE_NAME AS VIEW_NAME,
       V1.VIEW_DEFINITION,
       V2.VIEW_LEVEL + 1
FROM   INFORMATION_SCHEMA.VIEWS V1
       INNER JOIN V_VIEWS V2
             ON V1.VIEW_DEFINITION
                LIKE '%' + V2.VIEW_SCHEMA + '.' + V2.VIEW_NAME + '%'
WHERE  V2.VIEW_DEFINITION LIKE '%STE_TVA_CODE_PAYS%'
  AND  V2.VIEW_SCHEMA + '.' + V2.VIEW_NAME <> V1.TABLE_SCHEMA + '.' + V1.TABLE_NAME
)
SELECT *
FROM   V_VIEWS

car n'oubliez pas qu'il peut y avoir des vues de vues !

Pour les proc stock et fonctions, utilisez INFORMATION_SCHEMA.ROUTINES

A +

**Philippe Robert** · 16/02/2008, 19h46

Hélas, la norme mise en place dans ma société ne ressemble pas à cela.

En outre, cela ne résoud pas entièrement mon problème. En effet, j'ai aussi des requêtes faisant SELECT * (je sais, SELECT * est a éviter, mais il en existe un tas qui existaient bien avant que je sois en fonction au poste de DBA).

Si je supprime une colonne d'une table, le SELECT * fonctionnera, mais si la colonne en question est utilisée dans l'application, cela provoquera un plantage.

Et je ne parle pas de toutes les requêtes qui sont codées dans l'application, sans passer par une procédure stockée. Pour les identifier, je dois passer via le profiler et faire des recherches dans les traces.