Results 1 to 10 of 10

Thread: Table size

  1. #1
    Join Date
    Nov 2003
    Posts
    68

    Cool Unanswered: Table size

    Hello,
    How can i get the size of a table in sqserver 2000 ?
    How can read a image field ?
    Thanks for this answer.

  2. #2
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    Use enterprise manager

    Highlight Database node , view , taskpad , table info


    I don't think you can directly read the image from the database as it is not stored there. Only a pointer to the file system is stored in image field datatypes. This is the same for textcolumns as well.

    hth

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Database / Table Sizes: sp_spaceused
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    sp_spaceused is a good way to find out but answer this then

    how do you find out the exact size of all the different indexes on a table ?

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by aldo_2003
    sp_spaceused is a good way to find out but answer this then

    how do you find out the exact size of all the different indexes on a table ?
    You get it in INDEX_SIZE column of the output as a total.

    And who told you that IMAGE and TEXT values are not stored in the database? They are, in TEXT/IMAGE pages.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2004
    Posts
    60

    Post

    Philio, here's what i use (see attached)...sp_spaceused is good, but i tend to lean towards my homegrown stuff just 'cause i'm difficult that way.
    Peace.
    Attached Files Attached Files

  7. #7
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149
    "all the different indexes" - need to read the question son....

    So if you had one clustered and two non-clustered indexes how could you find out the space used by each?

    My point being is that finding the size of a table and being able to attribute this size to the sum of it's parts is not as straight forward as you might think.

    Point taken re: the text/image pages ....

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by aldo_2003
    need to read the question son
    I read the question, son, and it's below for your convenience
    Quote Originally Posted by philio
    Hello,
    How can i get the size of a table in sqserver 2000 ?
    How can read a image field ?
    Thanks for this answer.
    Using task pad to retrieve sizes vs. sp_spaceused??? And you still have doubts about it?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Nov 2003
    Location
    Edinburgh
    Posts
    149

    Smile

    Point taken,

    I should try and raise my own threads rather than muscle into others...

    Yeah , Taskpad and Em can be a bit bit dodge....

    Had this problem before - sp_spaceused is great unless you want to attribute the size of the table down to each of its indexes and the data it holds.

    As far as I can see SQL server does not present an easy way for you to do this without buggering about with the sysindexes table.

    Taskpad can provide info but how much do you trust it?????

    Any ideas ????

    p.s been a while since been on the site , need to remember my manners

  10. #10
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Hey, it's all cool!


    used field in sysindexes will give you the specifics of each index in terms of size.

    To get there I usually use sp_helpindex for each index in the table, and go from there.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •