Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: row count in a table

    Hi all,

    I have created a proc in sqlserver which counts the no of rec in each table
    in the db, I want to create the same in sybase, as I am new to sybase

    please help me in converting this sp in to sybase, as select count(*) will be time consuming for me as the no of tables are 1000 +


    SQL Server proc. and query to get the results.


    CREATE
    FUNCTION dbo.UDF_Tab_Row_count (
    @sTableName
    sysname -- Table to retrieve Row Count
    )
    RETURNS INT -- Row count of the table, NULL if not found.
    AS
    BEGIN
    DECLARE @nRowCount INT -- the rows
    DECLARE @nObjectID INT -- Object ID
    SET @nObjectID = OBJECT_ID(@sTableName)
    -- Object might not be found
    IF @nObjectID is null RETURN NULL
    SELECT TOP 1 @nRowCount = rows
    FROM sysindexes
    WHERE id = @nObjectID AND indid < 2
    RETURN @nRowCount
    END

    ----- end of function----

    SELECT
    [name], dbo.UDF_Tab_Row_count ([name]) as [Row Count]
    FROM sysobjects
    WHERE type='U' and name != 'dtproperties'
    ORDER BY [name]


    --out put will be table name and number of records in each table.----
    name Row Count
    Batting 13
    inv 4
    Person 11

  2. #2
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    The following might work for you:
    Code:
    select  o.name, sum(rowcnt(i.doampg))
    from    sysobjects o, sysindexes i
    where   o.id=i.id
            and o.type='U'
            and not ( o.sysstat2 & 1024 = 1024 )
    group by o.name
    order by o.name

  3. #3
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    Mike

    That code will not work, it SUMs the indices for o.name (depending on the version, if table_a has 1 million rows, and 6 indices, you will get "table_a 6,000,000").

    Aakcse

    This is for Pre-15.0:
    Code:
    SELECT  o.name, 
            ROWCNT(i.doampg)
        FROM  sysobjects o,
              sysindexes i
        WHERE o.id = i.id
        AND   o.sysstat2 & 1024 = 0         -- not remote
        AND   o.sysstat2 & 2048 = 0         -- not proxy
        AND   (i.indid = 0 OR i.indid = 1)  -- Heap or ClustIdx only
        ORDER BY o.name
    15.0:
    Code:
    SELECT  name, 
            ROW_COUNT(DB_ID(), id)
        FROM  sysobjects
        WHERE type = "U"
        AND   sysstat2 & 1024 = 0  -- not remote
        AND   sysstat2 & 2048 = 0  -- not proxy
        ORDER BY name
    You do not need to code a function; the ROWCNT() or ROW_COUNT() function is already supplied by Sybase.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Derek Asirvadem
    That code will not work, it SUMs the indices for o.name (depending on the version, if table_a has 1 million rows, and 6 indices, you will get "table_a 6,000,000").
    Thanks for that - didn't realise

    Would it work if I just used the count from one of the indexes?
    (I don't have a Sybase server to try on at the moment)

  5. #5
    Join Date
    Mar 2009
    Location
    Sydney, Australia
    Posts
    258
    That question is already answered in the code submitted by me.
    Regards
    Derek Asirvadem (Formerly DerekA)
    Information Architect / Senior Sybase DBA
    Copyright 2009 Software Gems Pty Ltd

    I answer questions from the Original Poster only. If you have a genuine question, as the moderators have requested, start a new thread.

    http://www.softwaregems.com.au

Posting Permissions

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