List:General Discussion« Previous MessageNext Message »
From:Carlos Proal Date:May 11 2010 4:23pm
Subject:Re: How to put table definition into another table using SQL?
View as plain text  
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 
>>
>>
>
>

Thread
How to put table definition into another table using SQL?mos11 May
  • Re: How to put table definition into another table using SQL?Nuno Tavares11 May
  • Re: How to put table definition into another table using SQL?Phil11 May
  • Re: How to put table definition into another table using SQL?Anirudh Sundar11 May
    • Re: How to put table definition into another table using SQL?mos11 May
      • Re: How to put table definition into another table using SQL?Carlos Proal11 May
        • Re: How to put table definition into another table using SQL?mos11 May
      • Re: How to put table definition into another table using SQL?Anirudh Sundar11 May