Discussion :

[olra72]

Bonjour,

J'ai tenté d'utiliser le "réorganiser les tables" du "Schéma de base de données" de SQL server management studio (après y avoir mis toutes les tables et - je l'espère - connecté toutes les relations). Pour mes 24 tables, ça me donne ce qui suit :



J'ai beau avoir des conventions de noms (ID_xx pour les clés primaires, xx_ID pour les clés étrangères), je ne suis pas absolument sûr de les avoir toutes déclarées, qu'elles soient bien en 1-N ou 1-1, avec ou sans Delete Cascade (ou trigger faisant un équivalent pour les tables auto-référencées) et - de toute façon - le diagramme final me paraît tout sauf lisible !!

Vous qui avez de l'expérience, comment gérez-vous ce genre de problématiques ? Existe-t-il des outils pour produire automatiquement une partie de la documentation ? Existe-t-il une stratégie pour scinder un schéma complet en N-sous-schémas, avec la garantie de ne rien oublier au passage ?

Cordialement,
Olivier

PS : .. et à ceux qui me diront qu'ils faut un schéma clair AVANT de coder quoi que ce soit, je répondrai que je travaille avec des utilisateurs qui ne savent pas ce qu'ils veulent ni quels sont - ou seront - leur besoins futurs. Donc je discute avec eux, je crée des tables et les formulaires pour "maquetter" ce que j'ai compris de leurs besoins. La maquette sert de base aux discussions visant à rectifier le tir, avec souvent des rebonds dans des directions inattendues (Ah bon ? C'est possible de faire ça avec un ordi ? Putain, fallait le dire ! Alors, dans ce cas, on pourrait faire ...). Sans me vanter, mes utilisateurs apprécient de discuter avec moi et d'avancer à tâtons, plutôt que se prendre la tête avec le service informatique qui leur réclame "des cahiers des charges précis et définitifs", chose qu'il sont bien incapables de produire (d'ailleurs, c'est pas leur boulot). Bref, les tables, les relations, ça va, ça vient ... et je voudrais pouvoir produire et maintenir des schémas clairs - mais APRES COUP !

[olra72]

D'ailleurs rien qu'en me baladant dans mon plat de spaghetti, j'ai déjà trouvé deux relations que j'avais oublié de "brancher" ... vive les conventions de nommage !

Ensuite, à l'usage, j'ai aussi trouvé des clés primaires (numériques) où j'avais oublié d'installer l'équivalent du NUMEROAUTO d'Access.

Je vais voir si, depuis Access, on peut parcourir des tables SQL et obtenir des infos sur leurs champs, leurs index, leurs relations, etc. (comme je le faisais pour des tables Access).

Cordialement,
Olivier

[olra72]

Bon, devant l'enthousiasme général, je vais répondre à mes questions moi-même, ça pourra servir à d'autres ...

Alors, dans Vues / Vues du système (voir ci-dessous), il y a des choses comme sys.tables, sys.objects et sys.columns qui permettent de faire divers bilans (comme de compter ses tables, lister ses clés étrangères, etc.).


Ha oui, mes clés étrangères se terminent toutes par "_ID" (en majuscules) et, pour les retrouver dans sys.columns, il me faut faire : SELECT * FROM sys.columns WHERE name COLLATE Latin1_General_BIN Like '%\_ID' ESCAPE '\' (on y retrouve l'imposition de la case-sensitivity et la recherche d'un underscore, deux points qui résistent un peu).

Sinon, dans l'explorateur d'objets, quand on ouvre simultanément les Colonnes et les Index, on peut répérer facilement les clés étrangères (les petites clés grises). D'ailleurs, dans l'image qui suit, je vois que j'ai oublié de déclarer "Suivi_ID" comme telle, comme quoi, les conventions de noms, c'est pratique pour le contrôle.


Voila, voila ... et je continue à chercher !

Cordialement,
Olivier

[aieeeuuuuu]

Bonjour,

si le but est de trouver les contraintes référentielles non déclarées en se basant sur la convention de nommage _ID, alors, vous pouvez compléter votre requête avec les tables sys.foreign_keys et sys.foreign_key_columns dans lesquelles vous trouverez les informations concernant les clefs étrangéres.

Si le besoin va plus loin que ça, alors il faudrait le préciser un peu...

[olra72]

Comme le dit "aieeeuuuuu" (dont je viens de voir le message), pour les clés étrangères, il y a aussi sys.foreign_keys (toujours dans Vues / Vues du système) mais ça suppose des conventions de noms stables ou de suivre les divers _id pour remonter aux champs gauche-droite concernés.

