Finding table size (physical disk space)

101 100+

How do I find how much space a table takes up on a hard drive? Is there any call you can make that returns a size?

If so, how can I display this with asp?

Thanks for any help and insights.

Oct 4 '07 #1

Subscribe Post Reply

23668

pbmods

5,821

Expert 4TB

Heya, Zensunni.

Check this out:

Expand|Select|Wrap|Line Numbers

 
SELECT

        `DATA_LENGTH`

    FROM

        `information_schema`.`TABLES`

    WHERE

    (

            `TABLE_SCHEMA` = 'database name'

        AND

            `TABLE_NAME` = 'table name'

    )

    LIMIT 1;

Oct 4 '07 #2

zensunni

101

100+

Ops, I'm retarded.

I meant to post in the MS SQL forum. Thanks for the reply though. MySQL is my DB of choice. Just wish all my clients could think that too :)

Thanks again and sorry.

Oct 5 '07 #3

pbmods

5,821

Expert 4TB

Heya, zensunni.

No problem.

I'm going to go ahead and move this thread to the MS SQL forum, where our resident Experts will be better able to help you out.

Oct 5 '07 #4

zensunni

101

100+

I'm pretty sure it's one of the system tables I have to access, which holds the information, but I'm unsure of which one.

Oct 11 '07 #5

iburyak

1,017

Expert 512MB

Let's assume you want to find out space that is taken by sysobjects.
Usually to see space used by the table you execute following statement:

Expand|Select|Wrap|Line Numbers

sp_spaceused sysobjects

Because you need to query the same data I assume you are interested in reserved space by this table in a database. Use query below to select the same data directly from system tables. If you use SQL 2005 add schema name before system table name.

Expand|Select|Wrap|Line Numbers

 Declare @table varchar(50)

select @table = 'sysobjects'
 
select ltrim(str((select sum(reserved)  

                  from sysindexes

                  where indid in (0, 1, 255)  

                  and id = object_id(@table) ) 

                * d.low / 1024.,15,0) +  

       ' KB')

 from master.dbo.spt_values d  

  where d.number = object_id(@table) 

   and d.type = 'E'

Good Luck.

Oct 12 '07 #6

zensunni

101

100+

Thanks all. I used these commands:

To find all the tables:
SELECT * FROM sysobjects WHERE type = 'U'

To find out how much space was used by a table:
exec sp_spaceused '<tablename>'

Oct 30 '07 #7

Similar topics

Finding Table and database size

by: Dan Muey | last post by:

Howdy List! A couple questions about finding the disk space used by a = table/database: 1) First how can I get the size a table is taking up on disk? Is it the 'Data_length' field in: SHOW...

MySQL Database

Database size question

by: dchow | last post by:

Our database size is currently 4G and is incrementing at a rate of 45M/day. What is the max size of a SQL database? And what is the size beyond which the server performance will start to go down?

Microsoft SQL Server

good table design...

by: toedipper | last post by:

Hello, I am designing a table of vehicle types, nothing special, just a list of unique vehicle types such as truck, lorry, bike, motor bike, plane, tractor etc etc For the table design I am...

Microsoft SQL Server

How to reterive the last 5 records in a table?

by: Guru | last post by:

Hi I have a table which contains number of rows. I want to fetch the last 5 records from the table. I know for the first 'n' records we can use FETCH FIRST n ROWS ONLY command. But i want to...

DB2 Database

Table size estimation

by: chettiar | last post by:

Hi, I am wanting to find out the size of a table for which I am using the following query: select c.card * b.columnlength from syscat.tables c, (select sum(a.avgcollen) as columnlength from...

DB2 Database

Finding duplicates

by: Maxi | last post by:

I have 100 tabes in an Access database, every table has 1 filed with 100 names (records), no primary key assigned. I would like to find duplicates. Here is the criteria: The computer should...

Microsoft Access / VBA

Controlling maximal on-disk size of a table

by: Nils Rennebarth | last post by:

I have a table that is essentially a log where new entries are streaming in continually and from time to time I throw old entries away to keep the table from growing. I understand that in...

PostgreSQL Database

Upload file with big size (over 130 MBytes) in ASP.NET. Please help!

by: bienwell | last post by:

Hi all, I developed an web page in ASP.NET to upload file into the server. In the Web.config file, I declared <httpRuntime executionTimeout="1200" maxRequestLength="400000" /> The MAX...

ASP.NET

Finding table size (physical disk space)

by: zensunni | last post by:

How do I find how much space a table takes up on a hard drive? Is there any call you can make that returns a size? If so, how can I display this with asp? Thanks for any help and insights.

Microsoft SQL Server

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Merging data from multiple Excel files

by: ryjfgjl | last post by:

In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...

Data Management

Navigating the Data Structures and Algorithms (DSA)

by: BarryA | last post by:

What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...

Algorithms / Advanced Math

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server