Discussion :

[agemis31]

Bonjour à tous,

Rui Carvalho à posté le 7ème challenge de la série des TSQL Challenges.

Attention, la date limite est le 25 mai !

Le challenge traduit en français est disponible ici et certainement bientôt sur rui.fr

Bon amusement

[lmalta]

Merci pour la traduction car je n'avais rien compris sous la version anglaise

Ma contribution

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
create TABLE #T (BASE sysname,[TABLE] sysname,SIZE int,[ROWS] INT) 
DECLARE @dataname as sysname,@strquery varchar(8000)
declare CursDatabase CURSOR FOR select name from sys.databases where name not in('master','tempdb','msdb','model')
open CursDatabase
fetch CursDatabase into @dataname
WHILE @@FETCH_STATUS = 0
BEGIN
select @strquery = 'use '+@dataname+' insert into #T select Top 5 '''+@dataname+''',a2.name,SUM(CASE WHEN(ps.index_id<2)THEN(ps.in_row_data_page_count+ps.lob_used_page_count+ps.row_overflow_used_page_count)ELSE(ps.lob_used_page_count+ps.row_overflow_used_page_count)END)*8,SUM(CASE WHEN (ps.index_id < 2)THEN row_count ELSE 0 END ) FROM sys.dm_db_partition_stats ps INNER JOIN sys.all_objects a2 ON(ps.object_id=a2.object_id)INNER JOIN sys.schemas a3 ON(a2.schema_id=a3.schema_id)WHERE a2.type<>N''S''and a2.type<>N''IT''GROUP BY ps.object_id,a2.name ORDER BY 3desc'
exec(@strquery)
fetch CursDatabase into @dataname
end
CLOSE CursDatabase
DEALLOCATE CursDatabase
select Top 5 BASE,[TABLE],convert(varchar,SIZE)+' KB'SIZE,[ROWS] from #T order by SIZE desc
drop table #T

@ +

[agemis31]

Merci Imalta,

Sous 2005 et 2008, il faudrait aussi exclure les bases ReportServer et ReportServerTempDB

@+

[lmalta]

Bonjour,

petite erreur dans ma requete je remonte des KO et non des KB il suffit donc d'enlever le multiplicateur par 8 pour avoir l'information demandée.
@+

[SQLpro]

Imalta, il me semble que ta solution est incorrecte, car elle doit fonctionner avec TOUTES les versions de SQL Server.
Or cela ne fonctionne pas avec 2000.
J'ai posé la question à Rui.

De plus tu as limité ta query à 8000 car. Si la base comporte 4000 tables... Cela ne passera pas !

Enfin tu as oublié le schéma SQL de la table.... Toutes les bases n'ont pas qu'un seul schéma et deux schémas SQL différents peuvent comporter une table de même nom !

A +

[SQLpro]

Voici d'ailleurs ma solution (inspirée de la tienne) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE ##T 
(BASE    sysname, 
 [TABLE] sysname, 
 SIZE    int, 
 [ROWS]  INT) 
 
DECLARE @DB AS sysname, 
        @Q varchar(max)
 
SET @Q = ''
 
SELECT @Q = @Q + 'USE ' + name +';INSERT ##T SELECT Top 5 ''' + name + ''', s.name + ''.'' + a.name, SUM(used_page_count) + SUM(reserved_page_count), row_count FROM sys.dm_db_partition_stats p INNER JOIN sys.all_objects a ON(p.object_id=a.object_id) INNER JOIN sys.schemas s ON (a.schema_id=s.schema_id) WHERE a.type<>''S''and a.type<>''IT'' GROUP BY p.object_id, s.name +''.'' +a.name, row_count ORDER BY 3 desc;'
FROM sys.databases 
WHERE name NOT IN ('master', 'tempdb', 'msdb', 'model')
 
EXEC(@Q)
 
SELECT Top 5 BASE, [TABLE], CAST(SIZE AS VARCHAR(99)) + ' KB' SIZE, [ROWS] 
FROM   ##T 
ORDER  BY SIZE desc

Et ce qu'elle donne sur mon PC :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
BASE         TABLE                            SIZE      ROWS
------------ -------------------------------- --------- -------
DB_GUID      dbo.T_ID2                        964 KB    50000
DB_SIG       Hotel.ChambrePlanningClient      95 KB     14562
DB_SEM       S_CIN.T_FILM_FLM                 8 KB      1
DB_SIG       Client.Client                    8 KB      101
DB_SIG       Hotel.ModePaiement               8 KB      3

A +