Carel Greaves


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




Re: Check the size of all tables in my database

Sankar Reddy


Courtesy of Vinod Kumar

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





Re: Check the size of all tables in my database

peternolan9

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







Re: Check the size of all tables in my database

Carel Greaves

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.





Re: Check the size of all tables in my database

GlennAlanBerry

Try this:

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

EXEC sp_msforeachtable 'sp_spaceused " "'





Re: Check the size of all tables in my database

Carel Greaves

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.





Re: Check the size of all tables in my database

GlennAlanBerry

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



Re: Check the size of all tables in my database

Arnie Rowland

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






Re: Check the size of all tables in my database

Marcel van der Holst - MSFT

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,





Re: Check the size of all tables in my database

stytxus

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