USE DWDATA GO IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ps_GetAllTableSizes' AND type = 'P') DROP PROCEDURE ps_GetAllTableSizes GO CREATE PROCEDURE ps_GetAllTableSizes AS /* Obtains spaced used data for ALL user tables in the database */ --For storing values in the cursor DECLARE @TableName VARCHAR(100) --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 --Create 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 off the cursor CLOSE tableCursor DEALLOCATE tableCursor --Select all records so we can use the reults -- Order by the biggest ones SELECT * FROM #TempTable ORDER BY LEN(dataSize) DESC, LEFT(datasize,1) DESC --Final cleanup! DROP TABLE #TempTable GO -- Execute the Store procedure for testing -- Once the store procedure has been implemented, you just need to run the code below to get the results EXEC ps_GetAllTableSizes GO
Sample Results:
