Scripting mySQL to create db and tables in one script

[TechBoyJK]

Hi Guys,

I have mySQL running on a Windows 2008 box. It's fine, I've setup databases, tables, etc.

However, I'm trying to get some scripts running.

I have a script to create a database, and I have scripts to create tables.

However, if I run the following script (and the db doesn't exist), the table is being inserted into an existing database.

Is there something I can add to this code to keep the table that is created after the db to be inserted into the same DB?

I want tbl_adCategories inserted into dbAds. I'm guessing I need to declare dbAds in the create table script?

CREATE SCHEMA `dbAds`;

CREATE TABLE `tbl_adCategories`(
`categoryID` int(11) NOT NULL AUTO_INCREMENT,
`categoryParentName` varchar(30) NULL,
`categoryName` varchar(31) NOT NULL,
`categoryStatus` varchar(16) NOT NULL,
`categoryParentID` char(10) NULL,
PRIMARY KEY (`categoryID`),
UNIQUE KEY `categoryID_UNIQUE` (`categoryID`))

 

[KLin]

CREATE TABLE `dbAds`.`tbl_adCategories`

 

[TechBoyJK]

Thanks Klin!

 

[KLin]

np

 

[TechBoyJK]

Looking at that code, do you see any problems with it? I'm going to be writing up a script that creates 100's of tables. The code itself works, and it looks ok to me. Just curious more than anything.

 

[Crusty]

If you don't want to repeat yourself everytime you can do a USE <database_name>;

 

[TechBoyJK]

CREATE SCHEMA `dbAds`;

USE 'dbAds';

CREATE TABLE `tbl_adCategories`(
`categoryID` int(11) NOT NULL AUTO_INCREMENT,
`categoryParentName` varchar(30) NULL,
`categoryName` varchar(31) NOT NULL,
`categoryStatus` varchar(16) NOT NULL,
`categoryParentID` char(10) NULL,
PRIMARY KEY (`categoryID`),
UNIQUE KEY `categoryID_UNIQUE` (`categoryID`))



that look ok?

 

[Crusty]

I'm not your compiler, test it out and see what happens.

 

[TechBoyJK]

I'm not your compiler, test it out and see what happens.

hahaha I am I am. It's not right as I posted it. Googling how to correct it now.

 

[beginner99]

Looking at that code, do you see any problems with it?

Not really just don't like the naming style of tables. Just leave away the "tbl_" prefix and start capitalized.. But that is purely personal preference.