Checking space used by SQL Tables and reorganizing those

SET NOCOUNT ON

/*DATABASE TABLE SPY SCRIPT

Micheal Soelter

1/24/03

DESCRIPTION

Returns TABLE Size Information

SORTING USAGE

@Sort bit VALUES

0 = Alphabetically BY TABLE name

1 = Sorted BY total space used by TABLE

*/

DECLARE @cmdstr varchar(100)

DECLARE @Sort bit

SELECT @Sort = 1 /* Edit this value FOR sorting options */

/* DO NOT EDIT ANY CODE BELOW THIS LINE */

--Create Temporary Table

CREATE TABLE #TempTable

( [Table_Name] varchar(50),

Row_Count int,

Table_Size varchar(50),

Data_Space_Used varchar(50),

Index_Space_Used varchar(50),

Unused_Space varchar(50)

)

--Create Stored Procedure String

SELECT @cmdstr = 'sp_msforeachtable ''sp_spaceused "?"'''

--Populate Tempoary Table

INSERT INTO #TempTable EXEC(@cmdstr)

--Determine sorting method

IF @Sort = 0

BEGIN

--Retrieve Table Data and Sort Alphabet

-- ically

SELECT * FROM #TempTable ORDER BY Table_Name

END

ELSE

BEGIN

/*Retrieve TABLE Data AND Sort BY the size OF the Table*/

SELECT * FROM #TempTable ORDER BY CAST(SUBSTRING(Table_Size,1,CHARINDEX(' ',Table_Size,1)) AS INT) DESC

END

DECLARE @TableName varchar(100)

DECLARE TestCursor CURSOR FOR SELECT Table_Name FROM #TempTable FAST_FORWARD

OPEN TestCursor

FETCH NEXT FROM TestCursor INTO @TableName

WHILE @@FETCH_STATUS = 0 BEGIN

DBCC CLEANTABLE(0, @TableName)

FETCH NEXT FROM TestCursor INTO @TableName

END

CLOSE TestCursor

DEALLOCATE TestCursor

--Delete Temporay Table

DROP TABLE #TempTable