Requête SQL : taille des tables

**Teooy** · 31/12/2018, 16h58

Bonjour,

j'aimerais connaitre la taille des tables dans une base de données.

J'ai trouvé 2 requêtes qui donne le résultat:
- une avec la table sys.indexes, et spt_values
- l'autre avec la table sys.allocation_units

les voici:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM sys.tables t
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
GROUP BY t.Name, s.Name, p.Rows
ORDER BY UsedSpaceKB DESC

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
select 
TableName = convert(varchar(100),sysobjects.name)
    ,TotalRows = max(sysindexes.rows)
    ,MbData = (sum(convert(real,sysindexes.dpages)) * spt_values.low / 1048576)
    ,MbTotal = (sum(convert(real,sysindexes.used)) * spt_values.low / 1048576 )
from sysobjects 
join sysindexes on sysobjects.id = sysindexes.id
    join master.dbo.spt_values spt_values on spt_values.number = 1 and spt_values.type = 'E'
where sysobjects.type = 'U'
 
group by sysobjects.name, spt_values.low
order by 4 desc

le résultat n'est pas le même, laquelle donne le résultat le plus cohérent ?

Merci,

Teddy

**aieeeuuuuu** · 02/01/2019, 11h06

Bonjour,

La deuxième fait appel à sysindexes qui est dépréciée...

**Teooy** · 02/01/2019, 17h36

Bonjour,

Merci de ta réponse.

Pourtant celle - ci me donne le même résultat que dans l'explorateur d'objet Propriété de la table/stockage même si c'est déconseillé sysindexes.

Je partirai quand même sur la première mais je n'explique toujours pas la différence

.

**janlouk** · 02/01/2019, 17h52

Bonjour,

Ce n'est pas quelque chose que j'utilise très souvent, mais voici ce que j'ai pour la taille des tables. Je n'efface pas quand je trouve un script "meilleur", donc regarde ce qui te convient.

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
 
/****** Object:  StoredProcedure [dbo].[sp_SOS]    Script Date: 09/08/2016 15:15:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[sp_SOS] 
  @DbName sysname = NULL,  
  @SchemaName sysname = NULL,  
  @ObjectName sysname = N'%',  
  @TopClause nvarchar(20) = NULL,
  @ObjectType nvarchar(50) = NULL,  
  @ShowInternalTable nvarchar(3) = NULL, 
  @OrderBy nvarchar(100) = NULL,  
  @UpdateUsage bit = 0 
AS
 
/*=================================================================================================
 
Author:     Richard Ding
 
Created:    Mar. 03, 2008
 
Modified:   Mar. 17, 2008
 
Purpose:    Manipulate object size calculation and display for SS 2000/2005/2008
 
Parameters: 
  @DbName:            default is the current database
  @SchemaName:        default is null showing all schemas
  @ObjectName:        default is "%" including all objects in "LIKE" clause
  @TopClause:         default is null showing all objects. Can be "TOP N" or "TOP N PERCENT"
  @ObjectType:        default is "S", "U", "V", "SQ" and "IT". All objects that can be sized
  @ShowInternalTable: default is "Yes", when listing IT, the Parent excludes it in size 
  @OrderBy:           default is by object name, can be any size related column
  @UpdateUsage:       default is 0, meaning "do not run DBCC UPDATEUSAGE" 
 
Note:       SS 2000/2005/2008 portable using dynamic SQL to bypass validation error;
            Use ISNULL to allow prefilled default parameter values;
            Use "DBCC UPDATEUSAGE" with caution as it can hold up large databases;
            Unicode compatible and case insensitive; 
 
Sample codes:
 
   EXEC dbo.sp_SOS;
   EXEC dbo.sp_SOS 'AdventureWorks', NULL, '%', NULL, 'U', 'No', 'T', 1;
   sp_SOS 'TRACE', NULL, NULL, Null, '  ,,, ,;SQ,;  u  ;;;,,  v  ,,;iT     ,  ;', 'No', N'N', 0;
   sp_SOS NULL, NULL, NULL, NULL, 'U', 'Yes', N'U', 1;
   sp_SOS 'AdventureWorks', 'Person%', 'Contact%', NULL, 'U', 'no', 'N', 0;
   sp_SOS 'AdventureWorks', NULL, NULL, N'Top 100 Percent', 'S', 'yes', N'N', 1;
   sp_SOS 'AdventureWorks', NULL, 'xml_index_nodes_309576141_32000', NULL, 'IT', 'yes', 'N', 1;
   sp_SOS 'TRACE', NULL, 'Vw_DARS_217_overnight_activity_11142007', ' top 10 ', 'v', 'yes', 'N', 0;
   sp_SOS 'AdventureWorks', NULL, 'xml%', ' top 10 ', null, 'yes', 'N', 1;
   sp_SOS 'AdventureWorks2008', NULL, 'sales%', NULL, '  ,,;  u  ;;;,,  v  ', 'No', N'N', 1;
   sp_SOS NULL, NULL, NULL, N'Top 100 Percent', ' ;;Q, U;V,', N'Y', 1;
 
=================================================================================================*/
 
