How to put table definition into another table using SQL?

How to put table definition into another table using SQL?

am 11.05.2010 17:36:29 von mos

I'd like to get the field names and data types of a table, say TableX, and
put it into TableDef using nothing but SQL. I know I can list the table
definition using "Describe Table" and then loop through the results and
insert the first two columns Field and Type into TableDef, but is there a
way to do it using just SQL?

Example:
Describe TableX:

First_Name Char(15) ....
Last_Name Char(20) ...
Start_Date Date ..
Salary Double ..


And I'd like TableDef to have these rows:

ColName ColType
--------------- --------------
First_Name Char(15)
Last_Name Char(20)
Start_Date Date
Salary Double

Is there a way to do this with one SQL statement? I'm really looking for
the MySQL internal table where it stores the table definitions.

TIA
Mike


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to put table definition into another table using SQL?

am 11.05.2010 17:46:53 von nuno.tavares

--------------enigCD8BA44D3859249B2CA96C30
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

create table TableDef like TableX ?

--=20
Nuno Tavares
DRI, Consultoria Inform=E1tica
Telef: +351 936 184 086



mos escreveu:
> I'd like to get the field names and data types of a table, say TableX,
> and put it into TableDef using nothing but SQL. I know I can list the
> table definition using "Describe Table" and then loop through the
> results and insert the first two columns Field and Type into TableDef,
> but is there a way to do it using just SQL?
>=20
> Example:
> Describe TableX:
>=20
> First_Name Char(15) ....
> Last_Name Char(20) ...
> Start_Date Date ..
> Salary Double ..
>=20
>=20
> And I'd like TableDef to have these rows:
>=20
> ColName ColType
> --------------- --------------
> First_Name Char(15)
> Last_Name Char(20)
> Start_Date Date
> Salary Double
>=20
> Is there a way to do this with one SQL statement? I'm really looking fo=
r
> the MySQL internal table where it stores the table definitions.
>=20
> TIA
> Mike
>=20
>=20


--------------enigCD8BA44D3859249B2CA96C30
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAkvpe+0ACgkQncah1swpSqhujgCeLmm/CpcMVLcRwCaqR9hf JIz/
JzIAoJrl2Uni7CFGRmNWW3dthfF+9MHL
=kTYv
-----END PGP SIGNATURE-----

--------------enigCD8BA44D3859249B2CA96C30--

Re: How to put table definition into another table using SQL?

am 11.05.2010 17:52:17 von Phil

--0016e649d7c68c83b20486538296
Content-Type: text/plain; charset=ISO-8859-1

create table TableDEF like TableX;

Or am I missing something ??

On Tue, May 11, 2010 at 11:36 AM, mos wrote:

> I'd like to get the field names and data types of a table, say TableX, and
> put it into TableDef using nothing but SQL. I know I can list the table
> definition using "Describe Table" and then loop through the results and
> insert the first two columns Field and Type into TableDef, but is there a
> way to do it using just SQL?
>
> Example:
> Describe TableX:
>
> First_Name Char(15) ....
> Last_Name Char(20) ...
> Start_Date Date ..
> Salary Double ..
>
>
> And I'd like TableDef to have these rows:
>
> ColName ColType
> --------------- --------------
> First_Name Char(15)
> Last_Name Char(20)
> Start_Date Date
> Salary Double
>
> Is there a way to do this with one SQL statement? I'm really looking for
> the MySQL internal table where it stores the table definitions.
>
> TIA
> Mike
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=pchapman@nc.rr.com
>
>


--
Distributed Computing stats
http://stats.free-dc.org

--0016e649d7c68c83b20486538296--

Re: How to put table definition into another table using SQL?

am 11.05.2010 17:53:38 von Anirudh Sundar

--0016e64718e85edba7048653872a
Content-Type: text/plain; charset=ISO-8859-1

Hi Mike,

Did you try this :-

Create table TableDef select * from TableX where 1 = 2;

If you give this statement you will create the new table "TableDef" with the
same variables and datatypes as table "TableX"

Let me know if it works....

Cheers,
Anirudh Sundar

On Tue, May 11, 2010 at 9:06 PM, mos wrote:

> I'd like to get the field names and data types of a table, say TableX, and
> put it into TableDef using nothing but SQL. I know I can list the table
> definition using "Describe Table" and then loop through the results and
> insert the first two columns Field and Type into TableDef, but is there a
> way to do it using just SQL?
>
> Example:
> Describe TableX:
>
> First_Name Char(15) ....
> Last_Name Char(20) ...
> Start_Date Date ..
> Salary Double ..
>
>
> And I'd like TableDef to have these rows:
>
> ColName ColType
> --------------- --------------
> First_Name Char(15)
> Last_Name Char(20)
> Start_Date Date
> Salary Double
>
> Is there a way to do this with one SQL statement? I'm really looking for
> the MySQL internal table where it stores the table definitions.
>
> TIA
> Mike
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
>
>

--0016e64718e85edba7048653872a--

Re: How to put table definition into another table using SQL?

am 11.05.2010 18:06:46 von mos

