Asking the Database to Describe Itself 3

[Genimuse]

I know from memory that there are statements you can use (SELECTs, I'm pretty sure) to ask the database to list its tables, and to ask it to describe a table (column names and types/sizes), but apparently my Google- and Tek-Tips-Fu isn't strong enough, because I can't seem to find any reference to the commands.

Working with this customer's undocumented database is a huge hassle, but if I could at least get it to describe itself, it would be a giant step in the right direction.

Anyone know what I'm talking about, or have my years invented memories? Thanks in advance.

 

[Zathras]

Each database has its own way of doing it.

You didn't specify what dbms you are using, so it's pretty much a guess from here.

you could try
SELECT * FROM CATALOG

or if you are using SQL Server, there are stored procedures such as
sp_tables
and
sp_columns [tablename]

If you tell us what your database brand is, perhaps you can get a more definitive answer.

 

[JarlH]

Table information can be found in the INFORMATION_SCHEMA views. (e.g. INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS)

Specified by the ISO/ANSI standard.

 

[Genimuse]

The database is Advantage, which uses something called Streamline SQL (which I have documentation for). I don't have the DBMS itself, just a database (which is being called by an application) and an ODBC connection. Thus I was hoping for a fairly standardized (ANSI) way of requesting the info from the DB.

I'll give your suggestions a try, thank you both.

 

[Genimuse]

Oh, and probably obviously, the database documentation didn't state how to request the information I want.

 

[PHV]

And what about this site ?

http://solutions.advantagedatabase.com/

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Genimuse]

That's where I got the documentation from. Unfortunately support all assumes you have a copy of the DBMS software, which I'd need to buy... just to get this information (since I'd be using ODBC for all of my needs). But obviously if that's the only way...

 

[Genimuse]

Oh, and I'm working with a version 7 database, which is no longer for sale or supported. I appreciate the suggestion.

 

[PHV]

If you have MS-Access and the ODBC driver you may play with linked tables ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Genimuse]

I did manage to yank it into Access (and link to it, separately). I managed to get access to the tables and format, but with nearly 50 tables and tons of columns in many of them, and a need to document and understand the schema, I was hoping to not to need to go through each field one at a time to see the length of text or the type of number.

The commands suggested above don't seem to be responded to by either Advantage or Access. Maybe I should ask over in the Access forum how to get a descriptive schema of the entire database.

Thanks again!