0

I am attempting to use the stored procedure sp_spaceused to retrieve the amount of space used by the tables in a database. IMPORTANT: I want to be able to make a query that returns this data, and cannot create a table.

The default use of this stored procedure outputs a table that contains the name of the database, the database size, and the unallocated space. By using the @objname parameter, you can choose to only return the space used of one table in the database and will return the name of the table, the number of rows the table has, the space reserved, the data, the index size, and the unused space.

What I want, is to take the values returned when using the @objname parameter for every table in the database, and combine them into a single output table. Say a database had 1000 tables in it. The final result should be, an output table with 1000 rows that has columns for the name of each table, the number of rows each table has, the space reserved, the data, the index size, and the unused space. (the outputs of the sp_spaceused stored procedure with the @objname parameter defined) If there is some other way to achieve what I want without sp_spaceused, I am open to suggestions.

Say this is a sample of tables from inside a database: [dbo].[DogLocations], [dbo].[DogPrices], [dbo].[Dogs]. The code below would make 3 separate tables for each input that contains all the information I need. 

EXEC sp_spaceused @objname = N'[dbo].[DogLocations]'
EXEC sp_spaceused @objname = N'[dbo].[DogPrices]'
EXEC sp_spaceused @objname = N'[dbo].[Dogs]'

How could I get this information from all tables in the database without inputting each table individually, and merge all the output tables into one?

Joshua H
  • 1
  • 2
  • Perhaps something like this https://www.mssqltips.com/sqlservertip/1177/determining-space-used-for-all-tables-in-a-sql-server-database/ – John Cappelletti Jul 20 '22 at 19:23
  • @JohnCappelletti That is very useful and I will look into it. One problem is that at first glance, it seems that this method requires the creation of a new table, and while I forgot to specify this at first so it is not your fault but mine, I need to use a query to return this information without creating a new table. – Joshua H Jul 20 '22 at 19:30
  • 1
    You can't create _a #temp table_? – Aaron Bertrand Jul 20 '22 at 19:37
  • 1
    Anyway this series might also be useful: https://www.mssqltips.com/sqlservertip/6389/how-to-find-space-used-by-tables-and-indexes-in-sql-server-part-3/?utm_source=AaronBertrand – Aaron Bertrand Jul 20 '22 at 19:37
  • 1
    @JohnCappelletti This actually works perfectly. The temp tables are working so I'm able to execute the query and it's giving me everything I need. – Joshua H Jul 20 '22 at 19:46
  • @JoshuaH Happy it helped. I'm sure by now you see in the example provided a temp table was created #SpaceUsed – John Cappelletti Jul 20 '22 at 19:50
  • Temp tables and loops are completely unnecessary here, you can do it all in a single query – Charlieface Jul 20 '22 at 20:59

0 Answers0