At 10:53 AM 5/11/2010, Anirudh Sundar wrote:
>Hi Mike,
>
>Did you try this :-
>
>Create table TableDef select * from TableX where 1 = 2;
>
>If you give this statement you will create the new table "TableDef" with
>the same variables and datatypes as table "TableX"
>
>Let me know if it works....
>
>Cheers,
>Anirudh Sundar


Anirudh,
Thanks for the quick reply. I use something like that all the time
when I want to copy a table's definition. Actually I use "Create table2
select * from table1 limit 0". But what I'm looking for here is the actual
definition of TableX to be stored in TableDef.

So each row of TableDef represents a column definition from TableX.

>And I'd like TableDef to have these rows:
>
>ColName ColType
>--------------- --------------
>First_Name Char(15)
>Last_Name Char(20)
>Start_Date Date
>Salary Double

The above data are actually "rows" from TableDef. TableDef only has 2
columns "ColName" and "ColType". The TableDef rows are of course the
columns making up TableX.

Mike


>On Tue, May 11, 2010 at 9:06 PM, mos
><mos99@fastmail.fm> wrote:
>I'd like to get the field names and data types of a table, say TableX, and
>put it into TableDef using nothing but SQL. I know I can list the table
>definition using "Describe Table" and then loop through the results and
>insert the first two columns Field and Type into TableDef, but is there a
>way to do it using just SQL?
>
>Example:
>Describe TableX:
>
>First_Name Char(15) ....
>Last_Name Char(20) ...
>Start_Date Date ..
>Salary Double ..
>
>
>And I'd like TableDef to have these rows:
>
>ColName ColType
>--------------- --------------
>First_Name Char(15)
>Last_Name Char(20)
>Start_Date Date
>Salary Double
>
>Is there a way to do this with one SQL statement? I'm really looking for
>the MySQL internal table where it stores the table definitions.
>
>TIA
>Mike
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To
>unsubscribe:
>http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to put table definition into another table using SQL?

am 11.05.2010 18:23:50 von Carlos Proal

Hi Mike, you need to query the information_schema schema/database that
contains the database dictionary.
You can read the manual or a beautiful diagram here:
http://www.xcdsql.org/Misc/MySQL_INFORMATION_SCHEMA.html

Have a nice day
Carlos Proal

On 5/11/2010 11:06 AM, mos wrote:
> At 10:53 AM 5/11/2010, Anirudh Sundar wrote:
>> Hi Mike,
>>
>> Did you try this :-
>>
>> Create table TableDef select * from TableX where 1 = 2;
>>
>> If you give this statement you will create the new table "TableDef"
>> with the same variables and datatypes as table "TableX"
>>
>> Let me know if it works....
>>
>> Cheers,
>> Anirudh Sundar
>
>
> Anirudh,
> Thanks for the quick reply. I use something like that all the time
> when I want to copy a table's definition. Actually I use "Create
> table2 select * from table1 limit 0". But what I'm looking for here is
> the actual definition of TableX to be stored in TableDef.
>
> So each row of TableDef represents a column definition from TableX.
>
>> And I'd like TableDef to have these rows:
>>
>> ColName ColType
>> --------------- --------------
>> First_Name Char(15)
>> Last_Name Char(20)
>> Start_Date Date
>> Salary Double
>
> The above data are actually "rows" from TableDef. TableDef only has 2
> columns "ColName" and "ColType". The TableDef rows are of course the
> columns making up TableX.
>
> Mike
>
>
>> On Tue, May 11, 2010 at 9:06 PM, mos
>> <mos99@fastmail.fm> wrote:
>> I'd like to get the field names and data types of a table, say
>> TableX, and put it into TableDef using nothing but SQL. I know I can
>> list the table definition using "Describe Table" and then loop
>> through the results and insert the first two columns Field and Type
>> into TableDef, but is there a way to do it using just SQL?
>>
>> Example:
>> Describe TableX:
>>
>> First_Name Char(15) ....
>> Last_Name Char(20) ...
>> Start_Date Date ..
>> Salary Double ..
>>
>>
>> And I'd like TableDef to have these rows:
>>
>> ColName ColType
>> --------------- --------------
>> First_Name Char(15)
>> Last_Name Char(20)
>> Start_Date Date
>> Salary Double
>>
>> Is there a way to do this with one SQL statement? I'm really looking
>> for the MySQL internal table where it stores the table definitions.
>>
>> TIA
>> Mike
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives:
>> http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
>>
>>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to put table definition into another table using SQL?

am 11.05.2010 18:24:40 von Anirudh Sundar

--0016e68e8ba5596ac6048653f667
Content-Type: text/plain; charset=ISO-8859-1

Hey Mike,

I do not see a possibity of a quick response for this....

We need to work on a query (if at all it is possible to be done in a query).

Will get back soon on this.

Cheers,
Anirudh Sundar



On Tue, May 11, 2010 at 9:36 PM, mos wrote:

> At 10:53 AM 5/11/2010, Anirudh Sundar wrote:
>
>> Hi Mike,
>>
>> Did you try this :-
>>
>> Create table TableDef select * from TableX where 1 = 2;
>>
>> If you give this statement you will create the new table "TableDef" with
>> the same variables and datatypes as table "TableX"
>>
>> Let me know if it works....
>>
>> Cheers,
>> Anirudh Sundar
>>
>
>
> Anirudh,
> Thanks for the quick reply. I use something like that all the time when
> I want to copy a table's definition. Actually I use "Create table2 select *
> from table1 limit 0". But what I'm looking for here is the actual definition
> of TableX to be stored in TableDef.
>
> So each row of TableDef represents a column definition from TableX.
>
>
> And I'd like TableDef to have these rows:
>>
>> ColName ColType
>> --------------- --------------
>> First_Name Char(15)
>> Last_Name Char(20)
>> Start_Date Date
>> Salary Double
>>
>
> The above data are actually "rows" from TableDef. TableDef only has 2
> columns "ColName" and "ColType". The TableDef rows are of course the columns
> making up TableX.
>
> Mike
>
>
> On Tue, May 11, 2010 at 9:06 PM, mos <
>> mos99@fastmail.fm> wrote:
>> I'd like to get the field names and data types of a table, say TableX, and
>> put it into TableDef using nothing but SQL. I know I can list the table
>> definition using "Describe Table" and then loop through the results and
>> insert the first two columns Field and Type into TableDef, but is there a
>> way to do it using just SQL?
>>
>> Example:
>> Describe TableX:
>>
>> First_Name Char(15) ....
>> Last_Name Char(20) ...
>> Start_Date Date ..
>> Salary Double ..
>>
>>
>> And I'd like TableDef to have these rows:
>>
>> ColName ColType
>> --------------- --------------
>> First_Name Char(15)
>> Last_Name Char(20)
>> Start_Date Date
>> Salary Double
>>
>> Is there a way to do this with one SQL statement? I'm really looking for
>> the MySQL internal table where it stores the table definitions.
>>
>> TIA
>> Mike
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives:
>> http://lists.mysql.com/mysql
>> To unsubscribe: <
>> http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com>
>> http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
>>
>>
>

--0016e68e8ba5596ac6048653f667--

Re: How to put table definition into another table using SQL?

am 11.05.2010 18:35:03 von mos

At 11:23 AM 5/11/2010, Carlos Proal wrote:

>Hi Mike, you need to query the information_schema schema/database that
>contains the database dictionary.
>You can read the manual or a beautiful diagram here:
>http://www.xcdsql.org/Misc/MySQL_INFORMATION_SCHEMA.html
>
>Have a nice day
>Carlos Proal

Carlos,
Perfect! Thanks.

Mike


>On 5/11/2010 11:06 AM, mos wrote:
>>At 10:53 AM 5/11/2010, Anirudh Sundar wrote:
>>>Hi Mike,
>>>
>>>Did you try this :-
>>>
>>>Create table TableDef select * from TableX where 1 = 2;
>>>
>>>If you give this statement you will create the new table "TableDef" with
>>>the same variables and datatypes as table "TableX"
>>>
>>>Let me know if it works....
>>>
>>>Cheers,
>>>Anirudh Sundar
>>
>>
>>Anirudh,
>> Thanks for the quick reply. I use something like that all the time
>> when I want to copy a table's definition. Actually I use "Create table2
>> select * from table1 limit 0". But what I'm looking for here is the
>> actual definition of TableX to be stored in TableDef.
>>
>>So each row of TableDef represents a column definition from TableX.
>>
>>>And I'd like TableDef to have these rows:
>>>
>>>ColName ColType
>>>--------------- --------------
>>>First_Name Char(15)
>>>Last_Name Char(20)
>>>Start_Date Date
>>>Salary Double
>>
>>The above data are actually "rows" from TableDef. TableDef only has 2
>>columns "ColName" and "ColType". The TableDef rows are of course the
>>columns making up TableX.
>>
>>Mike
>>
>>
>>>On Tue, May 11, 2010 at 9:06 PM, mos
>>><mos99@fastmail.fm> wrote:
>>>I'd like to get the field names and data types of a table, say TableX,
>>>and put it into TableDef using nothing but SQL. I know I can list the
>>>table definition using "Describe Table" and then loop through the
>>>results and insert the first two columns Field and Type into TableDef,
>>>but is there a way to do it using just SQL?
>>>
>>>Example:
>>>Describe TableX:
>>>
>>>First_Name Char(15) ....
>>>Last_Name Char(20) ...
>>>Start_Date Date ..
>>>Salary Double ..
>>>
>>>
>>>And I'd like TableDef to have these rows:
>>>
>>>ColName ColType
>>>--------------- --------------
>>>First_Name Char(15)
>>>Last_Name Char(20)
>>>Start_Date Date
>>>Salary Double
>>>
>>>Is there a way to do this with one SQL statement? I'm really looking for
>>>the MySQL internal table where it stores the table definitions.
>>>
>>>TIA
>>>Mike
>>>
>>>
>>>--
>>>MySQL General Mailing List
>>>For list archives:
>>>http://lists.mysql.com/mysql
>>>To unsubscribe:
>>>http://lists.mysql.com/mysql?unsub=sundar.anirudh@gmail.com
>>>
>>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=mos99@fastmail.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org