Differences
This shows you the differences between two versions of the page.
Last revision Both sides next 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:// | Download Link: [[http:// | ||
- | |||
- | |||
<code sql> | <code sql> | ||
Line 39: | Line 24: | ||
DECLARE tableCursor CURSOR | DECLARE tableCursor CURSOR | ||
FOR | FOR | ||
- | select | + | SELECT |
- | from dbo.sysobjects | + | FROM dbo.sysobjects |
- | where | + | WHERE |
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 | + | tableName |
- | numberofRows | + | numberofRows |
- | reservedSize | + | reservedSize |
- | dataSize | + | dataSize |
- | indexSize | + | indexSize |
- | unusedSize | + | unusedSize |
) | ) | ||
--Open the cursor | --Open the cursor | ||
Line 72: | Line 57: | ||
SELECT * | SELECT * | ||
FROM #TempTable | FROM #TempTable | ||
- | ORDER BY cast(numberofrows | + | ORDER BY CAST(numberofrows |
--Final cleanup! | --Final cleanup! | ||
DROP TABLE #TempTable | DROP TABLE #TempTable | ||
GO | GO | ||
</ | </ | ||
- | |||
- | |||
==== 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 | ||
</ | </ | ||
- | |||
- | |||
==== 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: {{: | ||
+ | ===== Version Information ===== | ||
- | Download Link: {{ : | ||
- | |||
- | |||
- | |||
- | ===== Version Information ===== | ||
* Entered : 10/14/2009 | * Entered : 10/14/2009 | ||
* Version : 1.0 | * Version : 1.0 | ||
- | |||
- | |||