taille de toutes les tables dans une base

Question

Bonjour à vous,
Serait-il possible d'avoir la requete pour obtenir la taille de toutes les tables dans une base sous sql server 2005 ?

Merci à vous tous,

Cdt,

Richard

nhervagault · Answer

Salut

Voici un debut de solution

USE pubs
EXEC sp_spaceused 'titles'


La suite est peut etre dans ce post de christian
http://www.sqlfr.com/codes/SQL-SERVER-2005-RECHERCHE-CHAMPS-LEURS-NOMS-MULTI_42129.aspx


bon sql

Ruffal · Answer

Salut, 

Merci beaucoup pour ton aide mais malheuresement non :

Je cherche juste un scripts pour avoir la taille des tables (en mo, ko etc...) d'une base de donnée.

Merci,

Richard

cs_NonoNantes · Answer

Salut, C'est peut-être un peu tard, mais ce topic m'a aidé à trouver la réponse: Le sp_spacused, fait bien ce que je peux pour une table, et je voulais la même chose pour toutes les tables, alors j'ai regardé ce que faisait le sp_spaceused, je m'en suis inspiré et voilà ce que j'ai fait : (ca liste les tables utilisateurs de la base et donne le volume occupé par les données et par les indexs) select nomTable= object_name(p.object_id), lignes = sum( CASE When (p.index_id < 2) and (a.type = 1) Then p.rows Else 0 END ), 'mémoire (kb)' = ltrim(str(sum(a.total_pages)* 8192 / 1024.,15,0)) , 'données (kb)' = ltrim(str(sum( CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) * 8192 / 1024.,15,0)), 'indexs (kb)' = ltrim(str((sum(a.used_pages)-sum( CASE When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END) )* 8192 / 1024.,15,0)) from sys.partitions p, sys.allocation_units a ,sys.sysobjects s where p.partition_id = a.container_id and p.object_id s.id and s.type 'U' -- tables de type Utilisateur (exclusion des tables système group by p.object_id order by object_name(p.object_id) Voilà ! NN

nivsql · Answer

Je vous proposerais une approche légerement différentes pour optenir ce genre de résultats. 
Cette requete présente pour chaque table de chaque schéma la taille de chaque index en Kilo bytes ainsi que le nombres de lignes qu'ils contiennent (SQL 2005 permet la création d'index filtré contenant moins de ligne que la table). Si la table n'a pas d'index cluster la ligne HEAP donne la taille de l'espace "données" de la table, sinon cette espace est celui de l'index cluster. 
L'option WITH ROLLUP dans le group by permet d'afficher des totaux par Table, Schéma et bien sur le total final pour la base (le nombre de ligne "de la base" est peu pertinant). 

WITH

 table_space_usage 
--Création d'une CTE pour reccupérer les informations voulues(

schema_name, table_name
, index_name
, used
, reserved
, ind_rows
, tbl_rows
)as

(select

 s
.name
,o

.name
,

coalesce(i
.name
,
'HEAP'),
-- Les tables HEAP n'ont pas d'index Clusterp

.used_page_count 
* 8
,p

.reserved_page_count 
* 8
,p

.row_count
,

case
when i
.index_id 
in
(0
,1
)
then p
.row_count 
else 0 
endFROM

sys.dm_db_partition_stats
as p
INNER

JOIN
sys.objects
as o
on

 o
.object_id
= p
.object_idINNER

JOIN
sys.schemas
as s
on

 s
.schema_id
= o
.schema_idLEFT

OUTER
JOIN
sys.indexes
as i
on

 i
.object_id
= p
.object_id
and i
.index_id 
= p
.index_id
WHERE

 o
.type_desc 
=
'USER_TABLE'AND

 o
.is_ms_shipped 
= 0
)

-- puis intérrogation de la CTE avec mise en formeselect

 t
.schema_name, t
.table_name
,

coalesce(t
.index_name
,
'TOTAL')
as index_name
,

sum(t
.used
)
as used_in_kb
,

sum(t
.reserved
)
as reserved_in_kb
,

case
grouping(t
.index_name
)
when 0 
then
sum(t
.ind_rows
)
else
sum(t
.tbl_rows
)
end
as
rowsFROM

 table_space_usage 
as t
GROUP

BY t
.schema_name, t
.table_name
, t
.index_name
WITH

ROLLUP
-- Attention cette option est en cours de déprécation ! ORDER

BYgrouping

(t
.schema_name), t
.schema_name,grouping

(t
.table_name
), t
.table_name
,grouping

(t
.index_name
), t
.index_name

nivsql · Answer

En plus lisible (je me suis fais avoir pour mon premier poste). 



WITH
 table_space_usage --Création d'une CTE pour reccupérer les informations voulues<?xml:namespace prefix o ns "urn:schemas-microsoft-com:office:office" /??>





(

schema_name
, table_name, index_name, used, reserved, ind_rows, tbl_rows)





as

(






select
 s.name,






       o.name,






      
   
coalesce
(i.name,'HEAP'),-- Les tables HEAP n'ont pas d'index Cluster






       p.used_page_count * 8,






       p.reserved_page_count * 8,






       p.row_count,






       
case
when i.index_id in(0,1)then p.row_count else 0 end





FROM

sys.dm_db_partition_stats
as p





INNER

JOIN
sys.objects
as o





on
 o.object_id= p.object_id





INNER

JOIN
sys.schemas
as s





on
 s.schema_id= o.schema_id





LEFT

OUTER
JOIN
sys.indexes
as i





on
 i.object_idp.object_idand i.index_id p.index_id





WHERE
 o.type_desc ='USER_TABLE'





AND
 o.is_ms_shipped = 0





)

-- puis intérrogation de la CTE avec mise en forme






select
 t.schema_name, t.table_name,






       
coalesce
(t.index_name,'TOTAL')as index_name,






       
sum
(t.used)as used_in_kb,






       
sum
(t.reserved)as reserved_in_kb,






      
   
case
grouping
(t.index_name)when 0 thensum(t.ind_rows)elsesum(t.tbl_rows)endasrows





FROM
 table_space_usage as t





GROUP

BY t.schema_name, t.table_name, t.index_name





WITH

ROLLUP
-- Attention cette option est en cours de déprécation ! 






ORDER

BY






grouping
(
t.schema_name), t.schema_name,





grouping
(
t.table_name), t.table_name,





grouping
(
t.index_name), t.index_name

cs_NonoNantes · Answer

Bonjour,

Effectivement, ce script donne un résultat plus détaillé par index.
Mais je pense que la demande initiale de Ruffal était pas si poussée. En tout cas, pour mon besoin, c'est surtout la ligne TOTAL qui m'intéresse.
(Je note au passage que le détail des résultats comporte quelques octets de différence entre ce script et le mien, je ne sais pas lequel à tort ou raison, mais dans l'esprit ca permet de cibler les plus gourmands en espace.)

Merci.
NN

Taille de toutes les tables dans une base

6 réponses

Votre réponse

Discussions similaires