Voici un petit script pour lister la taille de toutes les tables de toutes les bases de votre serveur…

CREATE TABLE ##T  (DB NVARCHAR(128), T NVARCHAR(128), S NVARCHAR(128),  

                   rows bigint, reserved varchar(64), data varchar(64), index_size varchar(64), unusued varchar(64)) 

 

INSERT INTO ##T (DB) SELECT name FROM sys.databases WHERE name <> 'tempdb'; 

 

DECLARE @SQL VARCHAR(max); 

SET @SQL = ''; 

 

SELECT @SQL = @SQL + 'SELECT ''' + DB +''', TABLE_NAME COLLATE French_BIN, TABLE_SCHEMA COLLATE French_BIN FROM '  

                   + DB + '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' UNION ALL ' 

FROM   ##T;                    

 

SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 10); 

 

INSERT INTO ##T (DB, T, S) 

EXEC (@SQL); 

 

DECLARE @DB NVARCHAR(128), @T NVARCHAR(128), @S NVARCHAR(128); 

DECLARE @TT TABLE (name sysname, rows bigint, reserved varchar(64), data varchar(64), index_size varchar(64), unusued varchar(64)); 

DECLARE C CURSOR  

   LOCAL FORWARD_ONLY STATIC READ_ONLY  

FOR  

   SELECT DB, T, S FROM ##T; 

    

OPEN C; 

    

FETCH C INTO @DB, @T, @S; 

 

WHILE @@FETCH_STATUS = 0 

BEGIN 

   SET @SQL = 'EXEC ' + @DB + '..sp_spaceused ''' + @S +'.' + @T +''''; 

   INSERT INTO @TT EXEC (@SQL); 

   UPDATE ##T 

   SET    rows       = T.rows, 

          reserved   = T.reserved, 

          data       = T.data, 

          index_size = T.index_size, 

          unusued    = T.unusued 

   FROM   @TT AS T 

   WHERE  DB = @DB 

     AND  S = @S 

     AND  T = @T;        

   DELETE FROM @TT;  

   FETCH C INTO @DB, @T, @S; 

END; 

CLOSE C; 

DEALLOCATE C; 

 

SELECT DB AS DATA_BASE, S AS SCHEMA_NAME, T AS TABLE_NAME, 

       rows, reserved, data, index_size, unusued 

FROM   ##T; 

 

DROP TABLE ##T;

—
Frédéric BROUARD, Spécialiste modélisation, bases de données, optimisation, langage SQL.
Le site sur le langage SQL et les S.G.B.D. relationnels : http://sqlpro.developpez.com/
Expert SQL Server http://www.sqlspot.com : audit, optimisation, tuning, formation
* * * * * Enseignant au CNAM PACA et à l’ISEN à Toulon * * * * *