Check the size of all tables in my database

Hi

I am trying to check the size of each table in my database?

SELECT <TableName> , 'Size in bytes/megabytes' FROM DATABASE

I can't for the lif of me figure out how this is done.

Any help would be greatly appreciated

Kind Regards

Carel Greaves

Courtesy of Vinod Kumar

http://www.extremeexperts.com/SQL/Scripts/FindSizeOfTable.aspx|||

Hi Carel,

in management studio click on database, then right click, then click on reports - > standard reports -> Disk usage by table.

There are quite a few useful reports there....and in case you have not heard of them, there are a set of performance reports which we have found very useful now from MSFT. They can show you things like the plans for sql in process on the machine.....so if you have a slow running query you can look at the machine and then look at the plan that is being used for the running of the query....

This set of performance reports is a good beginning for a set of tools to monitor a server.....well, good for free....if you want better you should look into things like quest.....

Best Regards

Peter

|||

I must be blind because i don't see the reports link, maybe its a plug-in that i don't have with my management studio.

The database is SQL 2000, that is why i am looking for a query to perform the task, however i do connect to this specific server using a SQL Server 2005 Management Console.

|||

Try this:

-- will result in the dump information on space occupied by each table.

EXECsp_msforeachtable'sp_spaceused "?"'

|||

Thats EXACTLY what i want thanks, but it give me this error

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

When i take the results to a file or report then it doesn't include the stats, it only has the table names.

I'm qute new to this, soz guys. I really appreciate the help.

|||If you still have Query Analyzer available, you can run that query there. It does not have the 100 resultset limit that SSMS has.|||

Here is a stored procedure that I use, it doesn't have the resultset display limit, and it does not depend upon 'undocumented' functionality. (We keep getting MSFT folks warning us that it may change in the future...)

Code Snippet

CREATE PROCEDURE dbo.TableSpace
AS
BEGIN

DECLARE
@.TotalRows int,
@.Counter int,
@.TableName varchar(50)

DECLARE @.MyTables table
( RowID int IDENTITY,
TableName varchar(50),
Rows bigint,
Reserved varchar(12),
Data varchar(12),
IndexSize varchar(12),
Unused varchar(12)
)

INSERT INTO @.MyTables ( TableName )
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

SELECT
@.TotalRows = @.@.ROWCOUNT,
@.Counter = 1

WHILE ( @.Counter <= @.TotalRows )

BEGIN

SELECT @.TableName = TableName
FROM @.MyTables
WHERE RowID = @.Counter

INSERT INTO @.MyTables
EXECUTE sp_spaceused @.TableName

SET @.Counter = ( @.Counter + 1 )

END

DELETE FROM @.MyTables
WHERE RowID <= @.TotalRows

SELECT
TableName,
Rows,
Reserved,
Data,
IndexSize,
Unused
FROM @.MyTables
ORDER BY TableName

END
GO

EXECUTE dbo.TableSpace

|||select page_count * 8 * 1024 from sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, 'DETAILED')

This gives you the size for each table, each index, and each partition. The dmv returns the number of pages, which you need to multiple by 8K to get the byte size.

If you only want the information for a certain table / index / partition, you can specify the second, third and fourth parameter. If you specify NULL, you get all tables / indexes / partitions

Thanks,|||

Similar to the stored proc another poster provided, this script will get the info you are looking for and is compatilble with SQL 2000. Of course, you can adjust the results query as you need to fit your uses and provide different statistics. Hope this helps.

if object_id('tempdb..#TableUsage') is not null drop table #TableUsage

create table #TableUsage

(

TableName sysname,

Rows int,

Reserved varchar(20),

ReservedValue as cast(replace(Reserved, ' KB', '') as int),

Data varchar(20),

DataValue as cast(replace(Data, ' KB', '') as int),

Indexsize varchar(20),

IndexsizeValue as cast(replace(Indexsize, ' KB', '') as int),

Unused varchar(20),

UnusedValue as cast(replace(Unused, ' KB', '') as int),

)

exec sp_msforeachtable 'insert #TableUsage ( TableName, Rows, Reserved, Data, Indexsize, Unused ) exec sp_spaceused ''?'''

select

count(*) as Tables,

sum(ReservedValue) as Reserved,

sum(DataValue) as Data,

sum(IndexsizeValue) as Indexsize,

sum(UnusedValue) as Unused

from #TableUsage

if object_id('tempdb..#TableUsage') is not null drop table #TableUsage