Table Size Information

Objecive: To find table size information for particular database
USE DBNAME
go
SET NOCOUNT ON
/*DATABASE TABLE SPY SCRIPT
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(150),
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 Alphabetically
SELECT * FROM #TempTable ORDER BY Table_Name
END
ELSE
BEGIN
/*Retrieve TABLE Data AND Sort BY the size OF the Table*/
SELECT *, cast(replace(table_size, 'kb', '') as int) ts FROM #TempTable ORDER BY ts DESC
END
DROP TABLE #TempTable