Simple query for generating database metrics?

Give a database (Sybase) with many tables, I would like to write a SQL query that will c开发者_JS百科alculate, for each table, the number of rows and the number of columns.

Unfortunately, my SQL is a bit rusty. I can generate the table names:

select name from sysobjects where type = 'U'   

but how to bind the databases returned to T in:

select count(*) from T

is beyond me. Is it even possible to do this kind of thing?

I don't use Sybase, but the online docs indicate the row counts are in systabstats and the columns are in syscolumns.

SELECT sysobjects.name, 
      (SELECT COUNT(*) FROM syscolumns WHERE syscolumns.id = sysobjects.id) AS cols,
       systabstats.rowcnt
    FROM sysobjects
    JOIN systabstats 
    ON (sysobjects.id = systabstats.id AND sysobjects.type = 'U' AND systabstats.indid = 0)

As fredt has given the answer, I'll just provide some extra info.

The built in procedure sp_spaceused "tablename" will give you the number of rows for a selected table, along with details of how much storage space it's using. Used without the parameter it provides storage usage for the current database as a whole.

You can look at the SQL in the various system stored procedures to see where they get their information from. sp_spaceused and sp_help would both be useful for you in this. They live in the sybsystemprocs database. Just be careful not to modify any of those procedures.

There are various versions of a stored procedure called sp_rowcount floating around the internet, that provide what you ask for (rowcount anyway), but inside they are equivalent to the select statement from fredt. The one I use provides index count and table locking scheme. I don't recall exactly where I got mine so don't want to just distribute it in case I upset someone's copyright.