List the size of tables and stuff in a Sqlserver
A simple solution copied from here is
1 | sp_msforeachtable "sp_spaceused '?'" |
an almost as simple is the one below copied from here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 | SET NOCOUNT ON CREATE TABLE #TBLSize (Tblname VARCHAR(80), TblRows INT, TblReserved VARCHAR(80), TblData VARCHAR(80), TblIndex_Size VARCHAR(80), TblUnused VARCHAR(80)) DECLARE @DBname VARCHAR(80) DECLARE @tablename VARCHAR(80) SELECT @DBname = DB_NAME(DB_ID()) PRINT 'User Table size Report for (Server / Database): ' + @@ServerName + ' / ' + @DBName PRINT '' PRINT 'By Size Descending' DECLARE TblName_cursor CURSOR FOR SELECT NAME FROM sysobjects WHERE xType = 'U' OPEN TblName_cursor FETCH NEXT FROM TblName_cursor INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO #tblSize(Tblname, TblRows, TblReserved, TblData, TblIndex_Size, TblUnused) EXEC Sp_SpaceUsed @tablename -- Get the next author. FETCH NEXT FROM TblName_cursor INTO @tablename END CLOSE TblName_cursor DEALLOCATE TblName_cursor SELECT CAST(Tblname AS VARCHAR(30)) 'Table', CAST(TblRows AS VARCHAR(14)) 'Row Count', CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) AS INT) 'Total Space (KB)', CAST(TblData AS VARCHAR(14)) 'Data Space', CAST(TblIndex_Size AS VARCHAR(14)) 'Index Space', CAST(TblUnused AS VARCHAR(14)) 'Unused Space' FROM #tblSize ORDER BY 'Total Space (KB)' DESC PRINT '' PRINT 'By Table Name Alphabetical' SELECT CAST(Tblname AS VARCHAR(30)) 'Table', CAST(TblRows AS VARCHAR(14)) 'Row Count', CAST(LEFT(TblReserved, CHARINDEX(' KB', TblReserved)) AS INT) 'Total Space (KB)', CAST(TblData AS VARCHAR(14)) 'Data Space', CAST(TblIndex_Size AS VARCHAR(14)) 'Index Space', CAST(TblUnused AS VARCHAR(14)) 'Unused Space' FROM #tblSize ORDER BY 'Table' DROP TABLE #TblSize |