EXEC sp_spaceused for all tables in my database

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?
LegalZoomerAsked:
Who is Participating?
 
apirniaConnect With a Mentor Commented:
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
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
EXEC sp_MSForEachTable "EXEC sp_spaceused '?','TRUE' "
0
 
LegalZoomerAuthor Commented:
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
 
apirniaCommented:
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
 
Scott PletcherSenior DBACommented:
Or set QA output to text instead of grid; I don't think text output has that limit either.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.