Row count column count in all tables in Sybase database

Question

I have a database name ATs . In this database 150 tables. I want to create a statement that return the row count and column count all tables on the database .

I have created a store procedure For SQL SERVER 2008 but i don't know how to write this script for Sybase.

Answer 1

Sybase ASA has a bunch of system tables providing you with information about the structure of your database. The two tables that are of interest for you are SYSTABLE (all tables) and SYSCOLUMN (all columns).

I tried this quick and dirty stored procedure that works for me (on the rather aged ASA version 8!). It creates a temporary table and a cursor to iterate over all tables. For every table the table name, number of columns and number of rows are inserted into the temp table and finally returned.

(Hint: the tablefilter allows to return only a subset of the whole database, if you have many tables.)

CREATE PROCEDURE Usr_TableStats(in par_tablefilter char(100))
RESULT (tablename varchar(255), number_of_cols int, number_of_rows int)
BEGIN

    declare err_notfound exception for sqlstate value '02000';
    declare @table_id  integer;
    declare @tablename varchar(100);
    declare @cols      integer;
    declare @sql       varchar(300);

    declare tables no scroll cursor for select table_id, table_name from sys.systable where table_type = 'BASE' and table_name like par_tablefilter || '%' order by table_name;

    create table #tablestats (
        tablename       varchar(100) not null,
        number_of_cols  int not null default 0,
        number_of_rows  int not null default 0
    );

    open tables;

    LoopTables: loop

        fetch next tables into @table_id, @tablename;

        if sqlstate = err_notfound then
            leave LoopTables
        else
            SELECT COUNT(column_id) INTO @cols FROM SYSCOLUMN WHERE table_id = @table_id;
            set @sql= 'INSERT INTO #tablestats SELECT ''' || @tablename || ''', ' || @cols || ', COUNT(*) FROM ' || @tablename || ';';
            EXECUTE IMMEDIATE WITH QUOTES @sql;
        end if

    end loop LoopTables;

    close tables;

    SELECT tablename, number_of_cols, number_of_rows FROM #tablestats;

END

Call it in iSQL like this:

CALL Usr_TableStats('%'); -- all tables
CALL Usr_TableStats('ADDRESS%'); -- only tables starting with ADDRESS