Axter
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?
What is the SQL command to list all columns in a table?
depends on the link, see syscolumns
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?
If so, is there a command that is more general, and would apply to most database types?
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.
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.
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.
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?
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')
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')
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?
select xf$name from x$file
So the commands that PeteLong posted will not work on Btrieve. Is that correct?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
select * from sysobjects