[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


EXEC sp_spaceused for all tables in my database

Posted on 2007-08-09
Medium Priority
Last Modified: 2009-07-29
I can run EXEC sp_spaceused 'Tablename','TRUE' to get the size of my tables, is there any way I can run it for all so I can see which one takes the most space?
Question by:LegalZoomer
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 200 total points
ID: 19665002
EXEC sp_MSForEachTable "EXEC sp_spaceused '?','TRUE' "

Author Comment

ID: 19665069
It works and then it fails:

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

Accepted Solution

apirnia earned 1800 total points
ID: 19665097
Try this:

create table #TableStatistics (tablename varchar(128), rowcnt int, reserved varchar(10), data varchar(10), index_size varchar(10), unused varchar(10))
exec sp_MSforeachtable
   'insert into #TableStatistics EXEC sp_spaceused ''?'' '
select * from #TableStatistics
    order by rowcnt desc
drop table #TableStatistics

Expert Comment

ID: 19665111
I was in the middle of testing this but then I saw that aneeshattingal beat me to it. Basicaly you insert it to a temp table which wont have the limit of 100.
LVL 70

Expert Comment

by:Scott Pletcher
ID: 19665455
Or set QA output to text instead of grid; I don't think text output has that limit either.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

834 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