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

23671

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

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++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

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

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The...

Networking - Hardware / Configuration