Il y a aussi plein d'info dans la série des INFORMATION_SCHEMA.xxx (mais je me demande quand même si ces vues sont toujours up-to-date ou si une action est requise pour les actualiser ?).

Pour retrouver les descriptions des champs des tables, c'est dans sys.extended_properties (un grand merci à http://stackoverflow.com/questions/8...eir-data-types).

Par exemple, pour retrouver les champs dont la description contient "organ", ça commence par SELECT * FROM sys.extended_properties Where CONVERT(CHAR,value) LIKE '%organ%' à combiner avec sys.all_columns via major_id et minor_id (qui sont à recoller respectivement avec object_id et column_id) pour retrouver le petit nom des champs.

Maintenant, pour définir et retrouver une description générale des tables elles-mêmes, je cherche encore ...

[aieeeuuuuu]

pour définir les propriétés etendues, vous avez la procédure systéme sys.sp_addextendedproperty

[olra72]

En fait, la description des tables atterrit bien dans sys.extended_properties mais je cherchais à retrouver le texte "Alarmes associées à la surveillance radiologique de points de stockage" via la requête suivante :
SELECT * FROM sys.extended_properties WHERE convert(char,value) like '%adio%'
.. qui ne me retournait rien, alors je me suis dit que ma propriété n'était pas là ..
.. alors, qu'en fait, je devais faire :
SELECT * FROM sys.extended_properties WHERE convert(char(250),value) like '%adio%'
Ça marche aussi avec :
SELECT * FROM sys.extended_properties WHERE convert(varchar(MAX),value) like '%adio%'

... mais je n'ai rien trouvé de tel dans les docs que j'ai pu consulter. Visiblement le "convert(char,.." tout seul se limite à un certains nombres de caractères ou quelque chose comme ça ..

Cordialement,
Olivier

[Invité]

bizarre...
Je me servais de ça pour avoir les ExtendedProperty sur une table :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
-- liste des Extended Properties d'une table et de ses colonnes + suppression des Extended Properties
SELECT SCHEMA_NAME(OBJ.schema_id) AS TABLE_SCHEMA , OBJ.name AS TABLE_NAME
	, COALESCE(COL.COLUMN_NAME,'*** TABLE LEVEL ***') AS COLUMN_NAME, COL.ORDINAL_POSITION, COL.IS_NULLABLE, COL.DATA_TYPE
	, EXT.NAME AS ExtendedPropertyName, EXT.value AS ExtendedPropertyValue
	, 'EXEC sys.sp_dropextendedproperty @name=N'''+EXT.NAME+''' , @level0type=N''SCHEMA'',@level0name=N'''+SCHEMA_NAME(OBJ.schema_id)+''', @level1type=N''TABLE'',@level1name=N'''+OBJ.name+''''
	+ CASE WHEN COL.COLUMN_NAME IS NULL THEN '' ELSE ', @level2type=N''COLUMN'',@level2name=N'''+COL.COLUMN_NAME+'''' END as DropExtendedProperties
FROM sys.objects AS OBJ
INNER JOIN sys.extended_properties AS EXT ON EXT.major_id = OBJ.object_id AND EXT.class = 1 -- OBJECT_OR_COLUMN
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS AS COL 
	ON COL.TABLE_SCHEMA = SCHEMA_NAME(OBJ.schema_id) AND COL.TABLE_NAME = OBJ.name AND COL.ORDINAL_POSITION = EXT.minor_id
-- WHERE OBJ.schema_id = SCHEMA_ID('dbo') AND OBJ.name = 'CustomerCommentCategory'
ORDER BY COL.ORDINAL_POSITION, EXT.NAME

[SQLpro]

Comme le dit "aieeeuuuuu" (dont je viens de voir le message), pour les clés étrangères, il y a aussi sys.foreign_keys (toujours dans Vues / Vues du système) mais ça suppose des conventions de noms stables ou de suivre les divers _id pour remonter aux champs gauche-droite concernés.

Il y a aussi plein d'info dans la série des INFORMATION_SCHEMA.xxx (mais je me demande quand même si ces vues sont toujours up-to-date ou si une action est requise pour les actualiser ?).

Ces vues, comme toutes les vues, sont des requêtes portant les tables, en l'occurrence ici les tables systèmes...
Pour avoir la liste des contraintes ne répondant pas à votre norme de dénomination, voici comment faire :

Code :

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

1
2
3
4
SELECT *
FROM  INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND CONSTRAINT_NAME COLLATE French_CI_AI NOT LIKE 'FK?_T?_%' ESCAPE '?'

Par exemple.

A +

[olra72]

Ok ! Merci à tous ! Je crois que j'ai tout le nécessaire.

Cordialement,
Olivier