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