How do I Get User Tables by SIZE?

The following code will display User Tables by SIZE in DESCENDING ORDER:

 

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
declare @id int
declare @type character(2)
declare @pages int
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
 
CREATE TABLE #spt_space
(
objid int NULL,
rows int NULL,
reserved dec(15) NULL,
DATA dec(15) NULL,
indexp dec(15) NULL,
unused dec(15) NULL
)
 
SET nocount ON
 
-- Create a cursor to loop through the user tables
declare c_tables cursor FOR
SELECT id
FROM sysobjects
WHERE xtype = 'U'
 
open c_tables
 
fetch next FROM c_tables
INTO @id
 
while @@fetch_status = 0
begin
 
/* Code from sp_spaceused */
INSERT INTO #spt_space (objid, reserved)
SELECT objid = @id, sum(reserved)
FROM sysindexes
WHERE indid IN (0, 1, 255)
AND id = @id
 
SELECT @pages = sum(dpages)
FROM sysindexes
WHERE indid < 2
AND id = @id
SELECT @pages = @pages + isnull(sum(used), 0)
FROM sysindexes
WHERE indid = 255
AND id = @id
UPDATE #spt_space
SET DATA = @pages
WHERE objid = @id
 
 
/* index: sum(used) where indid in (0, 1, 255) - data */
UPDATE #spt_space
SET indexp = (SELECT sum(used)
FROM sysindexes
WHERE indid IN (0, 1, 255)
AND id = @id)
- DATA
WHERE objid = @id
 
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
UPDATE #spt_space
SET unused = reserved
- (SELECT sum(used)
FROM sysindexes
WHERE indid IN (0, 1, 255)
AND id = @id)
WHERE objid = @id
 
UPDATE #spt_space
SET rows = i.rows
FROM sysindexes i
WHERE i.indid < 2
AND i.id = @id
AND objid = @id
 
fetch next FROM c_tables
INTO @id
end
 
SELECT TableName = (SELECT LEFT(name,60) FROM sysobjects WHERE id = objid),
Rows = convert(char(11), rows),
ReservedKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
DataKB = ltrim(str(DATA * d.low / 1024.,15,0) + ' ' + 'KB'),
IndexSizeKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
UnusedKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
 
FROM #spt_space, master.dbo.spt_values d
WHERE d.number = 1
AND d.type = 'E'
ORDER BY reserved DESC
DROP TABLE #spt_space
close c_tables
deallocate c_tables

 


 


Add Feedback