Finding the name and data type of a column in SQL

Travis86 · 01-09-2004, 09:45 AM

I'm trying to create a web-based interface to an SQL database. First, it will display then name of the column. Then, if the user is editing a row that is a bool, it'll display a true/false/null listbox, and if it's text, it will display a textbox. phpMyAdmin is able to detect the name and data type, so I assume it is possible, but I can't find how on Goolgle.

Hko · 01-09-2004, 10:01 AM

Code:

DESCRIBE <tablename>

mfeat · 01-09-2004, 10:24 AM

you could also do:

select column_name, data_type
from all_tab_columns
where table_name = 'my_table'

some documentation on all_tab_columns:
http://216.239.37.104/search?q=cache...=en&ie=UTF-8#7

Travis86 · 01-09-2004, 10:25 AM

I think DESCRIBE is what I want since I just want to list the data. If I were going to compare the data to something, I'd probably use all_tab_columns.

Thanks.

On further investigation, all_tab_columns is only for MaxDB databases. I don't have mine set up like that.

Strike · 01-09-2004, 12:34 PM

If you want to use this SQL for many databases (i.e., not MySQL which I'm guessing you are using since afaik DESCRIBE is only in the MySQL spec), then DESCRIBE is not ANSI SQL standard compliant. In fact, there is no ANSI standard way of querying the column name and column data type via SQL that I know of. Why would your program need to know such things?

Travis86 · 01-09-2004, 09:05 PM

I want users to be able to add columns to a table and then input data into the table they created. Since I don't know what the name or type of the the column was that they created, I need to ask the database. At first I thought I could keep the name in the first row, but if it was numerical, that wouldn't work, and I would also need the data type in there, as well. I haven't decided exactly what I will do.

Thanks for the tip about the SQL standard. It's hard to keep track of that sort of stuff.

Strike · 01-10-2004, 09:15 AM

What language are you using? There might be an API call that allows you to obtain the structure of a given table without having to use SQL. Of course, you can just parse the returned values from the DESCRIBE call for MySQL.

Travis86 · 01-10-2004, 11:15 PM

I'm using PHP. I don't think there's an API call with that, I know there is one in Java. I may just have to rewrite it in Java. 'Course I'd have to wait until I learn Java better....

deiussum · 01-11-2004, 10:09 AM

As another alternative that could work for any DB system, you could just keep your own tables which describe your user-created tables for you. Kind of like the MySQL and SQL Server system tables, but in your own way so that it is stored the same for any DB backend.

vasudevadas · 01-13-2004, 12:23 PM

Quote:

Originally posted by mfeat
you could also do:

select column_name, data_type
from all_tab_columns
where table_name = 'my_table'

some documentation on all_tab_columns:
http://216.239.37.104/search?q=cache...=en&ie=UTF-8#7

Does that work for all relational databases, or does that apply only to Oracle's data dictionary, I wonder?

Strike · 01-13-2004, 01:25 PM

PHP eh? You can use mysql_list_fields to get the field names. And then you can use mysql_field_type to get the type

Granted, you can "cheat" and use the MySQL-specific "DESCRIBE" query, but using API calls is the standard way of doing it across languages and RDBMSes.

jchristman · 08-15-2004, 04:20 PM

I have been trying to use this function, but I have never been able to can any one show me some actual working code where the function mysql_list_fields is working and how they used it.