Comments from the following blog entry: Determining All Table Sizes in SQL Server, located at: http://www.publicidentity.ca/post/2007/11/Determining-All-Table-Sizes-in-SQL-Server.aspx

Very Nice Workaround!! Keep up the good work!!

Great tool, but I would like to see them listed by datasize with the largest on top. How would that be done? ORDER BY does not seem to work correctly.

Justin,

The result from SQL Server is a varchar data element. If you wanted to be able to sort it based on the sizes, when inserting the values into the temporary table, you would have to trim off the KB designator at the end of the size, and convert it to a numeric data type column. It is do-able, but you will have to modify the SQL to get it done.

if you want post a request in my Forum and I can try get a modified version of the script out there.

hi
there is a simple wayt to get size off table is sql sever by SP_SPACEUSED 'TABLENAME'

Yes, that is the method that this uses, it just combines the info for ALL tables in your database.

ORDER BY CAST(LEFT(dataSize,LEN(dataSize)-3) AS NUMERIC(18,0)) DESC

Your SP seems to be working for objects with 'dbo' owner but in the case of tables with schema name prefixed results in error. The SP has to be modified according to that.

Nice one! Helped me find what was making an inherited database 16Gb big (15 Gb in a single, unnecessary, stats table!).

Many thanks, much appreciated.

For SQL2000 (and earlier) you should run DBCC UPDATEUSAGE to make sure you get the correct counts returned. From Books Online:
In earlier (before 2005) versions of SQL Server, the values for the table and index row counts and page counts can become incorrect. Databases that were created on versions prior to SQL Server 2005 may contain incorrect counts. Therefore, we recommend running DBCC UPDATEUSAGE after upgrading to SQL Server 2005 to correct any invalid counts.

Yes, that is a good point, the down side, is recalculation is VERY costly if you go to run it on ALL tables inside a database at once, thus why I left that option off in this example.

Superb.....Its Very helpful.

I found the following very useful.

http://www.therightstuff.de/2007/11/19/How-To-Obtain-The-Size-Of-All-Tables-In-A-SQL-Server-Database.aspx

Here I can't see any screenshot to see how it works so I was a bit hesitant to use the script.

So, I used the steps from above link instead.

Anyway, thank you for your posting.

I think the following statement would suffice

EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Nice script. Did anyone get it to work with tables within schema?

Let me do a bit of research, I think I might be able to do it.

I like taking shortcuts. I opened sp_spaceused and came up with this:


ALTER Procedure [dbo].[spTableSize] AS

Declare @Low float
select @Low = d.low / 1024.
from master.dbo.spt_values d
where d.number = 1
and d.type = 'E'

select o.name, str((sum(i1.dpages) + isnull(sum(i2.used), 0)) * @Low) + ' KB'
from sysindexes i1
inner join sysobjects o on o.id = i1.id
inner join sysindexes i2 on o.id = i2.id
where i1.indid < 2
and i2.indid < 255
group by o.name

Someone said there is even a way to do this as a view and thereby be able to use it in joins.

Very helpful. Thanks Mitchel. I'd like to second the "sorted" results idea.

Dax

Quick addition to sort the results by the number of rows.

SELECT * FROM #TempTable ORDER BY cast(numberofrows as int) DESC

This creates the USP and order by row counts.

--


CREATE PROCEDURE usp_GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/


--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
Name varchar(100),
Rows varchar(100),
reserved varchar(50),
data varchar(50),
index_Size varchar(50),
unused varchar(50)
)


--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
Exec sp_MSforeachtable @command1="exec sp_spaceused'?'"

--Select all records so we can use the reults
SELECT *
FROM #TempTable
Order By Rows

--Final cleanup!-

DROP TABLE #TempTable

In the create table section it can be a better soulution to use int as datatype in the Rows Coloumn. Gives a better sort.

Thanks a lot - helped a lot.

This works well and can be queried into other views, etc.:

alter function ObjSpaceUsed
(
@id int
)
returns @objInfo table( SchemaName sysname, ObjName sysname, ObjRows int, ObjReservedBytes int, ObjDataBytes int, ObjIndexBytes int, ObjUnusedBytes int )
as
begin

declare @type character(2) -- The object type.
,@pages bigint -- Working variable for size calc.
,@dbname sysname
,@dbsize bigint
,@logsize bigint
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
,@schemaName sysname

select @schemaName = SCHEMA_NAME(schema_id)
from sys.objects
where object_id = @id

SELECT
@reservedpages = SUM (reserved_page_count),
@usedpages = SUM (used_page_count),
@pages = SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
),
@rowCount = SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
)
FROM sys.dm_db_partition_stats
WHERE object_id = @id;

/*
** Check if table has XML Indexes or Fulltext Indexes which use internal tables tied to this table
*/
IF (SELECT count(*) FROM sys.internal_tables WHERE parent_id = @id AND internal_type IN (202,204,211,212,213,214,215,216)) > 0
BEGIN
/*
** Now calculate the summary data. Row counts in these internal tables don't
** contribute towards row count of original table.
*/
SELECT
@reservedpages = @reservedpages + sum(reserved_page_count),
@usedpages = @usedpages + sum(used_page_count)
FROM sys.dm_db_partition_stats p, sys.internal_tables it
WHERE it.parent_id = @id AND it.internal_type IN (202,204,211,212,213,214,215,216) AND p.object_id = it.object_id;
END

insert @objInfo
SELECT
@schemaName,
object_name(@id),
rows = @rowCount,
reserved = @reservedpages * 8192,
data = @pages * 8192,
index_size = CASE WHEN @usedpages > @pages THEN (@usedpages - @pages) ELSE 0 END * 8192,
unused = CASE WHEN @reservedpages > @usedpages THEN (@reservedpages - @usedpages) ELSE 0 END * 8192

return
end
go
select osu.*
from sys.objects so
CROSS APPLY dbo.ObjSpaceUsed(so.object_id) osu
where so.type='U'

In my just prior comment, to use this function for a single object by name, just call:

select * from dbo.ObjSpaceUsed(object_id('someschema.someobject'))

Great! Thanks a lot. :)