IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Outils SQL Server Discussion :

Schéma de base de données (SQL Server M. Studio)


Sujet :

Outils SQL Server

  1. #1
    Membre habitué
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2016
    Messages
    136
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Drôme (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Santé

    Informations forums :
    Inscription : octobre 2016
    Messages : 136
    Points : 149
    Points
    149
    Par défaut Schéma de base de données (SQL Server M. Studio)
    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 :

    Nom : Capture.PNG
Affichages : 87
Taille : 15,4 Ko

    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 !

  2. #2
    Membre habitué
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2016
    Messages
    136
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Drôme (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Santé

    Informations forums :
    Inscription : octobre 2016
    Messages : 136
    Points : 149
    Points
    149
    Par défaut
    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

  3. #3
    Membre habitué
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2016
    Messages
    136
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Drôme (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Santé

    Informations forums :
    Inscription : octobre 2016
    Messages : 136
    Points : 149
    Points
    149
    Par défaut
    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.).
    Nom : Capture.PNG
Affichages : 64
Taille : 6,9 Ko

    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.
    Nom : Capture.PNG
Affichages : 57
Taille : 18,9 Ko

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

    Cordialement,
    Olivier

  4. #4
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 641
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 641
    Points : 13 067
    Points
    13 067
    Par défaut
    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...

  5. #5
    Membre habitué
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2016
    Messages
    136
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Drôme (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Santé

    Informations forums :
    Inscription : octobre 2016
    Messages : 136
    Points : 149
    Points
    149
    Par défaut
    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 ...

  6. #6
    Modérateur

    Profil pro
    dba
    Inscrit en
    janvier 2010
    Messages
    5 641
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : janvier 2010
    Messages : 5 641
    Points : 13 067
    Points
    13 067
    Par défaut
    pour définir les propriétés etendues, vous avez la procédure systéme sys.sp_addextendedproperty

  7. #7
    Membre habitué
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2016
    Messages
    136
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Drôme (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Santé

    Informations forums :
    Inscription : octobre 2016
    Messages : 136
    Points : 149
    Points
    149
    Par défaut
    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

  8. #8
    Expert éminent Avatar de 7gyY9w1ZY6ySRgPeaefZ
    Homme Profil pro
    dba
    Inscrit en
    juillet 2007
    Messages
    5 190
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : juillet 2007
    Messages : 5 190
    Points : 7 375
    Points
    7 375
    Par défaut
    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

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 723
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 723
    Points : 49 092
    Points
    49 092
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par olra72 Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  10. #10
    Membre habitué
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    octobre 2016
    Messages
    136
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 61
    Localisation : France, Drôme (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux
    Secteur : Santé

    Informations forums :
    Inscription : octobre 2016
    Messages : 136
    Points : 149
    Points
    149
    Par défaut
    Ok ! Merci à tous ! Je crois que j'ai tout le nécessaire.

    Cordialement,
    Olivier

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [Projet en cours] outil permettant de générer un schéma de la base de données SQL SERVER 2008
    Par ugar1984 dans le forum Projets
    Réponses: 2
    Dernier message: 03/08/2014, 23h03
  2. [C#] Lister les Bases de données (SQL SERVER) d'un poste
    Par justice007 dans le forum Accès aux données
    Réponses: 9
    Dernier message: 21/06/2010, 16h10
  3. probleme base de donnée SQL server
    Par waguila666 dans le forum ASP
    Réponses: 9
    Dernier message: 01/12/2004, 11h54
  4. Réponses: 3
    Dernier message: 29/03/2004, 18h02

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo