get all the columns on all the tables in information_schema
This is a discussion on get all the columns on all the tables in information_schema within the MySQL forums, part of the Database Server Software category; --> >> > ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, >> > COLUMN_DEFAULT >> > FROM INFORMATION_SCHEMA.COLUMNS >> > WHERE table_name = 'table1', ...
| |||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
05-29-2008, 10:41 AM
| ||||
| ||||
 Re: get all the columns on all the tables in information_schema >> > ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, >> > 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.      |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema Gordon Burditt wrote: >>> > ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, >>> > 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. 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. |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema On Wed, 28 May 2008 13:51:56 -0700 (PDT), JRough wrote: > 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' > > ---------------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 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 |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema On May 28, 2:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> wrote: > Gordon Burditt wrote: > >>> > ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, > >>> > 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. > > 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. Paul 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, |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema On May 28, 2:19 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote: > On Wed, 28 May 2008 13:51:56 -0700 (PDT), JRough wrote: > > 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' > > > ---------------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 > > 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 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' tia, |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema JRough wrote: > On May 28, 2:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > wrote: >> Gordon Burditt wrote: >>>>>> ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, >>>>>> 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. >> 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. > > Paul > 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! -- ================== Remove the "x" from my email address Jerry Stuckle JDS Computer Training Corp. jstucklex@attglobal.net ================== |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema On 29 May, 03:06, Jerry Stuckle <jstuck...@attglobal.net> wrote: > JRough wrote: > > On May 28, 2:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > > wrote: > >> Gordon Burditt wrote: > >>>>>> ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, > >>>>>> 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. > >> 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. > > > Paul > > 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! I didn't miss it! The OP did! |
|
05-29-2008, 10:41 AM
| |||
| |||
| Re: get all the columns on all the tables in information_schema On 29 May, 08:53, Captain Paralytic <paul_laut...@yahoo.com> wrote: > On 29 May, 03:06, Jerry Stuckle <jstuck...@attglobal.net> wrote: > > > > > JRough wrote: > > > On May 28, 2:05 pm, "Paul Lautman" <paul.laut...@btinternet.com> > > > wrote: > > >> Gordon Burditt wrote: > > >>>>>> ----------------SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, > > >>>>>> 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. > > >> 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. > > > > Paul > > > 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! > > I didn't miss it! The OP did! Oops, Google Groups is missing a post! |
|
06-02-2008, 01:34 PM
| |||
| |||
| Re: get all the columns on all the tables in information_schema JRough wrote: > On May 28, 2:19 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote: >> On Wed, 28 May 2008 13:51:56 -0700 (PDT), JRough wrote: >> > 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' >> >> > ---------------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 >> >> 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 > > 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' > > 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! |
|
06-02-2008, 01:34 PM
| ||||
| ||||
| Re: get all the columns on all the tables in information_schema On Wed, 28 May 2008 16:08:59 -0700 (PDT), JRough <jlrough@yahoo.com> wrote: >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/...ate-table.html >tia, YW, HTH -- ( Kees ) c[_] Too much capitalism does not mean too many capitalists, but too few capitalists (GK Chesterton) (#473) |
Linear Mode
