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)