In order to get a list of all tables in the current database, you can filter the sysobjects table by type = ‘U’ e.g.:
select convert(varchar(30),o.name) AS table_name from sysobjects o where type = 'U' order by table_name
In order to get the number of rows of each table, you can use the row_count function. It takes two arguments:
- the database ID – you can get the ID of the current database using the db_id function
- the object ID – it’s the id column in sysobjects
e.g.:
select convert(varchar(30),o.name) AS table_name, row_count(db_id(), o.id) AS row_count from sysobjects o where type = 'U' order by table_name
And in order to get some size information you can use the data_pages function. It will return the number of pages and you can then multiply it by the number of kilobyte per page e.g.:
select convert(varchar(30),o.name) AS table_name, row_count(db_id(), o.id) AS row_count, data_pages(db_id(), o.id, 0) AS pages, data_pages(db_id(), o.id, 0) * (@@maxpagesize/1024) AS kbs from sysobjects o where type = 'U' order by table_name
The first column returned by this statement contains the table name (if you have names longer than 30 characters, you should replace 30 by something higher), the number of rows, the number of data pages, the size in kilobytes.
If you have an ASE version older than 15, the statement above will not work but you can use the statement below instead:
select sysobjects.name, Pages = sum(data_pgs(sysindexes.id, ioampg)), Kbs = sum(data_pgs(sysindexes.id, ioampg)) * (@@maxpagesize/1024) from sysindexes, sysobjects where sysindexes.id = sysobjects.id and sysindexes.id > 100 and (indid > 1) group by sysobjects.name order by sysobjects.name
This will return the table name, number of pages and size in kilobytes.
The second (pre Sybase 15) query only seems to work for tables that have indexes. I was trying to find same info for massive list of tables in scratchdb where folks drop temporary stuff, but many w/out indexes.