SQL script not creating the tables

Question

I am currently trying to make a simple database but everytime I run the script, it creates the database, but does not create any of the tables, any ideas?

use master
IF EXISTS(select * from sys.databases where name = 'MyWebDB')
DROP DATABASE MyWebDB

CREATE DATABASE MyWebDB
DROP TABLE Users
CREATE TABLE Users
(
UserID int IDENTITY PRIMARY KEY,
EmailAddress varchar(100) NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
);
CREATE TABLE Products(
ProductID int IDENTITY PRIMARY KEY,
ProductName varchar(100) NOT NULL
);
DROP TABLE Downloads
CREATE TABLE Downloads(
DownloadID int IDENTITY PRIMARY KEY,
UserID int FOREIGN KEY REFERENCES Users(UserID),
DownloadDate datetime NOT NULL,
FileName varchar(100) NOT NULL,
ProductID int FOREIGN KEY REFERENCES Products(ProductID)
);

You get an error for the drop table and the scripts ends? How could the tables even exist if you just created the whole database — James Z
– James Z, Commented Jun 5, 2015 at 19:35
Looks like you're using master and then creating a new database. Your tables may be going to the master database not MyWebDB — Darren
– Darren, Commented Jun 5, 2015 at 19:36
Ohh, So you have to manually create the database, then create the script to add the tables. Is this what you're getting at? @JamesZ — OysterMaker
– OysterMaker, Commented Jun 5, 2015 at 19:38
Before your first drop table add a line with the word "GO", then add another line with "USE MyWebDB" — JBrooks
– JBrooks, Commented Jun 5, 2015 at 20:00

John Specko · Accepted Answer · 2015-06-05 19:51:14Z

Get rid of your drop tables and make sure to change your database context by using the USE keyword and the database name. If you want to check for the existence of the table like you are doing with the database, query if exists from sys.tables.

USE master
GO
IF EXISTS(select * from sys.databases where name = 'MyWebDB')
DROP DATABASE MyWebDB

CREATE DATABASE MyWebDB
USE MyWebDB
GO
CREATE TABLE Users
(
UserID int IDENTITY PRIMARY KEY,
EmailAddress varchar(100) NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
);
CREATE TABLE Products(
ProductID int IDENTITY PRIMARY KEY,
ProductName varchar(100) NOT NULL
);
CREATE TABLE Downloads(
DownloadID int IDENTITY PRIMARY KEY,
UserID int FOREIGN KEY REFERENCES Users(UserID),
DownloadDate datetime NOT NULL,
FileName varchar(100) NOT NULL,
ProductID int FOREIGN KEY REFERENCES Products(ProductID)
);

amza · Accepted Answer · 2015-06-05 19:39:00Z

3

You have use master so your tables are probably going there.
You're dropping the Users table before you even create it. (Since you dropped the db all tables are implicitly dropped).
You're missing some semicolons at the end of some lines.

answered Jun 5, 2015 at 19:39

amza

8102 gold badges8 silver badges32 bronze badges

1 Comment

Tingo Over a year ago

Trying to drop nonexisting tables is probably causing an error and the rest of the script doesn't execute.

Dan · Accepted Answer · 2015-06-05 19:44:08Z

I agree with stazima, you're setting the database to master so your tables are probably there.

Setting the script to use the new database will help:

use master
IF EXISTS(select * from sys.databases where name = 'MyWebDB')
DROP DATABASE MyWebDB

CREATE DATABASE MyWebDB;
USE MyWebDB;
GO

DROP TABLE Users;
CREATE TABLE Users
(
UserID int IDENTITY PRIMARY KEY,
EmailAddress varchar(100) NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
);
CREATE TABLE Products(
ProductID int IDENTITY PRIMARY KEY,
ProductName varchar(100) NOT NULL
);
DROP TABLE Downloads;
CREATE TABLE Downloads(
DownloadID int IDENTITY PRIMARY KEY,
UserID int FOREIGN KEY REFERENCES Users(UserID),
DownloadDate datetime NOT NULL,
FileName varchar(100) NOT NULL,
ProductID int FOREIGN KEY REFERENCES Products(ProductID)
);

Joe T. · Accepted Answer · 2015-06-05 19:47:38Z

2

use master;
IF EXISTS(select * from sys.databases where name = 'MyWebDB')
DROP DATABASE MyWebDB;

CREATE DATABASE MyWebDB;

-- added line
USE MyWebDB;

DROP TABLE Users;
CREATE TABLE Users
(
UserID int IDENTITY PRIMARY KEY,
EmailAddress varchar(100) NOT NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
);
CREATE TABLE Products(
ProductID int IDENTITY PRIMARY KEY,
ProductName varchar(100) NOT NULL
);

-- missing ; ?

DROP TABLE Downloads;
CREATE TABLE Downloads(
DownloadID int IDENTITY PRIMARY KEY,
UserID int FOREIGN KEY REFERENCES Users(UserID),
DownloadDate datetime NOT NULL,
FileName varchar(100) NOT NULL,
ProductID int FOREIGN KEY REFERENCES Products(ProductID)
);

edited Jun 5, 2015 at 19:47

answered Jun 5, 2015 at 19:36

Joe T.

437 bronze badges

5 Comments

Joe T. Over a year ago

The semicolon I'm referring to is at the end of Drop Table downloads; In the original, there was no semicolon on that line. Though, probably also need it on the other drop table lines as well. Drop Table Users; Drop Database MyWebDB; and Create Database MyWebDB;

Joe T. Over a year ago

More importantly, like Darren and Lain pointed out. You need to switch to the WebDB database after you create it.

OysterMaker Over a year ago

So do I need to change use master to use MyWebDB?

Joe T. Over a year ago

No, add a line use MyWebDB; after Create Database MyWebDB;

OysterMaker Over a year ago

After I did this, it says that it cannot find MyWebDB

Collectives™ on Stack Overflow

SQL script not creating the tables

4 Answers 4

Comments

1 Comment

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related