ZOUHAIER KHARROUBI

/*

Auteur : KHARROUBI Zouhaier

Date Création : 15/04/2009

Objectif : Lister la taille de toutes les tables de toutes les base d'un serveur

*/

CREATE TABLE #TableTemp(Data_Base NVARCHAR(128), Table_Name NVARCHAR(128), Shema_Name NVARCHAR(128),

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

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

DECLARE @Requete VARCHAR(max);

SET @Requete = '';

SELECT @Requete = @Requete + 'SELECT ''' + Data_Base +''', TABLE_NAME COLLATE French_BIN, TABLE_SCHEMA COLLATE French_BIN FROM '

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

FROM   #TableTemp;                    

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

INSERT INTO #TableTemp(Data_Base, Table_Name, Shema_Name)

EXEC (@Requete);

DECLARE @Data_Base NVARCHAR(128), @Table_Name NVARCHAR(128), @Shema_Name NVARCHAR(128);

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

DECLARE Curseur CURSOR

   LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

   SELECT Data_Base, Table_Name, Shema_Name FROM #TableTemp;

OPEN Curseur;

FETCH Curseur INTO @Data_Base, @Table_Name, @Shema_Name;

WHILE @@FETCH_STATUS = 0

BEGIN

   SET @Requete = 'EXEC ' + @Data_Base + '..sp_spaceused ''' + @Shema_Name +'.' + @Table_Name +'''';

   INSERT INTO @TT EXEC (@Requete);

   UPDATE #TableTemp

   SET   rows       = T.rows,

         reserved   = T.reserved,

         data       = T.data,

         index_size = T.index_size,

         unusued   = T.unusued

   FROM   @TT AS T

   WHERE Data_Base = @Data_Base

     AND Shema_Name = @Shema_Name

     AND Table_Name = @Table_Name;      

   DELETE FROM @TT;

   FETCH Curseur INTO @Data_Base, @Table_Name, @Shema_Name;

END;

CLOSE Curseur;

DEALLOCATE Curseur;

SELECT Data_Base AS DATA_BASE, Shema_Name , TABLE_NAME ,

       rows, reserved, data, index_size, unusued

FROM   #TableTemp;

DROP TABLE #TableTemp;