SET NOCOUNT ON;
 
--  Input parameter validity checking
DECLARE @SELECT nvarchar(2500), 
        @WHERE_Schema nvarchar(200),
        @WHERE_Object nvarchar(200), 
        @WHERE_Type nvarchar(200), 
        @WHERE_Final nvarchar(1000), 
        @ID int, 
        @Version nchar(2), 
        @String nvarchar(4000), 
        @Count bigint,
        @GroupBy nvarchar(450);
 
IF ISNULL(@OrderBy, N'N') NOT IN (N'', N'N', N'R', N'T', N'U', N'I', N'D', N'F', N'Y')
  BEGIN
    RAISERROR (N'Incorrect value for @OrderBy. Valid parameters are: 
      ''N''  -->  Listing by object name 
      ''R''  -->  Listing by number of records  
      ''T''  -->  Listing by total size 
      ''U''  -->  Listing by used portion (excluding free space) 
      ''I''  -->  Listing by index size 
      ''D''  -->  Listing by data size
      ''F''  -->  Listing by unused (free) space 
      ''Y''  -->  Listing by object type ',  16, 1)
    RETURN (-1)
  END;
 
--  Object Type Validation and Clean up
DECLARE @OTV nvarchar(10), @OTC nvarchar(10);
SELECT @OTV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType, 
              N'S, U, V, SQ, IT'), N' ', N''), N',', N''), N';', N''), N'SQ', N''), N'U', N''), 
              N'V', N''), N'IT', N''), N'S', N'');
IF LEN(@OTV) <> 0    --  only allow comma, semi colon and space around S,U,V,SQ,IT
  BEGIN
    RAISERROR (N'Parameter error. Choose ''S'', ''U'', ''V'', ''SQ'', ''IT'' or any combination of them, 
separated by space, comma or semicolon.
 
  S   ->   System table;
  U   ->   User table;
  V   ->   Indexed view;
  SQ  ->   Service Queue;
  IT  ->   Internal Table',  16, 1)
    RETURN (-1)
  END
ELSE    --  passed validation
  BEGIN
    SET @OTC = UPPER(REPLACE(REPLACE(REPLACE(ISNULL(@ObjectType,N'S,U,V,SQ,IT'),N' ',N''),N',',N''),N';',N''))
    SELECT @ObjectType = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(ISNULL
               (@ObjectType, N'S,U,V,SQ,IT'),N',',N''),N';',N''),N'SQ',N'''QQ'''),N'IT',N'''IT'''),N'S',
                             N'''S'''),N'U',N'''U'''),N'V',N'''V'''),N'QQ',N'SQ'),N' ',N''),N'''''',N''',''')
  END
 
----  common  ----
SELECT @DbName = ISNULL(@DbName, DB_NAME()), 
       @Version = SUBSTRING(CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')), 1, 
                    CHARINDEX(N'.', CONVERT(nchar(20), SERVERPROPERTY (N'ProductVersion')))-1),
       @OrderBy = N'ORDER BY [' + 
                    CASE ISNULL(@OrderBy, N'N') 
                      WHEN N'N' THEN N'Object Name] ASC ' 
                      WHEN N'R' THEN N'Rows] DESC, [Object Name] ASC '
                      WHEN N'T' THEN N'Total(MB)] DESC, [Object Name] ASC '
                      WHEN N'U' THEN N'Used(MB)] DESC, [Object Name] ASC '
                      WHEN N'I' THEN N'Index(MB)] DESC, [Object Name] ASC '
                      WHEN N'D' THEN N'Data(MB)] DESC, [Object Name] ASC ' 
                      WHEN N'F' THEN N'Unused(MB)] DESC, [Object Name] ASC '
                      WHEN N'Y' THEN N'Type] ASC, [Object Name] ASC ' 
                    END;
 
