Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Next revision 		Previous revision
control_-_how_to_determine_the_size_of_each_sql_database_table [2019/01/27 11:28]
127.0.0.1 external edit 		control_-_how_to_determine_the_size_of_each_sql_database_table [2019/03/12 16:48]
admin
Line 1: Line 1:
-======  ====== +======   ======
- +
  
 ===== Explanation of SQL ===== ===== 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. 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 ===== ===== Risk of Data Corruption if Run Improperly =====
  
 **None**. This is a selection query and no data is modified in the running of it. **None**. This is a selection query and no data is modified in the running of it.
- 
- 
- 
 ===== SQL ===== ===== SQL =====
- 
- 
  
 ==== Step 1: Create the Stored Procedure GetAllTableSizes ==== ==== Step 1: Create the Stored Procedure GetAllTableSizes ====
- 
- 
- 
-Download Link: [[http://www.ecyrious.com/control/Files/Control%20-%20SQL%20Database%20Table%20Size%20Breakdown.sql|Control - SQL Database Table Size Breakdown.sql]] 
- 
  
  
Line 39: Line 23:
 DECLARE tableCursor CURSOR DECLARE tableCursor CURSOR
 FOR FOR
-select [name] +SELECT [name] 
-from dbo.sysobjects +FROM dbo.sysobjects 
-where  OBJECTPROPERTY(id, N'IsUserTable') = 1+WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
 FOR READ ONLY FOR READ ONLY
 --A procedure level temp table to store the results --A procedure level temp table to store the results
 CREATE TABLE #TempTable CREATE TABLE #TempTable
 ( (
-    tableName varchar(100), +    tableName VARCHAR(100), 
-    numberofRows varchar(100), +    numberofRows VARCHAR(100), 
-    reservedSize varchar(50), +    reservedSize VARCHAR(50), 
-    dataSize varchar(50), +    dataSize VARCHAR(50), 
-    indexSize varchar(50), +    indexSize VARCHAR(50), 
-    unusedSize varchar(50)+    unusedSize VARCHAR(50)
 ) )
 --Open the cursor --Open the cursor
Line 72: Line 56:
 SELECT * SELECT *
 FROM #TempTable FROM #TempTable
-ORDER BY cast(numberofrows as int) DESC+ORDER BY CAST(numberofrows AS INT) DESC
 --Final cleanup! --Final cleanup!
 DROP TABLE #TempTable DROP TABLE #TempTable
 GO GO
 </code> </code>
- 
- 
  
 ==== Step 2: Execute the Stored Procedure GetAllTableSizes to get the table size results. ==== ==== Step 2: Execute the Stored Procedure GetAllTableSizes to get the table size results. ====
- 
- 
  
 <code sql> <code sql>
 EXEC GetAllTableSizes EXEC GetAllTableSizes
 </code> </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. ==== ==== 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 =====
  
-Download Link: {{ :control_-_sql_database_table_size_breakdown_excel_spreadsheet.xlsx|}} 
- 
- 
- 
-===== Version Information ===== 
   * Entered : 10/14/2009   * Entered : 10/14/2009
   * Version : 1.0   * Version : 1.0
- 
-