0

I want to know which tables in my SQL server database are fairly large in size. I found a SQL function that calculates an estimate size of a table (link), but it does not support to list it for all of the available tables. Is there a way to do so? I've managed to write the function within a TSQL script, it does answer my question, however it isn't the most beautiful way to solve it. 

DECLARE @table_name varchar(100)
DECLARE @tables TABLE (table_name VARCHAR(255), column_list VARCHAR(255));

SELECT * INTO #tmp_tables FROM (
SELECT o.name AS table_name,
    STUFF(
    (
        SELECT ', ' + c.name
        FROM sys.columns AS c
        WHERE c.object_id = o.object_id
        ORDER BY c.column_id
        FOR XML PATH('')
    ), 1, 2, '') AS column_list
FROM sys.objects AS o
WHERE type = 'U') table_names

WHILE (SELECT COUNT(*) FROM #tmp_tables) > 0
BEGIN
SELECT TOP 1 @table_name = table_name FROM #tmp_tables;
print @table_name;
EXEC Sp_spaceused @table_name;
DELETE #tmp_tables where table_name = @table_name;

END
DROP TABLE #tmp_tables;
Guido
  • 19
  • 1

2 Answers2

0

The documentation states that Sp_spaceused should be applied on all of the tables in the DB if the parameter is omitted.

If this is not true, I would use MSforeachtable to iterate through all of tables and call Sp_spaceused on each of them, and then sum the result.

sp_msforeachtable ' Sp_spaceused ''?'' '
Uri Goren
  • 13,386
  • 6
  • 58
  • 110
0

You can do this is a single query. Query dm_db_index_physical_stats to get the size of all partitions in the database. Join that to the other usual meta-data tables such as sys.indexes and sys.objects.

usr
  • 168,620
  • 35
  • 240
  • 369
  • I see it linked to this question, which is fairly the elegant version of what I was looking for: http://stackoverflow.com/questions/7892334/get-size-of-all-tables-in-database?lq=1 – Guido Jan 06 '15 at 16:16