IBM Support

How to find out the size of each table inside a SQL database

Troubleshooting


Problem

Customer/consultant would like to find out how large each of their Controller database tables (for example 'xdb18') are. How can they do this?

Symptom

Customer is suffering from a problem (for example slow performance) where it is possible that the issue is being caused by a large size of a specific table.

Environment

The script (inside this Technote) has been tested on all modern versions of Microsoft SQL (including 2000, 2005, 2008, 2014, 2016 & SQL 2019).

  • TIP: For similar instructions when using DB2, see separate IBM Technote #788433.

Resolving The Problem

Run the attached SQL script ("Tablesize.sql") on your database. This provides:

  • Number of records
  • Table Size
  • Data Space Used
  • Index Space Used
  • Unused Space

 
Steps:
1. Logon to the SQL server as an administrator
2. Launch SQL Server Management Studio
3. Right-click on the Controller database, and choose 'New Query'
4. Paste the script (below) into the screen on the right-hand side

SET NOCOUNT ON
/* 0 = Alphabetically BY TABLE name
1 = Sorted BY total space used by TABLE
*/
DECLARE @strSQL varchar(100)
DECLARE @bitSort Bit
DECLARE @vchSortString varchar(50)

-- Edit this value FOR sorting options 0=Alphabetically and 1 = Table Size Descending
SELECT @bitSort = 1

-- Create Temporary Table
CREATE TABLE #TempTable (
vchTableName varchar(100),
biRowCount bigInt,
vchTableSize varchar(50),
vchDataSpaceUsed varchar(50),
vchIndexSpaceUsed varchar(50),
vchUnusedSpace varchar(50))

-- Create SQL Statement to run Stored Procedure String
SELECT @strSQL = 'sp_msforeachtable ''sp_spaceused "?"'''

-- Populate Temporary Table
INSERT INTO #TempTable EXEC(@strSQL)

-- Run different queries depending on sorting methed selected

IF @bitSort = 0 -- Sort by vchTableName Ascending
SELECT vchTableName, biRowCount,
CASE WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchTableSize END AS vchTableSize,
CASE WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchDatASpaceUsed END AS vchDatASpaceUsed,
CASE WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchIndexSpaceUsed END AS vchIndexSpaceUsed,
CASE WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchUnusedSpace END AS vchUnusedSpace
FROM #TempTable
ORDER BY vchTableName

ELSE -- Sort by Total Table Size Descending
SELECT vchTableName, biRowCount,
CASE WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchTableSize,CHARINDEX(' ',vchTableSize)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchTableSize END AS vchTableSize,
CASE WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchDatASpaceUsed,CHARINDEX(' ',vchDatASpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchDatASpaceUsed END AS vchDatASpaceUsed,
CASE WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchIndexSpaceUsed,CHARINDEX(' ',vchIndexSpaceUsed)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchIndexSpaceUsed END AS vchIndexSpaceUsed,
CASE WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) BETWEEN 1000 AND 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000 AS varchar(5)) + ' MB'
WHEN CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt) > 999999 THEN CAST(CAST(LEFT(vchUnusedSpace,CHARINDEX(' ',vchUnusedSpace)) AS bigInt)/1000000 AS varchar(5)) + ' GB'
ELSE vchUnusedSpace END AS vchUnusedSpace
FROM #TempTable
ORDER BY cast(left(vchTableSize,charindex(' ',vchTableSize)) AS bigInt) DESC

-- Delete Temp Table
DROP TABLE #TempTable

   

6. Press 'Execute'.


7. The output will look similar to:

8. Optionally, you can now export this information into either Excel or Notepad:

  • Left mouse-click on the top-left (empty/blank) cell in the 'results' (which will make all the cells highlight in blue):
image 5425
  • Right-click on any of the blue (highlighted) cells, and choose "Copy" (or "Copy with Headers" if you prefer):
image 5427
  • Launch Excel (or Notepad) and paste the contents of the clipboard (to create an XLSX or TXT file).

Tablesize.sql

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS9S6B","label":"IBM Cognos Controller"},"ARM Category":[{"code":"a8m0z000000GmxvAAC","label":"Database Tables"}],"ARM Case Number":"TS003997135","Platform":[{"code":"PF033","label":"Windows"}],"Version":"All Version(s)","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

1021995

Document Information

Modified date:
06 August 2020

UID

swg21345780