SQL Server SQL Statements

How To Find The Physical Size Of SQL Server Tables And Indexes

Stewart Schatz
397Views
Read Time:44 Sec

Inspired by Shannon’s post (give me the size of data and indexes for a file in an oracle database please), I found a SQL statement that finds the size of each table and index in a SQL Server database. I also found that there are a ton of different ways to get this information. Below is the method that I is most beneficial to me:

 
EXEC sp_spaceused
GO

DECLARE @spaceUsed TABLE (
 name varchar(255), 
 rows int, 
 reserved varchar(50), 
 data varchar(50), 
 index_size varchar(50), 
 unused varchar(50))

INSERT INTO @spaceUsed
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT name, rows, left(reserved,len(reserved)-3) as [reserved (KB)], 
	left(data,len(data)-3) as [data (KB)], 
	left(index_size,len(index_size)-3) as [index_size (KB)], 
	left(unused,len(unused)-3) as [unused (KB)]
FROM @spaceUsed
order by rows desc

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.
stewart@9rains.com
http://stewartschatz.com

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%
(Add your review)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post E1 Batch Approval Post Confusion
Next post Safely Delete Files From WINSXS

More Stories