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

x
?
Solved

EXEC sp_spaceused for all tables in my database

Posted on 2007-08-09
5
Medium Priority
?
5,288 Views
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?
0
Comment
Question by:LegalZoomer
5 Comments
 
LVL 75

Assisted Solution

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

Author Comment

by:LegalZoomer
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.
0
 
LVL 9

Accepted Solution

by:
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
0
 
LVL 9

Expert Comment

by:apirnia
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.
0
 
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.
0

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