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.
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