SQL Server creates tables in wrong database

Question

I want to create a new database and fill it up with some tables. It creates a new database but the tables appear in another database.

The @@DbName contains the name of the database the user gave in, the fact that it is creating a database says the DbName is correct. When executed the database is created but empty, all tables are inserted in another database.

I believe the error lies in the USE [@@DbName]

What am I doing wrong?

My SQL statements:

CREATE DATABASE [@@DbName]
ON PRIMARY
  ( NAME = [@@DbName],
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\@@DbName.mdf',
    SIZE = 5 MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1 MB )
LOG ON
  ( NAME = [@@DbName_log],
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\@@DbName_log.ldf',
    SIZE = 1 MB,
    MAXSIZE = 2097152 MB,
    FILEGROWTH = 10 % )
COLLATE Latin1_General_CI_AS
GO

CREATE LOGIN [@@DbLogin] WITH PASSWORD=N'@@DbPassword', DEFAULT_DATABASE=[@@DbName], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [@@DbName]
GO

CREATE TABLE [dbo].[Customers] 
(
  [ID] int IDENTITY(1, 1) NOT NULL,
  [CustomerName] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Address] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Picture] image NOT NULL,
  [Balance] decimal(18, 2) NOT NULL
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employee] 
(
  [ID] int IDENTITY(1, 1) NOT NULL,
  [EmployeeName] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Address] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Email] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Phone] varchar(20) COLLATE Latin1_General_CI_AS NOT NULL
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Registers] 
(
  [ID] int IDENTITY(1, 1) NOT NULL,
  [RegisterName] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Device] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Errorlog] 
(
  [RegisterID] int NOT NULL,
  [Timestamp] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Message] varchar(255) COLLATE Latin1_General_CI_AS NOT NULL,
  [StackTrace] ntext COLLATE Latin1_General_CI_AS NOT NULL
)
ON [PRIMARY]
TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Products] 
(
  [ID] int IDENTITY(1, 1) NOT NULL,
  [ProductName] varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
  [Price] decimal(18, 2) NULL
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Register_Employee] 
(
  [RegisterID] int NOT NULL,
  [EmployeeID] int NOT NULL,
  [From] varchar(20) COLLATE Latin1_General_CI_AS NOT NULL,
  [Until] varchar(20) COLLATE Latin1_General_CI_AS NOT NULL
)
ON [PRIMARY]
GO

CREATE TABLE [dbo].[Sales] 
(
  [ID] int NOT NULL,
  [Timestamp] timestamp NOT NULL,
  [CustomerID] int NOT NULL,
  [RegisterID] int NOT NULL,
  [ProductID] int NOT NULL,
  [Amount] int NOT NULL,
  [TotalPrice] decimal(18, 2) NOT NULL
)
ON [PRIMARY]
GO

re create tables as `Server.Database.Schema.Object` or as `[@@DbName].[dbo].[Customers] — Deepshikha, Jan 06 '15 at 11:20
Please see http://stackoverflow.com/questions/2184219/statement-use-dbname-doesnt-work-why-how-to-do-that — Steve Ford, Jan 06 '15 at 11:47
You can't use variables in a `USE` statement unless they're sqlcmd variables. — ta.speot.is, Jan 06 '15 at 11:47
Dynamic SQL will solve this problem. Here are same example http://stackoverflow.com/questions/3788566/how-can-i-do-something-like-use-databasename — tungula, Jan 06 '15 at 15:42

SQL Server creates tables in wrong database

0 Answers0