How to find out the (biggest) table size in MS SQL Server?

How to find out the (biggest) table size in MS SQL Server? To find the answer in your preferred search engine is difficult – not because there are no hits, but there is that lot of crap to see, it’s just unbelievable. If you don’t want to enjoy this experience, maybe have a look into this page: http://blogs.technet.com/b/mdegre/archive/2009/10/14/determining-sql-server-table-size.aspx

The author, Michel Degremont, did a good job, but his post is ranked way too badly for the quality provided. Give credit where credit is due!

Standing on the shoulders of giants,
Yours, Martin

Adding a 1:1 copy here:

CREATE PROCEDURE getAllTablesSize

AS

BEGIN

DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS;

CREATE TABLE

#temp (

[name] varchar(250),

[rows] varchar(50),

[reserved] varchar(50),

[data] varchar(50),

[index_size] varchar(50),

[unused] varchar(50)

);

INSERT #temp EXEC (‘sp_msforeachtable ”sp_spaceused ””?”””’);

UPDATE

#temp

SET

[rows] = LTRIM(RTRIM(REPLACE(t.rows,’KB’,”))),

[reserved] = LTRIM(RTRIM(REPLACE(t.reserved,’KB’,”))),

[data] = LTRIM(RTRIM(REPLACE(t.data,’KB’,”))),

[index_size] = LTRIM(RTRIM(REPLACE(t.index_size,’KB’,”))),

[unused] = LTRIM(RTRIM(REPLACE(t.unused,’KB’,”)))

FROM #temp AS t

SELECT

SUM(CAST([reserved] as decimal))/1024 AS ‘Total reserved MB’,

SUM(CAST([data] as decimal))/1024 AS ‘Total data MB’,

SUM(CAST([index_size] as decimal))/1024 AS ‘Total index_size MB’,

SUM(CAST([unused] as decimal))/1024 AS ‘Total unused MB’

FROM

#temp

SELECT

[name] ,

CAST([rows] as INT)’rows’ ,CAST([reserved] as INT)/1024 ‘reserved MB’,

CAST([data] as INT)/1024 ‘data MB’ ,

CAST([index_size]/1024 as INT)’index_size MB’,

CAST([unused] as INT)/1024 ‘unused MB’

FROM

#temp

ORDER BY

CAST(reserved as INT) DESC

DROPĀ  TABLE #temp;

— rows : Number of rows existing in the table. If the object specified is a Service Broker queue, this column indicates the number of messages in the queue.

— reserved : Total amount of reserved space for objname.

— data : Total amount of space used by data in objname.

— index_size : Total amount of space used by indexes in objname.

— unused : Total amount of space reserved for objname but not yet used.

— unused : Total amount of space reserved for objname but not yet used.

— More detail here : http://msdn.microsoft.com/en-us/library/ms188776.aspx

 

END

GO

EXECUTE getAllTablesSize

 




You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply