Pour afficher la liste des tables SQL Server d’une base de données, plusieurs solutions existent. En effet, pour auditer une base des données et ses tables, il est nécessaire d’utiliser la liste des tables sous forme de table ou de tableau Excel. Les développeurs SQL Server recherchent aussi souvent des tables, par exemple pour les analyses d’impact.
Ainsi, toutes les solutions présentées ici utilisent les tables systèmes, comme par exemple:
- information_schema.tables
- sys.tables
- sys.indexes
- sys.partitions
- sys.allocation_units
- ou encore sys.schemas.
Il est en effet utile de lister rapidement toutes les tables pour un audit, qu’il soit technique ou fonctionnel.
Requête pour afficher la liste des tables d’une base de données SQL Server
Pour commencer, ces trois requêtes T-SQL permettent d’afficher les informations de manière différente. La première est une simple liste des tables. Tandis que la deuxième affiche des informations utiles comme la colonne object_id ou encore la date de création et de modification, soit create_date et modify_date.
La troisième requête affiche quant à elle le schéma, le nombre de lignes et l’espace disque utilisé par chaque table.
Afficher toutes les tables depuis la table système information_schema.tables
On peut par exemple utiliser la table système information_schema.table, comme suit:
SELECT * FROM information_schema.tables WHERE table_type='BASE TABLE';
Liste des tables SQL Server avec une requête sur sys.tables
On peut également avoir recours à la table système sys.tables, on peut voir dans le résultat de la requête que le type des tables stocké dans la colonne [type_desc] est USER_TABLE.
SELECT * FROM sys.tables;
Afficher les tables et leur taille depuis plusieurs tables systèmes
Pour lister toutes les tables d’une base sql server et certaines propriétés, iI est possible d’utiliser plusieurs tables système SQL Server comme sys.tables, sys.indexes, sys.partitions, sys.allocation_units et sys.schemas. Ces tables sont utiles pour afficher la liste des tables.
Elles permettent aussi d’afficher le nombre de lignes dans chaque table et l’espace disque utilisé. Cette requête affiche par exemple toutes les tables avec leur nombre de lignes triées par ordre décroissant.
SELECT
sch.name AS SCHEMANAME,
tab.name AS TABLENAME,
par.rows AS ROWCOUNTS,
SUM(alc.total_pages) * 8 AS TOTAL_SPACE,
SUM(alc.used_pages) * 8 AS USED_SPACE,
(SUM(alc.total_pages) - SUM(alc.used_pages)) * 8 AS UNUSED_SPACE
FROM sys.tables tab
INNER JOIN sys.indexes ind
ON tab.object_id = ind.object_id
INNER JOIN sys.partitions par
ON ind.object_id = par.object_id
AND ind.index_id = par.index_id
INNER JOIN sys.allocation_units alc
ON par.partition_id = alc.container_id
LEFT OUTER JOIN sys.schemas sch
ON tab.schema_id = sch.schema_id
GROUP BY tab.name, sch.name, par.rows
ORDER BY 3 desc;
Notes:
- Le mot clef ORDER BY 3 permet de trier la liste des tables SQL Server par leur nombre de lignes.
- Pour trier par schéma puis par nom de table, utiliser ORDER BY 1,2.
- La taille des tables est notée en Kilo-Octets.
Pour débuter sur SQL Server et apprendre comment créer une table, voici comment créer une table SQL Server et y insérer quelques lignes de données. Des tutoriels permettent aussi d’apprendre à modifier une colonne existante comme la longueur d’un champs.
