TSQL Challenge 7

Version imprimable

17/05/2009, 02h20
agemis31

TSQL Challenge 7

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 :P

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

Ma contribution

Code:

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

@ +

18/05/2009, 07h38
agemis31

Merci Imalta,

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

@+
18/05/2009, 09h37
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.
@+
18/05/2009, 14h30
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 +

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

Code:

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:

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 +

Une nouvelle version car les précédentes ne fonctionnait pas sous SQL7 (ben voilà comment passer le temps à l'heure du déjeuné)

Celle là a été vérifié sur SQL7 , SQL2000 , SQL2005
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 set nocount on create table ##T (Be sysname,e sysname,o sysname,rows int,data int) DECLARE @A varchar(500),@N varchar(500),@nomoer varchar(500),@RC int,@Q varchar(8000) DECLARE B CURSOR FOR select name from master.dbo.sysdataBases OPEN B FETCH NEXT FROM B INTO @N WHILE @@FETCH_STATUS = 0 BEGIN select @Q = 'insert into ##T select y.Be, y.e , y.o,y.rows , str(cast((y.pages+y.pages2)as float)* d.low / 1024.,15,0) data from ( select z.rows ,pages,isnull(sum(i1.used), 0) pages2,z.Be, z.e , z.o,z.oid from (select i.rows ,sum(dpages)pages,Be, e , o,t.oid from (select '''+@N+''' Be,a.name e,b.NAME o, a.id oid from ['+@N+'].dbo.sysobjects a, ['+@N+'].dbo.sysusers b WHERE a.uid = b.uid and xtype=''U'' ) t left join ['+@N+'].dbo.sysindexes i on i.indid < 2 and i.id = t.oid where Be = '''+@N+''' group by Be, e , o,i.rows,t.oid )z left join ['+@N+'].dbo.sysindexes i1 on i1.indid = 255 and i1.id = z.oid where z.Be = '''+@N+''' group by z.Be, z.e , z.o,z.rows,z.pages,z.oid )y , master.dbo.spt_values d where d.number = 1 and d.type = ''E''' exec (@Q) FETCH NEXT FROM B INTO @N END CLOSE B DEALLOCATE B set rowcount 5 select Be BASE, o+'.'+e [TABLE], CAST(data AS VARCHAR(99)) + ' KB' SIZE, [ROWS] from ##T order by data desc drop table ##T set rowcount 0 set nocount off
Par contre je n'ai pas saisi la remarque de SQLPro concernant la limite de la variable de @strquery varchar(8000) dans l'ancienne version et @Q varchar(8000) dans cette derniére
vu que j'utilise un curseur (je sais ce n'est pas géniale) le seul facteur de croissance de la taille de la chaine ne peut être que de taille(base)+taille(schéma), je ne cumul pas des concaténation de requete. (qu'est ce qui m'échappe ?)

En contrepartie j'ai inclus la remarque sur le schéma
A +

19/05/2009, 13h37
agemis31

Salut Imalta,

Waoouh ! Pas facile à lire.

Très interessant ton truc pour obtenir la taille d'une page à partir de spt_values.

Par contre, chez moi, ton script ne filtre pas les bases de données systèmes.

@+
19/05/2009, 14h24
lmalta

Salut,

Woui les tables systèmes ne sont volontairement pas filtrer
car ce n'est pas préciser dans le challenge et ce n'est pas improbable qu'un user en creer une dedans.
A +
19/05/2009, 21h06
agemis31

C'est vrai que ca peut arriver, j'aurai du écrire "ne filtre pas les tables des bases de données systèmes".

Néanmoins, chez moi, j'ai master et msdb dans le résultat. Dans ton premier script tu avais fait

Code:

FROM sys.DATABASES WHERE name NOT IN('master','tempdb','msdb','model')

pour ne travailler que sur les bases de données utilisateur, comme précisé dans le challenge.

Au sujet des versions cibles, il y a avait une confusion possible. due aux mots any et all en anglais. Ca doit marcher sur au moins une version de SQL serveur, pas sur toutes, en privilégiant 2005 et 2008.
Ca doit fonctionner sur une version "stock" (pour éviter l'utilisation d'outils exotiques).

Bien sur si cela fonctionne aussi sous 2005, 2008 et 2000 (et en plus 7 dans ton cas :P) c'est super, mais c'est n'est pas le but. Le but est d'obtenir le code correct le plus court possible.

Bon appétit puisque que tu n'as pas du manger beaucoup à midi !

@+

Allez, un peu de fun avec powershell ! Même si ca sort un peu du cadre des conditions strictes du challenge.

Code:

1
2
3
4
5
6
7
8
$SqlServerIncludeSystemObjects=$False 
dir `
|where{'ReportServer','ReportServerTempDB' -notcontains $_.Name} `
|foreach{$_.Tables|select Parent,Name,RowCount,DataSpaceUsed} `
|sort -p DataSpaceUsed -desc `
|select -F 5 `
|ft @{N='BASE';E={$_.Parent}},@{N='TABLE';E={$_.name}}, `
@{N='SIZE';E={'{0} KB' -f $_.DataSpaceUsed}},@{N='ROWS';E={$_.RowCount}}