Comment afficher la liste des toutes les tables utilisateurs d’une base SQL Server et leur taille avec du code T-SQL ?
Afficher la taille des tables SQL Server et l’espace disque utilisé physiquement est pratique pour analyser l’utilisation de chaque table. Trois façons différentes, mais similaires sont présentées ici. Le but, afficher les tables SQL Server et l’espace disque utilisé par chaque table. La troisième solution montre également le nombre de lignes pour chaque table.
Table des matières
1. Afficher le nom de toutes les tables SQL Server et leur taille
Consulter ces requêtes SQL Server pour afficher la liste des tables SQL Server et leur taille de différentes manières. Cette première requête utilise différentes tables système liées avec une jointure, à savoir sys.tables, sys.indexes, sys.partitions, sys.allocation_units et sys.schemas.
Elle affiche le nom du schéma, le nom de la table, le nombre de lignes et l’espace disque. Soit l’espace total, utilisé et inutilisé pour chaque table d’une base SQL Server.
SELECT
sch.name as SchemaName,
tab.name as TableName,
par.rows as RowCounts,
sum(alc.total_pages) * 8 as TotalSpace,
sum(alc.used_pages) * 8 as UsedSpace,
(sum(alc.total_pages) - sum(alc.used_pages)) * 8 as UnusedSpace
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 1,2;
Pour aller plus loin, l’objectif est généralement de vérifier et de libérer de l’espace disque sur le serveur hébergeant la base de données. Typiquement, lorsque le disque dur du serveur commence à être plein. Alors cette requête affiche la dernière fois qu’une table a été accédée ou mise à jour.
C’est très utile avant la suppression. Par exemple, si une table n’a pas été consultée au cours des trois dernières années, elle n’est peut-être plus nécessaire.
2. Gérer l’espace disque des bases SQL Server
En effet, toute base de données grandit, parce que le journal grandit, les données grandissent, le fichier journal grandit, le fichier de sauvegarde grandit, etc. Pour remédier à cela il est obligatoire de planifier des scripts de maintenance pour nettoyer les tables de log et supprimer régulièrement les données obsolètes, comme par exemple une période de cinq années glissantes pour rester en ligne.
Pour des raisons de conformité légale, selon le domaine d’activité, il peut être nécessaire de conserver et de sauvegarder les données plus longtemps.
3. Lister la taille des tables SQL Server avec les vues système
Cette approche consiste à utiliser les données stockées dans le schéma information_schema concernant les tables. La requête Microsoft SQL utilise la table système information_schema.tables pour afficher tous les attributs et métadonnées de la base de données système.
Elle affiche les quatre colonnes disponibles dans la vue système des tables :
- TABLE_CATALOG est le nom de la base de données.
- TABLE_SCHEMA est le schéma hébergeant la table, par défaut il s’agit du propriétaire de la base de données (dbo).
- TABLE_NAME est le nom de la table.
- TABLE_TYPE est le type de la table.
Notez que nous filtrons la requête sur la vue pour afficher uniquement le type de table de base. D’autres types de tables peuvent également être affichés.
SELECT
TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
TABLE_TYPE
FROM information_schema.tables
WHERE table_type='base table';
4. Lister toutes les tables MS SQL depuis sys.tables
La dernière requête utilise la table système sys.tables, elle est certainement la source de la vue système utilisée dans la première requête. Elle montre beaucoup plus d’informations et de métadonnées. Comme les données de création, la date de modification, et l’object_id réutilisable dans d’autres fonctions et procédures du système.
-- Utiliser la table système sys.tables SELECT * FROM sys.tables WHERE [type_desc] = 'USER_TABLE';
Conclusion
En conclusion, cet article de blog présente plusieurs méthodes pour afficher la liste de toutes les tables utilisateurs d’une base SQL Server et leur taille. L’affichage de la taille des tables et de l’espace disque utilisé est utile pour analyser l’utilisation de chaque table, ce qui peut aider à optimiser l’espace disque et améliorer les performances de la base de données.
Les requêtes SQL présentées dans l’article permettent d’obtenir des informations sur les tables en utilisant différentes vues système et tables système de SQL Server. En utilisant ces requêtes, les administrateurs de base de données peuvent mieux comprendre l’utilisation de leur base de données et prendre des mesures pour l’optimiser.
Pour aller plus loin dans le sujet de la maintenance des bases de données SQL Server, consultez ce court tutoriel pour libérer de l’espace disque avec un script SQL Shrink Database.
