Qu’est-ce qu’un dictionnaire de données ?

Dans SQL Server, le dictionnaire de données est un ensemble de tables de base de données utilisées pour stocker des informations sur la définition d’une base de données. Le dictionnaire contient des informations sur les objets de base de données tels que les tables, les index, les colonnes, les types de données et les vues.

Le dictionnaire de données est utilisé par SQL Server pour exécuter des requêtes et est automatiquement mis à jour chaque fois que des objets sont ajoutés, supprimés ou modifiés dans la base de données.

Tous les exemples de cet article sont basés sur Microsoft SQL Server Management Studio et la base de données AdventureWorks2012. Vous pouvez commencer à utiliser ces outils gratuits en utilisant mon Guide Pour commencer à utiliser SQL Server.

Comment SQL Server utilise le dictionnaire de données

SQL Server utilise le dictionnaire de base de données pour vérifier les instructions SQL. Lorsque vous exécutez une instruction SQL, le SGBD (Système de gestion de base de données) analyse l’instruction, puis détermine si les tables et les champs auxquels vous faites référence sont valides. Pour ce faire rapidement, il fait référence au dictionnaire de données.

En plus de tester la validité des instructions, SQL Server utilise le dictionnaire de données pour aider à la génération de plans de requêtes et pour référencer les informations définissant la structure de la base de données.

Le dictionnaire de données devient un guide à utiliser par SQL Server pour accéder à vos données. En termes simples, sans le dictionnaire de données, bien que SQL Server connaisse et comprenne le langage SQL, il ne connaîtrait pas vos tables et colonnes de base de données ; par conséquent, il ne serait pas en mesure de les interroger.

Autres utilisations du Dictionnaire de données

Étant donné que le dictionnaire de données contient la définition de la base de données, c’est une très bonne ressource à utiliser pour obtenir des informations sur la base de données. Ce qui est vraiment cool, c’est que le dictionnaire de données est composé de tables et de vues SQL. Cela signifie que vous pouvez obtenir des informations sur la base de données via des requêtes!

Les dictionnaires de données sont utilisés par les concepteurs et les développeurs pour comprendre la structure de la base de données. Vous pouvez considérer le dictionnaire comme un document de référence à jour.

Les outils de conception tels que SQL Server Management Studio affichent des informations sur les bases de données via l’explorateur d’objets à l’aide du dictionnaire de données.

Les tables énumérées ci-dessus ne sont pas connues par magie, l’explorateur d’objets a plutôt envoyé une requête au dictionnaire de données pour récupérer toutes les tables utilisateur .

Le dictionnaire de données est stocké dans une série de tables système. Bien que vous puissiez interroger directement ces tables, Microsoft se réserve le droit de modifier les tables système qui composent le dictionnaire de données. Pour cette raison, ils vous recommandent d’interroger les vues INFORMATION_SCHEMA plutôt que d’accéder directement aux tables.

Comme vous pouvez interroger vous-même le dictionnaire de données, vous pouvez répondre à certaines questions qui nécessiteraient autrement beaucoup de chasse et de picoration via l’explorateur d’objets. Par exemple, comment trouver facilement toutes les tables et vues à l’aide de la colonne BusinessEntityID ? Sans le dictionnaire de données, vous devrez utiliser l’explorateur d’objets et ouvrir chaque table et afficher et parcourir les définitions de la colonne. Cependant, en utilisant le dictionnaire de données, cela peut être fait en utilisant une requête simple.

Vues INFORMATION_SCHEMA

Les vues INFORMATION_SCHEMA incluses dans SQL Server sont conformes à la norme ISO SQL-92. Cela signifie que d’autres fournisseurs de bases de données conformes à la norme ISO fourniront le même ensemble de vues.

Voici quelques-unes des vues les plus couramment utilisées et leurs descriptions:COLONNES

