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
>> <<mailto:mos99@stripped>mos99@stripped> 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>http://lists.mysql.com/mysql
>> To unsubscribe:
>> <http://lists.mysql.com/mysql?unsub=1
>>
>>
>
>
