SQL queries the number and space of user tables used by the database

The SQL Server database administrator often struggles to clean up the table and write many queries to find the hard disk space used by the table. This article describes how to query the space usage of tables and help the database administrator identify the most used space to archive old data and clear unnecessary data tables. 1. Log on to S

The SQL Server database administrator often struggles to clean up the table and write many queries to find the hard disk space used by the table. This article describes how to query the space usage of tables and help the database administrator identify the most used space to archive old data and clear unnecessary data tables. 1. Log on to S

SQL ServerDatabaseAdministrator Hard DiskSpaceStruggle, constantly strive to clean up the "table", write a lot of queries, and find the tableUseHard DiskSpace.

This topic describes how to query a tableSpaceUseSituation, helpDatabaseThe Administrator identifiesUseThe mostSpaceTo Archive old data and clear unnecessary data tables.

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

2. Browse to what you wantSpaceInformationDatabase.

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

4. Check the result andViewSelectedDatabaseInternal tableSpace

DECLARE@TABLENM SYSNAME,@CNT INT,@TOPN INTDECLARE TABLE_SPACE CURSOR FAST_FORWARDFOR  SELECT NAME FROM SYSOBJECTS WHERE XTYPE = 'U'SELECT @CNT = 0, @TOPN = 0CREATE TABLE #TMPUSAGE ( NAME SYSNAME, ROWS INT, RESERVED VARCHAR(20), DATA VARCHAR(20), INDEX_SIZE VARCHAR(20), UNUSED VARCHAR(20) )OPEN TABLE_SPACEFETCH NEXT FROM TABLE_SPACE INTO @TABLENMWHILE @@FETCH_STATUS = 0 AND @CNT <= @TOPNBEGININSERT INTO #TMPUSAGE EXEC SP_SPACEUSED @TABLENM, 'TRUE' IF@TOPN <> 0SELECT @CNT = @CNT +1FETCH NEXT FROM TABLE_SPACE INTO @TABLENM ENDCLOSE TABLE_SPACEDEALLOCATE TABLE_SPACESELECT  *FROM #TMPUSAGE ORDER BY CONVERT(INT,LEFT(RESERVED, LEN(RESERVED)- 3)) DESCIF (SELECT OBJECT_ID('TEMPDB..#TMPUSAGE') ) IS NOT NULLDROP TABLE #TMPUSAGE