SQL Server SQL Statements

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

Stewart Schatz
561Views

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

PHP Code Snippets Powered By : XYZScripts.com