[SQL Server] Lister la taille de toutes les tables de toutes les base

/*

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;

Commentaires   

0 #1 Loretta 25-02-2018 03:58
Hi! Someone in my Myspace group shared this
site with us so I came to look it over. I'm definitely
loving the information. I'm bookmarking and will be tweeting this
to my followers! Superb blog and brilliant design and style.


Here is my blog ... money lender: https://cashmart.com.sg/
Citer

Ajouter un Commentaire


Code de sécurité
Rafraîchir