----------------------  SS 2000  -----------------------------------
IF @Version = N'8'
  BEGIN
    SELECT @SELECT = N'USE ' + @DbName + N' SELECT ' + ISNULL(@TopClause, N' ') +   
    N''''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''' AS ''Object Name'',
    o.type AS ''Type'',
    MAX(i.[rows]) AS ''Rows'',
    CONVERT(dec(10,3), SUM(i.reserved * 8.000/1024)) AS ''Total(MB)'', 
    CONVERT(dec(10,3), SUM((i.reserved - i.used) * 8.000/1024)) AS ''Unused(MB)'',
    CONVERT(dec(10,3), SUM(i.used * 8.000/1024)) AS ''Used(MB)'',
    CONVERT(dec(10,3), SUM((i.used - CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END)
      * 8.000/1024)) AS ''Index(MB)'',
    CONVERT(dec(10,3), SUM(CASE WHEN indid <> 255 THEN i.dpages ELSE i.used END 
      * 8.000/1024)) AS ''Data(MB)''
    FROM dbo.sysindexes i WITH (NOLOCK) 
    JOIN dbo.sysobjects o WITH (NOLOCK) 
    ON i.id = o.id 
    WHERE i.name NOT LIKE ''_WA_Sys_%'' 
    AND i.indid IN (0, 1, 255) AND USER_NAME(o.uid) LIKE ''' + ISNULL(@SchemaName, N'%') + N''' ',
    -- SS 2000 calculation as below:
    --  "reserved" = total size;
    --  "dpages" = data used;
    --  "used" = used portion (contains data and index);
    --  text or image column: use "used" for data size 
    --  Nonclustered index take tiny space, somehow it is not counted (see sp_spaceused).
    @WHERE_Final = N' AND OBJECT_NAME(i.id) LIKE ''' + ISNULL(@ObjectName, N'%') 
                 + N''' AND o.type IN (' + @ObjectType + N') ',
    @GroupBy = N' GROUP BY '''' + USER_NAME(o.uid) + ''.'' + OBJECT_NAME(i.id) + '''', o.type ',
    @String =  @SELECT + @WHERE_Final + @GroupBy + @OrderBy
  END
 
-------------------  ss 2k5 ------------------------------------------------------
IF @Version IN (N'9', N'10')
  BEGIN
    SELECT @String = N' 
IF OBJECT_ID (''tempdb.dbo.##BO'', ''U'') IS NOT NULL
  DROP TABLE dbo.##BO 
 
CREATE TABLE dbo.##BO (
  ID int identity,
  DOI bigint null,        -- Daughter Object Id
  DON sysname null,       -- Daughter Object Name
  DSI int null,           -- Daughter Schema Id
  DSN sysname null,       -- Daughter Schema Name
  DOT varchar(10) null,   -- Daughter Object Type
  DFN sysname null,       -- Daughter Full Name
  POI bigint null,        -- Parent Object Id
  PON sysname null,       -- Parent Object Name
  PSI bigint null,        -- Parent Schema Id
  PSN sysname null,       -- Parent Schema Name
  POT varchar(10) null,   -- Parent Object Type
  PFN sysname null        -- Parent Full Name
) 
 
INSERT INTO dbo.##BO (DOI, DSI, DOT, POI)
  SELECT object_id, schema_id, type, Parent_object_id 
FROM ' + @DbName + N'.sys.objects o WHERE type IN (''S'',''U'',''V'',''SQ'',''IT'') 
USE ' + @DbName + N' 
UPDATE dbo.##BO SET DON = object_name(DOI), DSN = schema_name(DSI), POI = CASE POI WHEN 0 THEN DOI ELSE POI END
UPDATE dbo.##BO SET PSI = o.schema_id, POT = o.type FROM sys.objects o JOIN dbo.##BO t ON o.object_id = t.POI
UPDATE dbo.##BO SET PON = object_name(POI), PSN = schema_name(PSI), DFN = DSN + ''.'' + DON, 
                    PFN = schema_name(PSI)+ ''.'' + object_name(POI)
'
EXEC (@String)
 
SELECT 
@WHERE_Type = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N't.DOT ' ELSE N't.POT ' END,  
@SELECT = N'USE ' + @DbName + N' 
  SELECT ' + ISNULL(@TopClause, N'TOP 100 PERCENT ') + 
      N' CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN THEN t.PFN 
          ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END AS ''Object Name'', 
         ' + @WHERE_Type + N' AS ''Type'',
         SUM (CASE WHEN ''' + isnull(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN 
           CASE WHEN (ps.index_id < 2 ) THEN ps.row_count ELSE 0 END
             ELSE CASE WHEN (ps.index_id < 2 and t.DON = t.PON) THEN ps.row_count ELSE 0 END END) AS ''Rows'',
         SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' 
                THEN ps.reserved_page_count ELSE 0 END)* 8.000/1024 AS ''Total(MB)'',
         SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' 
                THEN ps.reserved_page_count ELSE 0 END 
              - CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' THEN 
                  ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Unused(MB)'',
	     SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' 
                THEN ps.used_page_count ELSE 0 END)* 8.000/1024 AS ''Used(MB)'',
         SUM (CASE WHEN t.DON NOT LIKE ''fulltext%'' OR t.DON LIKE ''fulltext_index_map%'' 
                THEN ps.used_page_count ELSE 0 END
              - CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes')
                + N''' = ''No'' THEN 0 ELSE 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 END) * 8.000/1024 AS ''Index(MB)'',
	     SUM (CASE WHEN t.POT NOT IN (''SQ'',''IT'') AND t.DOT IN (''IT'') and ''' + isnull(@ShowInternalTable, N'Yes') 
	            + N''' = ''No'' THEN 0 ELSE 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 END) * 8.000/1024 AS ''Data(MB)''
    FROM sys.dm_db_partition_stats ps INNER JOIN dbo.##BO t
      ON ps.object_id = t.DOI 
',
@ObjectType = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N'''IT'',' + ISNULL(@ObjectType, N'''S'',''U'', 
                ''V'', ''SQ'', ''IT''') ELSE ISNULL(@ObjectType, N'''S'', ''U'', ''V'', ''SQ'', ''IT''') END,
@WHERE_Schema = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DSN ' ELSE N' t.PSN ' END, -- DSN or PSN
@WHERE_Object = CASE WHEN ISNULL(@ShowInternalTable, N'Yes') = N'Yes' THEN N' t.DON LIKE ''' + ISNULL(@ObjectName, N'%')
                + ''' OR t.PON LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' 
                ELSE N' t.pon LIKE ''' + ISNULL(@ObjectName, N'%') + N''' ' END,      -- DON or PON
@WHERE_Final = N' WHERE (' + @WHERE_Schema + N' LIKE ''' + ISNULL(@SchemaName, N'%') + N''' OR ' + @WHERE_Schema + 
               N' = ''sys'') AND (' + @WHERE_Object + N' ) AND ' + @WHERE_Type + N' IN (' + @ObjectType + N') ',
@GroupBy = N'GROUP BY CASE WHEN ''' + ISNULL(@ShowInternalTable, N'Yes') + N''' = ''Yes'' THEN CASE t.DFN WHEN t.PFN 
            THEN t.PFN ELSE t.DFN + '' (''+ t.PFN + '')'' END ELSE t.PFN END, ' + @WHERE_Type + N''
SELECT @String =  @SELECT + @WHERE_Final + @GroupBy + @OrderBy
 -- SELECT @String AS 'STRING'
END
 
-----  common  ------
IF OBJECT_ID(N'tempdb.dbo.##FO', N'U') IS NOT NULL
  DROP TABLE dbo.##FO;
 
CREATE TABLE dbo.##FO (
    ID int identity, 
    [Object Name] sysname, 
    [Type] varchar(2),
    [Rows] bigint, 
    [Total(MB)] dec(10,3), 
    [-] nchar(1), 
    [Unused(MB)] dec(10,3), 
    [==] nchar(2), 
    [Used(MB)] dec(10,3), 
    [=] nchar(1), 
    [Index(MB)] dec(10,3), 
    [+] nchar(1), 
    [Data(MB)] dec(10,3) );
 
INSERT INTO dbo.##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],[Used(MB)],[Index(MB)],[Data(MB)])
  EXEC (@String);
 
SELECT @Count = COUNT(*) FROM dbo.##FO;
 
IF @Count = 0
  BEGIN
    RAISERROR (N'No records were found macthcing your criteria.',  16, 1)
    RETURN (-1)
  END
ELSE    -- There're at least one records
  BEGIN
    --  Run DBCC UPDATEUSAGE to correct wrong values 
    IF ISNULL(@UpdateUsage, 0) = 1 
      BEGIN
        SELECT @ObjectName = N'', @ID = 0 
          WHILE 1 = 1
		        BEGIN
		          SELECT TOP 1 @ObjectName = CASE WHEN [Object Name] LIKE N'%(%' THEN 
                     SUBSTRING([Object Name], 1, CHARINDEX(N'(', [Object Name])-2) ELSE [Object Name] END
                      , @ID = ID FROM dbo.##FO WHERE ID > @ID ORDER BY ID ASC
		          IF @@ROWCOUNT = 0
		            BREAK
              PRINT N'==> DBCC UPDATEUSAGE (' + @DbName + N', ''' + @ObjectName + N''') WITH COUNT_ROWS' 
			        DBCC UPDATEUSAGE (@DbName, @ObjectName) WITH COUNT_ROWS
              PRINT N''
		        END
 
          PRINT N''
        TRUNCATE TABLE dbo.##FO
        INSERT INTO dbo.##FO ([Object Name], [Type], [Rows], [Total(MB)],[Unused(MB)],
                              [Used(MB)],[Index(MB)],[Data(MB)]) EXEC (@String)
      END
    ELSE
      PRINT N'(Warning: Run "DBCC UPDATEUSAGE" on suspicious objects. It may incur overhead on big databases.)'
    PRINT N''
 
    UPDATE dbo.##FO SET [-] = N'-', [==] = N'==', [=] = N'=', [+] = N'+'
 
    IF @Count = 1  -- when only 1 row, no need to sum up total
      SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
             [Index(MB)],[+],[Data(MB)] 
      FROM dbo.##FO ORDER BY ID ASC 
    ELSE
      BEGIN
        SELECT [Object Name], [Type], [Rows], [Total(MB)],[-], [Unused(MB)],[==], [Used(MB)],[=],
               [Index(MB)],[+],[Data(MB)] 
          FROM dbo.##FO ORDER BY ID ASC 
       COMPUTE SUM([Total(MB)]), SUM([Unused(MB)]), SUM([Used(MB)]), SUM([Index(MB)]), SUM([Data(MB)])
      END
  END
 
RETURN (0)

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
21
22
23
24
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
        SUM(a.total_pages) * 8 AS TotalSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    p.rows DESC

Et ci-dessous, vérifier si mon commentaire est encore d'actualité car j'ai souvenir que je m'étais cassé la tête avec :

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
-- ATTENTION, ici cela ne donne que la taille de la table sans les indexes. Quand on fait un clic droit sur une table/properties/storage.
-- Le data storage n'inclut pas la taille des indexes.
CREATE TABLE #t 
( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), 
data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))
GO
INSERT #t
EXEC [sys].[sp_MSforeachtable] 'EXEC sp_spaceused "?"'
GO
SELECT
name as TableName,
Rows,
ROUND(CAST(REPLACE(reserved, ' KB', '') as float) / 1024,2) as 'ReservedMB (Data+Index)',
ROUND(CAST(REPLACE(data, ' KB', '') as float) / 1024,2) as DataMB,
ROUND(CAST(REPLACE(index_size, ' KB', '') as float) / 1024,2) as IndexMB,
ROUND(CAST(REPLACE(unused, ' KB', '') as float) / 1024,2) as UnusedMB
FROM #t
ORDER BY CAST(REPLACE(reserved, ' KB','') as float) DESC
GO
Drop table #t

Invité · 02/01/2019, 18h37

J'utilise ce machin là, qui mériterait sûrement d'être améliorer... Un jour...

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
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- surveillance des bases de données : donne le nombre d'enregistrements par tables
DECLARE @TableCount TABLE ( table_schema VARCHAR(250), table_name VARCHAR(250), card int, ReservedSize_ko int, DataSize_ko int, IndexSize_ko int, UnusedSize_ko int
	PRIMARY KEY ( table_schema , table_name ) )
CREATE TABLE #spaceused_tab ( tableName VARCHAR(100), numberofRows VARCHAR(100), reservedSize VARCHAR(50), dataSize VARCHAR(50), indexSize VARCHAR(50), unusedSize VARCHAR(50) )
 
DECLARE @sql2 NVARCHAR(1500), @table_schema VARCHAR(255), @table_name VARCHAR(255)
DECLARE db_cursor CURSOR LOCAL FOR 
	SELECT 'INSERT  #spaceused_tab EXEC sp_spaceused ''' + QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) + '''' AS REQ2 , table_schema, table_name
	from INFORMATION_SCHEMA.TABLES 
	where table_type = 'BASE TABLE' --and TABLE_NAME = 'archive_fix_charges'
	FOR READ ONLY
 
	OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @sql2, @table_schema, @table_name
WHILE @@FETCH_STATUS = 0 
BEGIN 
	DELETE #spaceused_tab
	EXEC sp_executesql @sql2
	INSERT @TableCount 
	SELECT @table_schema, @table_name , numberofRows, REVERSE(SUBSTRING(REVERSE(reservedSize), 4, 999)), REVERSE(SUBSTRING(REVERSE(dataSize), 4, 999)),
		REVERSE(SUBSTRING(REVERSE(indexSize), 4, 999)), REVERSE(SUBSTRING(REVERSE(unusedSize), 4, 999)) FROM #spaceused_tab
	FETCH NEXT FROM db_cursor INTO @sql2, @table_schema, @table_name
END 
CLOSE db_cursor 
DEALLOCATE db_cursor
 
; WITH SR_col as (SELECT COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME, COUNT(*) nb_colonnes FROM INFORMATION_SCHEMA.COLUMNS AS COL WITH (NOLOCK) GROUP BY COL.TABLE_CATALOG, COL.TABLE_SCHEMA, COL.TABLE_NAME
) , SR_Ind AS (	SELECT OBJECT_NAME(IND.object_id) name , OBJECT_SCHEMA_NAME(IND.object_id) schema_name , IND.object_id, COUNT(*) nb_indexes from sys.indexes AS IND WITH (NOLOCK) WHERE IND.index_id > 0 GROUP BY IND.object_id )
SELECT TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME , MAX(SR_col.nb_colonnes) nb_colonnes
	, MAX(CASE WHEN SR_Ind.nb_indexes IS NULL THEN 0 ELSE SR_Ind.nb_indexes END) nb_indexes
	, SUM(CASE WHEN CNT.CONSTRAINT_TYPE = 'PRIMARY KEY' then 1 else 0 end) PK
	, SUM(CASE WHEN CNT.CONSTRAINT_TYPE = 'UNIQUE' then 1 else 0 end) UK
	, SUM(CASE WHEN CNT.CONSTRAINT_TYPE = 'FOREIGN KEY' then 1 else 0 end) FK
	, SUM(CASE WHEN CNT.CONSTRAINT_TYPE = 'CHECK' then 1 else 0 end) CHK
	, MAX(TBC.card) nb_lignes
	, CAST(MAX(TBC.ReservedSize_ko)/1024. AS DECIMAL(12,3)) ReservedSize_Mo
	, CAST(MAX(TBC.DataSize_ko)/1024. AS DECIMAL(12,3)) DataSize_Mo
	, CAST(MAX(TBC.IndexSize_ko)/1024. AS DECIMAL(12,3)) IndexSize_Mo
	, CAST(MAX(TBC.UnusedSize_ko)/1024. AS DECIMAL(12,3)) UnusedSize_Mo
FROM INFORMATION_SCHEMA.TABLES TAB WITH (NOLOCK)
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CNT WITH (NOLOCK) ON CNT.CONSTRAINT_CATALOG = TAB.TABLE_CATALOG AND CNT.CONSTRAINT_SCHEMA = TAB.TABLE_SCHEMA AND CNT.TABLE_NAME = TAB.TABLE_NAME
LEFT OUTER JOIN SR_col WITH (NOLOCK) ON SR_col.TABLE_CATALOG = TAB.TABLE_CATALOG AND SR_col.TABLE_SCHEMA = TAB.TABLE_SCHEMA AND SR_col.TABLE_NAME = TAB.TABLE_NAME
LEFT OUTER JOIN SR_Ind WITH (NOLOCK) ON SR_Ind.schema_name = TAB.TABLE_SCHEMA AND SR_Ind.name = TAB.TABLE_NAME
LEFT OUTER JOIN @TableCount AS TBC ON TBC.table_schema = TAB.TABLE_SCHEMA COLLATE DATABASE_DEFAULT AND TBC.table_name = TAB.TABLE_NAME COLLATE DATABASE_DEFAULT
WHERE TAB.table_type='BASE TABLE' AND TAB.TABLE_NAME NOT IN ('dtproperties','sysdiagrams')
GROUP BY TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
ORDER BY ReservedSize_Mo DESC, TAB.TABLE_CATALOG, TAB.TABLE_SCHEMA, TAB.TABLE_NAME
DROP TABLE #spaceused_tab  
GO

Requête SQL : taille des tables

Administration SQL Server

Discussions similaires

Partager

Partager