1: CREATE PROCEDURE GetAllTableSizes
2: AS
3: /*
4: Obtains spaced used data for ALL user tables in the database
5: */
6: DECLARE @TableName VARCHAR(100) --For storing values in the cursor
7:
8: --Cursor to get the name of all user tables from the sysobjects listing
9: DECLARE tableCursor CURSOR
10: FOR
11: select [name]
12: from dbo.sysobjects
13: where OBJECTPROPERTY(id, N'IsUserTable') = 1
14: FOR READ ONLY
15:
16: --A procedure level temp table to store the results
17: CREATE TABLE #TempTable
18: (
19: tableName varchar(100),
20: numberofRows varchar(100),
21: reservedSize varchar(50),
22: dataSize varchar(50),
23: indexSize varchar(50),
24: unusedSize varchar(50)
25: )
26:
27: --Open the cursor
28: OPEN tableCursor
29:
30: --Get the first table name from the cursor
31: FETCH NEXT FROM tableCursor INTO @TableName
32:
33: --Loop until the cursor was not able to fetch
34: WHILE (@@Fetch_Status >= 0)
35: BEGIN
36: --Dump the results of the sp_spaceused query to the temp table
37: INSERT #TempTable
38: EXEC sp_spaceused @TableName
39:
40: --Get the next table name
41: FETCH NEXT FROM tableCursor INTO @TableName
42: END
43:
44: --Get rid of the cursor
45: CLOSE tableCursor
46: DEALLOCATE tableCursor
47:
48: --Select all records so we can use the reults
49: SELECT *
50: FROM #TempTable
51:
52: --Final cleanup!
53: DROP TABLE #TempTable
54:
55: GO