Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007
    Location
    Mumbai,India
    Posts
    13

    Question Unanswered: Help regarding tables needed.

    Hi,
    I want help,
    Issue is i want to display table names and number of rows in each table present, in the database.

    I was able to do this in sql server with the following quey i made

    SELECT a.name tablename, max(b.rows) rowcount
    from sysobjects a, sysindexes b
    where a.xtype = 'U'
    and b.id = OBJECT_ID(a.name)
    group by a.name
    order by 2 Desc

    But i am not able to do same in sybase database as the sysindexes table dosent have any 'rows' coloums or from which i can get the count.

    Can you help me with this?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Look at the sp_spaceused stored procedure to see how to get the rowcount

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    This works on ASE 12.x
    Code:
    select u.name owner, o.name name
    ,rowtotal   = sum(rowcnt(i.doampg))
    ,reservedKB = sum((reserved_pgs(i.id, i.doampg) +
    		   reserved_pgs(i.id, i.ioampg)) * (low / 1024) )
    ,dataKB     = sum( data_pgs(i.id, i.doampg) * (low / 1024))
    ,indexKB    = sum(data_pgs(i.id, i.ioampg) * (low / 1024))
    ,unusedKB   = sum( ((reserved_pgs(i.id, i.doampg) +
    		     reserved_pgs(i.id, i.ioampg)) -
    		    (data_pgs(i.id, i.doampg) +
    		     data_pgs(i.id, i.ioampg)) ) * (low / 1024))
    from sysindexes i 
    join sysobjects o 
      on o.type = 'U' 
     and o.id = i.id
    join sysusers u 
      on o.uid = u.uid
    join master.dbo.spt_values d 
      on d.number = 1
     and d.type = 'E'
    group by u.name, o.name
    order by rowtotal desc, reservedKB desc, owner, name

  4. #4
    Join Date
    Aug 2007
    Location
    Mumbai,India
    Posts
    13
    Hi,

    Thanks a lot man that was exactly what i needed.

Posting Permissions

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