Dans SQL Server, les informations concernant une colonne de table spécifique (nom de colonne, identifiant de colonne, type de données de colonne ou contraintes de colonne par exemple) peuvent être récupérées en joignant des tables système telles que sys.tables, sys.columns et sys.types.

Requête 1 : extraction de tables et d’object_id

À propos de sys.tables

sys.tables est une table système utilisée pour conserver les informations sur les tables d’une base de données. Un enregistrement est créé dans la table sys.tables pour chaque table ajoutée à la base de données. Il n’existe qu’un enregistrement pour chaque table, qui contient des informations telles que le nom et l’identifiant d’objet de la table, la date de création, la date de modification, etc. L’identifiant d’objet est unique. Nous l’utiliserons pour joindre cette table à d’autres tables système (sys.columns) afin d’extraire les informations des colonnes.

La requête suivante peut être utilisée pour extraire le champ object_id de toutes les tables d’une base de données :

Select name AS TableName, object_id AS ObjectID
From sys.tables
From sys.tables
–– where name = '<TABLENAME>'
–– Uncomment above line and add <Table Name> to fetch details for particular table

Le résultat sera semblable à ce qui suit :

TableName

InstrumentAudit
InstrumentMerge
InstrumentMerge_MinDates

ObjectID

375724441
379864420
395864477

Requête 2 : extraction de colonnes

La prochaine étape consiste à extraire des colonnes pour ces tables. Pour ce faire, vous pouvez joindre sys.tables à sys.columns selon object_id pour les tables de la base de données.

À propos de sys.columns

sys.columns est une table système utilisée pour conserver les informations sur les colonnes d’une base de données. Un enregistrement est créé dans la table sys.columns pour chaque colonne ajoutée à une base de données. Il n’existe qu’un enregistrement pour chaque colonne, qui contient les informations suivantes :

  • Name : nom de la colonne. Il est unique au sein de l’objet de table.
  • Object_id : object_id est un identifiant unique pour la table contenant la colonne. Nous utiliserons cette colonne pour joindre sys.columns à sys.tables afin d’extraire des colonnes dans différentes tables.
  • Column_id : identifiant de la colonne. Il est unique au sein de l’objet de table./li>
  • user_type_id : code système pour le type de données de colonne.
  • max_length : longueur maximale (en octets) de la colonne.
  • is_nullable : 1 si la colonne est de type nullable.

Cette table contient d’autres colonnes, mais celles que nous avons mentionnées ci-dessus suffiront à notre propos.

La requête suivante peut être utilisée pour extraire les informations des colonnes (pour les tables correspondantes) et leur identifiant de type de données en joignant sys.columns et sys.tables selon la colonne object_id :

SELECT TAB.name AS TableName, TAB.object_id AS ObjectID, COL.name AS ColumnName, COL.user_type_id AS DataTypeID
From sys.columns COL
INNER JOIN sys.tables TAB
On COL.object_id = TAB.object_id
-- where TAB.name = '<TABLENAME>'
-- Uncomment above line and add <Table Name> to fetch details for particular table
-- where COL.name = '<COLUMNNAME>'
-- Uncomment above line and add <Column Name> to fetch details for particular column names

Vous devriez obtenir le résultat suivant :

TableName

InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentMerge
InstrumentMerge
InstrumentMerge_MinDates
InstrumentMerge_MinDates
InstrumentMerge_MinDates

ObjectID

375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
379864420
379864420
395864477
395864477
395864477

ColumnName

AuditDate
AuditID
ValidFrom
ValidTo
ID
Name
Type
Description
CreateDate
InstrumentOrigin
AuditType
ReutersID
PairBBID
BBID
MinBBDate
ReutersID

DataTypeID

61
56
61
61
56
167
56
167
61
56
175
56
56
56
61
56

Voir Sisense en action :

Quality Assurance Project Status - Software Dashboard

Requête 3 : ajout d’un nom de type de données

La prochaine étape consiste à remplacer l’identifiant de type de données par le nom de type de données. Pour ce faire, vous pouvez joindre la requête précédente à la table sys.types.

À propos de sys.types

sys.types est une table système utilisée pour conserver les informations sur les types de données de colonne d’une base de données. Cette table contient une ligne pour chaque type de données et inclut les informations suivantes :

  • Name : nom de la colonne. Il est unique au sein de l’objet de table.
  • user_type_id : code système pour le type de données de colonne. Il est unique pour cette table et utilisé pour la jointure à la table sys.columns.
  • max_length : longueur maximale (en octets) de la colonne.

Comme précédemment, cette table comporte d’autres colonnes, mais celles que nous avons mentionnées ci-dessus suffiront à notre propos.

La requête suivante peut être utilisée pour remplacer l’identifiant de type de données par le nom de type de données en joignant sys.types à sys.columns sur user_type_id :

SELECT TAB.name AS TableName, TAB.object_id AS ObjectID, COL.name AS ColumnName, TYP.name AS DataTypeName, TYP.max_length AS MaxLength
From sys.columns COL
INNER JOIN sys.tables TAB
On COL.object_id = TAB.object_id
INNER JOIN sys.types TYP
ON TYP.user_type_id = COL.user_type_id

-- where TAB.name = '<TABLENAME>'
-- Uncomment above line and add <Table Name> to fetch details for particular table
-- where COL.name = '<COLUMNNAME>'
-- Uncomment above line and add <Column Name> to fetch details for particular column names
-- where TYP.name = '<DATATYPENAME>'
-- Uncomment above line and add <Data Type Name> to fetch details for particular Data Type

Il en résultera la table suivante, contenant les descriptions de colonne que nous recherchions :

TableName

InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentAudit
InstrumentMerge
InstrumentMerge
InstrumentMerge_MinDates
InstrumentMerge_MinDates
InstrumentMerge_MinDates

ObjectID

375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
375724441
379864420
379864420
395864477
395864477
395864477

ColumnName

AuditDate
AuditID
ValidFrom
ValidTo
ID
Name
Type
Description
CreateDate
InstrumentOrigin
AuditType
ReutersID
PairBBID
BBID
MinBBDate
ReutersID

DataTypeName

datetime
Int
datetime
datetime
Int
varchar
Int
varchar
datetime
int
char
int
int
int
datetime
int

MaxLength

8
4
8
8
4
8000
4
8000
8
4
8000
4
4
4
8
4

Approfondissement de vos connaissances sur les données SQL Server

Vous recherchez un outil de génération de rapports SQL vous permettant de réaliser facilement des jointures entre bases de données à l’aide de données de SQL Server, de sources de données non structurées ou encore de fichiers plats ? Commencez votre essai gratuit, connectez-vous à votre base de données SQL Server en quelques clics seulement, et découvrez l’informatique décisionnelle en action.

Tags: | |