SQL 2005 Table Size

Hi,

  I need a query to collect the top 20 tables size for mty SQL 2005 database.

Thanks
LVL 1
ITMaster1979Asked:
Who is Participating?
 
Anthony PerkinsCommented:
Why re-invent the wheel: There is a standard report in SSMS that already gives you that information:
1. Object Explorer.
2. Right-click the database.
3. Reports
4. Standard Reports
5. Disk Usage by Top Tables.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
0
 
Aaron ShiloChief Database ArchitectCommented:
hi
this should help you.

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

--

CREATE PROCEDURE #TableSpaceUsed
AS

-- Create the temporary table...
CREATE TABLE #tblResults
(
   [name]   nvarchar(255),
   [rows]   int,
   [reserved]   varchar(255),
   [reserved_int]   int default(0),
   [data]   varchar(255),
   [data_int]   int default(0),
   [index_size]   varchar(255),
   [index_size_int]   int default(0),
   [unused]   varchar(255),
   [unused_int]   int default(0)
)


-- Populate the temp table...
EXEC sp_MSforeachtable @command1=
         "INSERT INTO #tblResults
           ([name],[rows],[reserved],[data],[index_size],[unused])
          EXEC sp_spaceused '?'"
   
-- Strip out the " KB" portion from the fields
UPDATE #tblResults SET
   [reserved_int] = CAST(SUBSTRING([reserved], 1,
                             CHARINDEX(' ', [reserved])) AS int),
   [data_int] = CAST(SUBSTRING([data], 1,
                             CHARINDEX(' ', [data])) AS int),
   [index_size_int] = CAST(SUBSTRING([index_size], 1,
                             CHARINDEX(' ', [index_size])) AS int),
   [unused_int] = CAST(SUBSTRING([unused], 1,
                             CHARINDEX(' ', [unused])) AS int)
   
-- Return the results...
SELECT * FROM #tblResults order by reserved_int desc


-- drop procedure TableSpaceUsed
-- exec TableSpaceUsed
0
 
ITMaster1979Author Commented:
good solution
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.