Solved

SQL 2005 Table Size

Posted on 2011-02-12
4
265 Views
Last Modified: 2012-05-11
Hi,

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

Thanks
0
Comment
Question by:ITMaster1979
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 83 total points
ID: 34881508
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 83 total points
ID: 34881629
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 84 total points
ID: 34883692
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
 
LVL 1

Author Closing Comment

by:ITMaster1979
ID: 35020282
good solution
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now