Comment Afficher la Liste des Tables SQL Server ? T-SQL

Pour auditer une base de données SQL Server, établir une liste des tables est obligatoire, il est courant de vouloir cette liste sous forme de tableau ou de feuille Excel. Cette requête SQL permet de lister toutes les tables d’une base de données SQL Server et d’afficher le nombre de lignes et la taille pour chaque table. Les développeurs SQL Server cherchent également souvent des tables, par exemple pour une analyse d’impact. Il est donc très utile de lister rapidement toutes les tables pour un audit, qu’il soit technique ou fonctionnel. Pour commencer, ces trois différentes requêtes T-SQL permettent d’afficher les informations de différentes manières, et avec différents niveaux de détails.

La première est une simple liste des tables.
La deuxième requête affiche des informations utiles telles que la colonne object_id, create_date et modify_date.
La troisième requête affiche le schéma, le nombre de lignes et l’espace disque utilisé par chaque table.

En effet, les tables système SQL Server et les vues système contiennent des métadonnées utiles qui décrivent les différents objets dans la base de données, y compris les tables bien sûr. Ainsi, toutes les solutions présentées dans cet article utilisent des tables ou des vues système, telles que :

information_schema.tables
sys.tables
sys.indexes
sys.partitions
sys.allocation_units
ou sys.schemas

1. Afficher une liste de toutes les tables avec une seule requête

Dans SQL Server, information_schema.table est une vue, pas une table. C’est l’une des vues INFORMATION_SCHEMA standardisées par l’ISO pour les systèmes de gestion de bases de données relationnelles (SGBDR). Cette vue fournit des informations sur les tables et les vues présentes dans une base de données. Vous pouvez la consulter pour obtenir des métadonnées sur ces tables et vues, telles que leurs noms, types (table ou vue), et autres détails. Par exemple, vous pouvez utiliser la table système information_schema.table, comme ceci :

SELECT *
FROM   information_schema.tables
WHERE  table_type='BASE TABLE';

*Requête pour obtenir la liste de toutes les tables dans SQL Server*

2. Requête pour lister les tables SQL Server avec la vue sys.tables

Vous pouvez également utiliser la table système sys.tables. Le résultat de la requête montre que le type de table stocké dans la colonne [type_desc] est USER_TABLE. Sys.tables en Transact-SQL est également une vue système et non une table. Elle fait partie du catalogue système de SQL Server et fournit des informations sur toutes les tables utilisateur et tables système existant dans la base de données actuelle. Elle peut être utilisée pour récupérer divers détails et métadonnées sur les tables, tels que leur nom, date de création, date de modification, etc. Bien qu’elle puisse être interrogée comme une table, ne la traitez pas comme une table utilisateur éditable. Au lieu de cela, c’est une vue structurée qui présente certaines métadonnées système sur les tables.

SELECT *
FROM   sys.tables;

3. Afficher la liste des noms de tables et des tailles en T-SQL

La troisième consiste à utiliser plusieurs tables système SQL Server telles que sys.tables, sys.indexes, sys.partitions, sys.allocation_units et sys.schemas. Ces tables sont utiles pour afficher la liste des tables. Elles servent également à afficher le nombre de lignes dans chaque table et l’espace disque utilisé. Par exemple, cette requête affiche toutes les tables avec leur nombre de lignes triées par ordre décroissant. Cette requête est utile pour trouver la taille des plus grandes tables dans une base de données SQL Server.

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 tabSELECT
	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 par.rows DESC;

Cette liste de points explique la requête ci-dessus et comment elle est construite.

Le mot-clé « ORDER BY par.rows DESC; » est utilisé pour trier la liste des tables SQL Server par leur nombre de lignes, dans un ordre décroissant.
Pour trier par schéma puis par nom de table, utilisez « ORDER BY 1,2 » ou « ORDER BY sch.name, tab.name; ».
La taille des tables est notée en KiloOctets ou KO.

A propos des objets SQL Server et des tables système

Ce tutoriel technique sur MS SQL Server explique comment créer et utiliser une requête pour lister toutes les tables dans une base de données donnée. Si vous êtes encore nouveau sur SQL Server et souhaitez créer une table, voici comment créer une table SQL Server et insérer quelques lignes de données. Il est possible d’afficher une autre liste de tables MS SQL avec plus d’accent sur l’espace disque disponible.

https://expert-only.com/en/t-sql/sql-server-table-size-disk-space/