Display the size of all tables in Sql Server 2005

sp_spaceused returns number of rows, disk space reserved, and disk space used by a table. However when you have to return the space used by all the tables in a database, you have two options: One is to loop through all the tables and then pass the table name to the sp_spaceused procedure. The second is to use the undocumented sp_MSforeachtable procedure. We will explore both of these over here:

The Lengthy Way

USE  yourdbname
DECLARE   @TblNames   Table
(
COUNTER  INT   IDENTITY ( 1 , 1 ),
tbl_name  nvarchar ( 100 )  NULL
)
DECLARE   @ROWCOUNT   INT
DECLARE   @I   INT
DECLARE   @str   nvarchar ( 100 )
SET   @I   =   1
INSERT   INTO   @TblNames (tbl_name)  SELECT  name  FROM  sys.Tables
SET   @ROWCOUNT   =   @@ROWCOUNT
WHILE   @I   <=   @ROWCOUNT
BEGIN
SELECT   @str   =  tbl_name  FROM   @TblNames   WHERE  COUNTER  =   @I
EXEC  sp_spaceused  @str
SET   @I   =   @I   + 1
END

 

Note: The advantage in taking the lengthy approach is that you can create another temporary table and sort the tables based on the space used.

The Short Way

USE  yourdbname
EXEC  sp_MSforeachtable  @command1 = " EXEC  sp_spaceused  ' ? ' "

Note: sp_MSforeachtable is an undocumented stored procedure

References :
http://msdn2.microsoft.com/en-us/library/ms188776.aspx
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic37975.aspx
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

转载于:https://www.cnblogs.com/josephshi/archive/2008/04/02/1134564.html