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. :)

If you change 'SELECT * FROM #TempTable' to the following will display the largest reserved space first.

SELECT *, replace(reservedSize, 'KB','')*1 as rSize FROM #TempTable order by rSize desc

Nice thread ! thanks to everyone for the contribution ..saved me a bunch of time !! :)

Nice! saved me a lot of work.. thanks.

Thanks for all of your help. It saves lot of time

You have to alter the script in order to get the schemaname.table name. Otherwise SQL Server will give you only the tables from the dbo schema.

Thanks a lot Mitchel !!!!!! It works like a charm, I had all informations I wanted to have, your article is VERY clear for a novice like me, everything is in to understand and to apply.
CONGRATULATIONS !! One of the best article regarding DNN and how to maintain a database.

Déclic Vidéo FX

Comments from the following blog entry: Determing SQL Server Table Size, located at: http://pimpthisblog.com/Determing-SQL-Server-Table-Size

Thanks very much. Very clear explanation. Helped me save a lot of time.

Comments from the following blog entry: How to list the size of the tables within your SQL Server database, located at: http://www.orcsweb.com/blog/brad/how-to-list-the-size-of-the-tables-within-your-sql-server-database/

how about this?
SELECT B.name, SUM(A.reserved_page_count * 8)/1024
FROM sys.dm_db_partition_stats A, sys.OBJECTS B
WHERE A.object_id = B.object_id
GROUP BY B.name
ORDER BY 2 DESC

how about this?
SELECT B.name, SUM(A.reserved_page_count * 8)/1024
FROM sys.dm_db_partition_stats A, sys.OBJECTS B
WHERE A.object_id = B.object_id
GROUP BY B.name
ORDER BY 2 DESC

how about this?
SELECT B.name, SUM(A.reserved_page_count * 8)/1024
FROM sys.dm_db_partition_stats A, sys.OBJECTS B
WHERE A.object_id = B.object_id
GROUP BY B.name
ORDER BY 2 DESC

how about this?
SELECT B.name, SUM(A.reserved_page_count * 8)/1024
FROM sys.dm_db_partition_stats A, sys.OBJECTS B
WHERE A.object_id = B.object_id
GROUP BY B.name
ORDER BY 2 DESC

For the order by DataSize I made this simple change:
ORDER BY LEN(dataSize) DESC, LEFT(datasize,1) DESC

Hi

I've modified your script. Thanks for your permission to post it on my blog. I've changed and parameterised the ORDER BY clause of the SELECT statement. You can find it <a href="http://bit.ly/AJZA_tblsz">here</a>.

Thank you
Andrew Jackson

Comments from the following blog entry: Determine All SQL Server Table Sizes, located at: http://stevesmithblog.com/blog/determine-all-sql-server-table-sizes/

You can also use Truncate commands to clear Eventlog ScheduleHistory and other troublemaker tables. We used this tip at our <a href="http://www.bookinturkeytours.com" target="_blank" title="book a cultural tour at Turkey">travel blog</a> several times.

Comments from the following blog entry: Listing the size of all tables in a SQL Server Database, located at: http://dave.harris.net/post/2010/06/02/Listing-the-size-of-all-tables-in-a-SQL-Server-Database.aspx

Comments from the following blog entry: Listing the size of all tables in a SQL Server Database, located at: http://dave.harris.net/post/2010/06/02/Listing-the-size-of-all-tables-in-a-SQL-Server-Database.aspx

that was a great help ....thanks

THANKYOU.
My database had grown to over 25GB.
Thanks to your handy script, I was finally able to discover why. The SearchItemWord & SearchItemWordPosition tables had over 1 Billion rows... (1,084,865,702). GoodLORD.

These two sites also helped with scripts to clear those tables out:
http://www.willstrohl.com/Blog/EntryId/576/Saving-Database-Space-in-DotNetNuke-Clearing-the-Search-Index
http://weblogs.asp.net/christoc/archive/2006/06/26/DotNetNuke-Daily-Tip-_2300_3-6_2F00_26_2F00_06-Clear-Search-Tables.aspx

You rock Mitch!

Hello All,

I keep my custom tables in a seperate schema, so that they are not mixed in with the dnn tables, to include the schema in the sproc, I have to make a change to retrieve the schema as well as the table name.

Here is my version. Hope it is useful to someone.

CREATE PROCEDURE GetAllTableSizes
AS
/*
Obtains spaced used data for ALL user tables in the database
*/
DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing
DECLARE tableCursor CURSOR
FOR
SELECT s.name + '.'+ o.name
from dbo.sysobjects o
INNER JOIN sys.schemas s ON o.uid = s.schema_id
where OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY


--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(100),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)

--Open the cursor
OPEN tableCursor

--Get the first table name from the cursor
FETCH NEXT FROM tableCursor INTO @TableName

--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Dump the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName

--Get the next table name
FETCH NEXT FROM tableCursor INTO @TableName
END

--Get rid of the cursor
CLOSE tableCursor
DEALLOCATE tableCursor

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

--Final cleanup!
DROP TABLE #TempTable

GO

Mitchell, buddy you've done a good job. Thanks for this Stored Procedure.
However, I had a suggestion. I'm not sure if it is practically possible. All the fields declared in the #TEMPTABLE are of type VARCHAR. In the end you've used a SELECT * FROM statement on this #TEMPTABLE. I added ORDER BY clause to arrange the output. I wanted to sort this list of tables by the number of rows that they have, with the largest tables at the top. Unfortunately it did not serve the purpose. So my question is whether SP_SPACEUSED returns a numeric value for RowCount. If yes, can we change the datatype of associated field in the #TEMPTABLE. What do you say ... ?
Thanks,
Aashish.

That should be possible, you will just have to modify the type, and potentially do a cast on the result to get it there.

Instead of using a cursor should go with set based operations.