Link to home
Start Free TrialLog in
Avatar of Axter
AxterFlag for United States of America

asked on

List all tables in a database

What is the SQL command to list all tables in a database?
What is the SQL command to list all columns in a table?
Avatar of Pete Long
Pete Long
Flag of United Kingdom of Great Britain and Northern Ireland image

What is the SQL command to list all tables in a database?

select * from sysobjects
depends on the link, see syscolumns
Avatar of Axter

ASKER

Is the above SQL command unique to Btrieve?

If so, is there a command that is more general, and would apply to most database types?
Avatar of Axter

ASKER

I'm writing a program that is suppose to work with both IBM UDB, Btrieve, and Pervasive SQL.

So I'm looking for commands that will work with all threee database types.

I just tried sysobjects on IBM UDB, and it failed.
It would also fail on Pervasive. For Pervasive to get the list of table name, you would use:
select xf$name from x$file
THen to list the columns in a table you would use:
SELECT xf$name, xe$name FROM "X$Field", x$file where xe$file = xf$id group by xf$name, xe$name
You can safely disregard any columns that start with "NN_" and have a datatype of 227.  
Avatar of Axter

ASKER

>>select xf$name from x$file

Is xf$name a constant in above command?

Do I replace "file" with the name of a file?

Do you have any good links with more info?

I notice there's no Pervasive SQL topic area.
Does Pervasive SQL use BTrieve as part of it's DB engine?
Are they associated with each other some how?
Pervasive.SQL is the current version of Btrieve.  "xf$name" is the name of the column in the system table X$FILE (you can double click it within the PCC and see what's all's there).  
To get a list of columns for a specific table, you'd need to look up the table name in the X$FILE to get the XF$ID which is in the X$FIELD table as XE$FILE column.  So for example:
select XE$NAME from X$FIELD where XE$FILE = (select XF$ID from X$FILE where XF$NAME = 'myTableName')
Avatar of Axter

ASKER

So just to verify, the following EXACT command will list all the tables?
select xf$name from x$file

So the commands that PeteLong posted will not work on Btrieve.  Is that correct?
ASKER CERTIFIED SOLUTION
Avatar of Mirtheil
Mirtheil
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Axter

ASKER

Thanks