Vytux

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

CREATE PROCEDURE [dbo].[GetAllTableSizes]
AS
/*
    Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100)    --For storing values in the cursor
 
--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR 
SELECT [name]
FROM dbo.sysobjects 
WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY
 
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
    tableName VARCHAR(100),
    numberofRows VARCHAR(100),
    reservedSize VARCHAR(50),
    dataSize VARCHAR(50),
    indexSize VARCHAR(50),
    unusedSize VARCHAR(50)
)
 
--Open the cursor
OPEN tableCursor
 
--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
 
--Loop until the cursor was not able to fetch
WHILE (@@FETCH_STATUS >= 0)
BEGIN
    --Dump the results of the sp_spaceused query to the temp table
    INSERT  #TempTable
        EXEC SP_SPACEUSED @TableName
 
    --Get the next table name
    FETCH NEXT FROM tableCursor INTO @TableName
END
 
--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
 
--Select all records so we can use the reults
SELECT * 
FROM #TempTable ORDER BY CAST(numberofrows AS INT) DESC
 
--Final cleanup!
DROP TABLE #TempTable