Check Used Table Space for User Defined Tables

,

Often SQL Server Database Administrators struggle with hard disk space and constantly strive to clean up ‘Tables’, writing many queries to find which Table uses the most hard disk space.

This article illustrates how to query the System Tables to find the space usage, which helps Database Administrators identify the Tables that are using the most space in order to archive the old data and purge un-necessary data.

1. Logon to the SQL Server Instance [SQL Server 2005 or SQL Server 2008].

2. Navigate to the database that you want to get the space information on.

3. Copy and Paste the code to your Query Window and Execute it.

4. Check the Results and view the Used Table Space within the selected Database.

DECLARE
@TABLENM SYSNAME,
@CNT INT,
@TOPN INT
DECLARE TABLE_SPACE CURSOR FAST_FORWARD
FOR 
 SELECT
 NAME
 FROM SYSOBJECTS
 WHERE XTYPE = 'U'
SELECT @CNT = 0, @TOPN = 0
CREATE TABLE #TMPUSAGE
 (
 NAME SYSNAME,
 ROWS INT,
 RESERVED VARCHAR(20),
 DATA VARCHAR(20),
 INDEX_SIZE VARCHAR(20),
 UNUSED VARCHAR(20)
 )
OPEN TABLE_SPACE
FETCH NEXT FROM TABLE_SPACE INTO @TABLENM
WHILE @@FETCH_STATUS = 0 AND @CNT <= @TOPN
BEGIN
INSERT INTO #TMPUSAGE 
EXEC SP_SPACEUSED @TABLENM, 'TRUE'
 IF
@TOPN <> 0
SELECT @CNT = @CNT +1
FETCH NEXT FROM TABLE_SPACE INTO @TABLENM
 END
CLOSE TABLE_SPACE
DEALLOCATE TABLE_SPACE
SELECT 
 *
FROM 
#TMPUSAGE 
ORDER BY 
CONVERT(INT,LEFT(RESERVED, LEN(RESERVED)- 3)) DESC

IF (SELECT OBJECT_ID('TEMPDB..#TMPUSAGE') ) IS NOT NULL
DROP TABLE #TMPUSAGE

Rate

5 (2)

Share

Share

Rate

5 (2)