Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ===== Explanation of SQL ===== This set of SQL queries will create a stored procedure that will generate a list of tables with their relevant sizes. An excel spreadsheet has been provided for additional formatting of the data. ===== Risk of Data Corruption if Run Improperly ===== **None**. This is a selection query and no data is modified in the running of it. ===== SQL ===== ==== Step 1: Create the Stored Procedure GetAllTableSizes ==== <code sql> CREATE PROCEDURE 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 GO </code> ==== Step 2: Execute the Stored Procedure GetAllTableSizes to get the table size results. ==== <code sql> EXEC GetAllTableSizes </code> ==== Step 3: Paste the query results into the first 5 columns of the excel spreadsheet below. The last 4 columns will calculate the table sizes in more usable numbers in megabytes. ==== Download Link: {{:control_-_sql_database_table_size_breakdown_excel_spreadsheet.xlsx|control_-_sql_database_table_size_breakdown_excel_spreadsheet.xlsx}} ===== Version Information ===== * Entered : 10/14/2009 * Version : 1.0