SQL to view the number of user tables used and the space used by the database

SQL Server database administrators often struggle with hard disk space, constantly trying to clean up "tables", composing many queries, and discovering the hard disk space that the table uses.

This article describes how to query the space usage of a system table to help the database administrator identify the space that is being used, so that old data can be archived and non-essential data tables are purged.

1. Log on to the SQL Server instance of [SQL Server 2005 or SQL Server 2008].

2. Browse to the database where you want to get the spatial information.

3. Copy and paste the code into your query window and execute it.

4. Check the results and view the table spaces within the selected database

DECLARE    @TABLENMSYSNAME,@CNT INT,    @TOPN INTDECLARETable_spaceCURSORFast_forward for          SELECTNAME fromSYSOBJECTSWHEREXTYPE= 'U'SELECT @CNT = 0,@TOPN = 0CREATE TABLE#TMPUSAGE (NAME SYSNAME, ROWSINT, RESERVEDVARCHAR( -), DATAVARCHAR( -), Index_sizeVARCHAR( -), UNUSEDVARCHAR( -)     )OPENTable_spaceFETCH NEXT  fromTable_space into @TABLENM while @ @FETCH_STATUS = 0  and @CNT <= @TOPNBEGIN    INSERT  into#TMPUSAGEEXECsp_spaceused@TABLENM,'TRUE' IF    @TOPN <> 0    SELECT @CNT = @CNT +1    FETCH NEXT  fromTable_space into @TABLENM ENDCLOSETable_spacedeallocateTable_spaceSELECT      * from#TMPUSAGEORDER  by     CONVERT(INT, Left(RESERVED,LEN(RESERVED)- 3))DESCIF(SELECT object_id('TEMPDB: #TMPUSAGE') ) is  not NULLDROP TABLE#TMPUSAGE

SQL to view the number of user tables used and the space used by the database