- // Step 1 // - if EXISTS (Select Name from sysobjects where name = '#stablesize' and type = 'u') DROP TABLE #SYSTABLESIZE - // Step 2 // - Create Table #Systables ( Table_name varchar (255), reserved varchar (255), data varchar (255), index_size varchar (255), unused varchar (255)) - // step 3 // - declare @tablename varchar (255) DECLARE TableListCursor CURSOR LOCAL FAST_FORWARD READ_ONLYFOR SELECT name FROM SYSOBJECTS WHERE xtype = 'U' ORDER BY nameOPEN TableListCursorFETCH NEXT FROM TableListCursor INTO @TableNameWHILE @@ FETCH_STATUS = 0BEGIN - PRINT @TableName INSERT #SysTableSize (TABLE_NAME, TABLE_ROWS, RESERVED, DATA , iNDEX_SIZE, UNUSED) EXEC sp_spaceused @TableName FETCH NEXT FROM TableListCursor INTO @TableName ENDCLOSE TableListCursorDEALLOCATE TableListCursor - // STEP 4 // - SELECT UPPER (TABLE_NAME) AS TABLE_NAME, CAST (REPLACE (RESERVED, 'KB', '') As Int) As Reserved_Size, Cast (Reserved, 'KB', '') AS INT) AS Reserved_Size, Cast (Replace (Data, 'KB', '') AS INT) AS DATA_SIZE, CAST (Replace (INDEX_SIZE, 'KB', '') AS INDEX_SIZE, CAST (Replace (unused, 'kb " , '') As int) as unuse_size from #syStablesize Order by Data_Size Desc - // Step 5 // - DROP TABLE #SyStablesize
