Create database if not exists and then create tables in that database in SQL Server

Question

I have SQL script (in one sql file) which

Create database if it does not exists
Create tables if they are not exist

I have started like:

IF ServerProperty('Edition') <> 'SQL Azure'
BEGIN
    DECLARE @createDatabaseSqlStatement NVARCHAR(MAX)
    SET @createDatabaseSqlStatement =
        'DECLARE @TargetDatabase VARCHAR(128) = ''MyDatabase''

        IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (''['' + name + '']'' = @TargetDatabase OR name = @TargetDatabase)))
        BEGIN

        CREATE DATABASE MyDatabase

        END

        USE [MyDatabase]'

    EXEC sp_executesql @createDatabaseSqlStatement
END

SET NOCOUNT ON

DECLARE @DefaultAdministrator   varchar(100) = 'admin'      
DECLARE @DefaultPassword    varchar(100) = 'tester01!!'     
DECLARE @DefaultAdministatorEmail varchar(100) = '[email protected]'
DECLARE @DefaultUserRoleName    varchar(100) = 'rolename'           
DECLARE @DefaultAdministratorRoleName varchar(100) = 'role'

--CREATE TABLES

--CREATE THE 'AspNetUsers' TABLE
IF OBJECT_ID (N'AspNetUsers', N'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[AspNetUsers](
        [Id] [nvarchar](128) NOT NULL,
        [Email] [nvarchar](256) NULL,
        [EmailConfirmed] [bit] NOT NULL,
        [PasswordHash] [nvarchar](max) NULL,
        [SecurityStamp] [nvarchar](max) NULL,
        [PhoneNumber] [nvarchar](max) NULL,
        [PhoneNumberConfirmed] [bit] NOT NULL,
        [TwoFactorEnabled] [bit] NOT NULL,
        [LockoutEndDateUtc] [datetime] NULL,
        [LockoutEnabled] [bit] NOT NULL,
        [AccessFailedCount] [int] NOT NULL,
        [UserName] [nvarchar](256) NOT NULL,
        [SystemId] [nvarchar](max) NOT NULL 
            CONSTRAINT DF__AspNetUsers__SystemId DEFAULT (''),
        [LastLogonTime] [datetime2] NULL,
        [BlockReason] [nvarchar](1000) NULL,
        [EmailConfirmationDateUtc] [datetime] NULL
     CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


    CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex] ON [dbo].[AspNetUsers]
    (
        [UserName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END


...

I have issue that tables are not created in MyDatabase database and they are stored in master database.

How to solve issue ? How to create table into MyDatabase database instead of master ?

If I put USE [MyDatabase] after SET NOCOUNT ON then script failed that MyDatabase is not found (in SQL Management Studio)

Rigerta · Accepted Answer · 2020-04-15 10:49:56Z

1

You are looking for the GO statement. It will separate the batches so in one batch you can create your database:

IF ServerProperty('Edition') <> 'SQL Azure'
BEGIN
    DECLARE @createDatabaseSqlStatement NVARCHAR(MAX)
    SET @createDatabaseSqlStatement =
        'DECLARE @TargetDatabase VARCHAR(128) = ''MyDatabase''

        IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (''['' + name + '']'' = @TargetDatabase OR name = @TargetDatabase)))
        BEGIN

        CREATE DATABASE MyDatabase

        END '

        -- this is not needed here: USE [MyDatabase]'

    EXEC sp_executesql @createDatabaseSqlStatement
END
GO

And afterwards you can simply use it by writing:

USE [MyDatatabase]
GO

And then the rest of your script.

answered Apr 15, 2020 at 10:49

Rigerta

4,0591 gold badge17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-04-15 10:46:11Z

This code doesn't look right:

    DECLARE @TargetDatabase VARCHAR(128) = ''MyDatabase''

    IF (NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (''['' + name + '']'' = @TargetDatabase OR name = @TargetDatabase)))
    BEGIN

    CREATE DATABASE MyDatabase

    END

    USE [MyDatabase]

This creates a database explicitly called MyDatabase, not named based on the value in @TargetDatabase. You would like to do:

    CREATE DATABASE @TargetDatabase

But that won't work. So you need dynamic SQL:

DECLARE @sql NVARCHAR(MAX) = 'CREATE DATABASE @TargetDatabase';

SET @SQL = REPLACE(@SQL, '@TargetDatabase', @TargetDatabase);

EXEC sp_executesql @SQL;

Then, the USE statement is only valid for that session -- which disappears when the dynamic SQL ends. My recommendation is simply to include the database name in all the table names:

CREATE TABLE MyDatabase.[dbo].[AspNetUsers] (
   . . .

Of course, you can use dynamic SQL for this to plug in the variable database name.

Collectives™ on Stack Overflow

Create database if not exists and then create tables in that database in SQL Server

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related