Tuesday, May 31, 2011

Get All Table Sizes

use master go CREATE PROCEDURE sp_GetAllTableSizes AS BEGIN DECLARE @tabname varchar(128) DECLARE table_cur CURSOR FAST_FORWARD FOR SELECT [name] FROM dbo.sysobjects (NOLOCK) WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 CREATE TABLE #TempTable ( tableName varchar(128), numberofRows int, reservedSize varchar(50), dataSize varchar(50), indexSize varchar(50), unusedSize varchar(50) ) OPEN table_cur FETCH NEXT FROM table_cur INTO @tabname WHILE (@@Fetch_Status >= 0) BEGIN INSERT #TempTable EXEC sp_spaceused @tabname FETCH NEXT FROM table_cur INTO @tabname END CLOSE table_cur DEALLOCATE table_cur SELECT * FROM #TempTable ORDER BY numberofRows DESC DROP TABLE #TempTable END --sp_GetAllTableSizes GO

No comments:

Post a Comment