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?
LVL 30
AxterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pete LongTechnical ConsultantCommented:
What is the SQL command to list all tables in a database?

select * from sysobjects
0
Pete LongTechnical ConsultantCommented:
depends on the link, see syscolumns
0
AxterAuthor Commented:
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?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AxterAuthor Commented:
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.
0
mirtheilCommented:
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.  
0
AxterAuthor Commented:
>>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?
0
mirtheilCommented:
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')
0
AxterAuthor Commented:
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?
0
mirtheilCommented:
Correct. To list all tables (including system tables) use: select xf$name from x$file
The commands Petelong will not work with Pervasive.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AxterAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.