get all the columns on all the tables in information_schema
JRough
database. I want to get the column names from all the tables. I can
figure out how to write the query for one table at a time. To save
time I would like to write it on all the tables but when I add tables
separated by a comma I get an error.
tia,
----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE,
COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'table1', 'table2'
AND table_schema = 'myDatabase'
Jerry Stuckle
... WHERE table_name IN ('table1', 'table2') ...
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Peter H. Coffin
table_schema = 'myDatabase'
--
Mares eat oats, and does eat oats, and little lambs eat ivy,
A kid will eat ivy too, wouldn't you?
Paul Lautman
There is no such thing as PHPAdmin.
Do you mean phpMyAdmin?
JRough
It would be nice if this would work because then I wouldn't have to
type in all the table names however, it does not work. This is the
error message and I double checked the database name spelling etc. It
stops where it starts processing the name of the database.
----------error-----
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = 'dhan' at line 1
JRough
wrote:
Yes I am in phpMyAdmin/databases and I am in the SQL view with the
information_schema database selected.
Paul Lautman
What is the IT that you are referring to? If you are referring to
SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE,
COLUMN_DEFAULT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
table_schema = 'myDatabase'
then it works fine for me, in which case, you have made a mistake somewhere.
JRough
> JRough wrote:
> > I am writing this query in PHPAdmin on the information_schema
> > database. I want to get the column names from all the tables. I can
> > figure out how to write the query for one table at a time. To save
> > time I would like to write it on all the tables but when I add tables
> > separated by a comma I get an error.
>
> > tia,
>
> > ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE,
> > COLUMN_DEFAULT
> > FROM INFORMATION_SCHEMA.COLUMNS
> > WHERE table_name = 'table1', 'table2'
> > AND table_schema = 'myDatabase'
>
> ... WHERE table_name IN ('table1', 'table2') ...
>
> --
the correct database name and table names:
COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name IN (
'Table1', 'Table2'
) AND table_schema = 'MyDatabase'
---------------error------------
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name
IN (
'INTERNAL_' at line 1
tia,
Paul Lautman
According to your first post, you knew all about normalising and SELECT
queries.
On 23rd May, you promised to actually learn the basics of SQL.
You seem to be trying to run before you can walk. Please do us all a favour
and read the sodding manual and learn how to program SQL.
Paul Lautman
> On May 27, 5:39 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> JRough wrote:
>> > I am writing this query in PHPAdmin on the information_schema
>> > database. I want to get the column names from all the tables. I
>> > can figure out how to write the query for one table at a time. To
>> > save time I would like to write it on all the tables but when I
>> > add tables separated by a comma I get an error.
>>
>> > tia,
>>
>> > ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE,
>> > COLUMN_DEFAULT
>> > FROM INFORMATION_SCHEMA.COLUMNS
>> > WHERE table_name = 'table1', 'table2'
>> > AND table_schema = 'myDatabase'
>>
>> ... WHERE table_name IN ('table1', 'table2') ...
>>
>> --
> The SQL doesn't appear to work.I tried it several times and included
> the correct database name and table names:
>
> COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name IN (
> 'Table1', 'Table2'
> ) AND table_schema = 'MyDatabase'
Please can you post the FULL query that you are executing. We can't tell you
anything if you only post part of it.
Gordon Burditt
>> > COLUMN_DEFAULT
>> > FROM INFORMATION_SCHEMA.COLUMNS
>> > WHERE table_name = 'table1', 'table2'
>> > AND table_schema = 'myDatabase'
Note: above incorrect query has 5 lines.
>COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name IN (
>'Table1', 'Table2'
>) AND table_schema = 'MyDatabase'
You're missing stuff off the beginning of this query, like the SELECT
keyword.
Paul Lautman
I don't know about you, but I'm getting pretty tired of this guy. In one of
his first posts he claimed to be able to normalise databases.
He also claimed to know how to do a SELECT query and ORDER BY.
He promised to learn SQL. He doesn't seem to be able to apply any intuition
to what he sees.
It feels like he doesn't want to learn anything, he just wants us to do the
work for him.
Peter H. Coffin
You might want to put SELECT back on the front.
--
"'I'm not sleeping with a jr. high schooler! I have a life-sized doll that
looks like one.' Uh huh. That sounds SO much less pathetic."
-- Piro's Conscience www.megatokyo.com
Jerry Stuckle
> On May 28, 2:05 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> I do know how to create an ERD. I do know some SQL basics but I did
> miss the SELECT statement. Thanks. The reason I did is I'm very
> confused with information_schema and how to get out of it what I
> want. YOu are right I did miss that.
> Sorry,
How could you miss the SELECT statement? It's the most basic statement
in SQL!
Captain Paralytic
I didn't miss it! The OP did!
Captain Paralytic
Oops, Google Groups is missing a post!
Paul Lautman
> okay? I don't want to update the wrong records I just want to insert
> comments on all the tables.
>
> UPDATE INFORMATION_SCHEMA
> SET TABLE_COMMENT = 'myComment'
> WHERE TABLE_NAME = 'myTable' AND TABLE_schema ='myDatabase'
>
> tia,
No it's not, you should be using the correct (ALTER TABLE) command. You
would know this if you had done as you said you would and learnt the basics!
Kees Nuyt
>If it isn't too much trouble can you tell me if this UPDATE query is
>okay? I don't want to update the wrong records I just want to insert
>comments on all the tables.
>
>UPDATE INFORMATION_SCHEMA
>SET TABLE_COMMENT = 'myComment'
>WHERE TABLE_NAME = 'myTable' AND TABLE_schema ='myDatabase'
It's not a good idea to try to update the INFORMATION_SCHEMA.
You can try:
ALTER TABLE tablename COMMENT = 'your comment';
to the same effect and with less risk.
See also:
http://dev.mysql.com/doc/refman/5.0/en/alter-table.html
which refers to
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
>tia,
YW, HTH
--
( Kees
)
c[_] Too much capitalism does not mean too many capitalists,
but too few capitalists (GK Chesterton) (#473)
Captain Paralytic
Instead of trying to play with the INFORMATION_SCHEMA tables, since
you have no knowledge of SQL, why not use the SHOW commands (the
natural partner for the CREATE & ALTER commands) which together make
up the proper way to interact with INFORMATION_SCHEMA?