• – Renvoie une ligne pour chaque colonne que l’utilisateur actuel a accès à utiliser dans la base de données actuelle. Cette vue peut être utilisée pour déterminer le type de données et la table que la colonne doit utiliser.TABLES
• – Renvoie une ligne pour chaque table que les utilisateurs ont accès à utiliser dans la base de données en cours. Notez que les tables et les vues sont renvoyées à l’aide de la vue TABLES.
• VIEW_TABLE_USAGE – Renvoie une ligne pour chaque table utilisée dans une vue de la base de données en cours.
• VUES – Renvoie une ligne pour les vues accessibles à l’aide des autorisations de l’utilisateur currents à partir de la base de données en cours.

Exemples utilisant des vues INFORMATION_SCHEMA

Liste de toutes les tables qui incluent une colonne spécifiée

Vous pouvez le faire en utilisant INFORMATION_SCHEMA.Vue COLONNES. Par exemple, les listes suivantes toutes les tables et vues contiennent la colonne BusinessEntityID

SELECT TABLE_NAMEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME = 'BusinessEntityID'

Comme vous pouvez l’imaginer, le dictionnaire de données peut être utile! Pouvez-vous imaginer devoir rechercher chaque définition à la main pour trouver toute l’occurrence d’un champ dans la base de données?

Listez toutes les tables d’une base de données

Utilisez le paramètre INFORMATION_SCHEMA.Vue TABLEAUX pour ce faire.

SELECT TABLE_NAME, TABLE_TYPEFROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESORDER BY TABLE_NAME

Cette requête renvoie à la fois des tables de base et des vues.

Liste le Nombre de tables dans chaque Schéma

Dans cet exemple, chaque schéma ainsi que le nombre de tables et de vues définies dans ceux-ci sont répertoriés :

SELECT TABLE_SCHEMA, Count(TABLE_SCHEMA)FROM AdventureWorks2012_Data.INFORMATION_SCHEMA.TABLESGROUP BY TABLE_SCHEMA

Vues de catalogue SQL Server

Les vues de catalogue sont une autre façon d’afficher le dictionnaire de données. Je pense que les vues du catalogue d’objets fournissent plus d’informations et j’ai tendance à utiliser ces vues sur les vues INFORMATION_SCHEMA.

Certaines des vues que j’ai trouvées les plus utiles incluent :

• objets – contiennent une ligne pour chaque objet, comme une CLÉ ÉTRANGÈRE ou une contrainte de CLÉ PRIMAIRE définie dans la base de données.colonnes
• – contiennent une ligne pour chaque colonne d’un objet tel qu’une vue ou des tables.
• tables – renvoie une ligne pour chaque objet table

Exemples utilisant des Vues de catalogue

Les exemples suivants utilisent les vues de catalogue Microsoft SQL Server pour obtenir les mêmes informations que celles utilisées ci-dessus à l’aide du système INFORMATION_SCHEMA.

Liste de toutes les tables qui incluent une colonne spécifiée

Afin d’obtenir les noms des tables en utilisant une colonne spécifique, à la fois le système.tables et systèmes.la vue colonnes doit être utilisée ensemble. Ils sont joints via object_id, qui est utilisé pour identifier les objets de base de données communs tels que les tables et les vues.

SELECT t.name, t.type_descFROM AdventureWorks2012_Data.sys.columns AS cINNER JOIN sys.tables AS t ON c.object_id = t.object_idWHERE c.name = 'BusinessEntityID'

Liste toutes les tables d’une base de données

Le système.la vue tables peut être utilisée pour obtenir les noms de toutes les tables définies dans la base de données. Cette vue renvoie à la fois des tables de base et des vues.

SELECT name, type_descFROM AdventureWorks2012_Data.sys.tablesORDER BY Name

Liste le Nombre de tables dans Chaque Schéma

Le système.la vue tables n’inclut pas de nom de schéma, donc la fonction intégrée SCHEMA_NAME est utilisée pour l’obtenir.

SELECT SCHEMA_NAME(schema_id), count(name)FROM AdventureWorks2012_Data.sys.tablesGROUP BY SCHEMA_NAME(schema_id)

Comme vous pouvez le voir dans l’exemple, c’est un peu plus technique que d’utiliser INFORMATION_SCHEMA. Cependant, je dis par expérience qu’il y a beaucoup plus d’informations contenues dans les vues de catalogue If Si vous avez une question particulière sur la structure d’une base de données, ce sont les tables à atteindre.