Post reply

Tables with max size

  • May 13, 2005 at 8:57 am

    #87548

    Hi all,

    I would like to clean some our tables (delete old records we don’t use for a while). So I am wondering is there a way to find somehow tables size (number of records) not checking “Property”- Rows for every table?

    Thanks

  • May 13, 2005 at 9:06 am

    #558743

    If they all have indexes, you can query sysobjects for types of "U" and join that with the sysindexes to get the row counts.

  • May 13, 2005 at 9:08 am

    #558746

    This is what he means :

    Select O.Name as TableName, max(I.rowcnt) as Total from dbo.sysindexes I inner join dbo.SysObjects O on i.id = O.id and O.Status >=0 and O.XType = 'U' and I.indid < 2 Group by I.id, O.Name order by Total Desc

  • May 13, 2005 at 9:34 am

    #558764

    Thanks a lot!

    And there is no direct way to find table size (Mb) besides manual calculation using rows number I've got?

  • May 13, 2005 at 9:36 am

    #558765

    Search the script section on this site for table size and you'll get a few exemples.

  • May 13, 2005 at 9:46 am

    #558768

    Thanks again.

  • May 13, 2005 at 2:10 pm

    #558843

    Run this query in the desired database will give you your required information"

    sp_msforeachtable 'sp_spaceused ''?'''

     

    hope this will help.

    Thanks.

  • May 13, 2005 at 2:13 pm

    #558844

    Also, in Enterprise Manager, Task Pad view, Table Info, you can see the estimated (from sysindexes) size in KB.

    Steve

  • May 13, 2005 at 3:06 pm

    #558852

    Cool! Thanks a lot for both tips!.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply