46

I want to make SQL Server script for creating database if it does not exist.

IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'DataBase')
  BEGIN
    CREATE DATABASE DataBase
    
    USE DataBase
    
    CREATE TABLE TableName (
        Id INT PRIMARY KEY IDENTITY (1, 1),
        Name VARCHAR(100)
    )

    --more tables here
    --some procedures here too

  END

From code above I'm getting this error:

Msg 911, Level 16, State 1, Line 5 Database 'DataBase' does not exist. Make sure that the name is entered correctly.

How to make database with tables and procedures when database does not exist? I want to make it in one query.

3 Answers 3

43

Could you check the following script :

    IF NOT EXISTS(SELECT * FROM sys.databases WHERE name = 'DataBase')
  BEGIN
    CREATE DATABASE [DataBase]


    END
    GO
       USE [DataBase]
    GO
--You need to check if the table exists
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='TableName' and xtype='U')
BEGIN
    CREATE TABLE TableName (
        Id INT PRIMARY KEY IDENTITY (1, 1),
        Name VARCHAR(100)
    )
END
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, it works, but do we really need to check every time if table exist? There is no option to only check if database exist and then creating tables? In next steps i wanted to creating procedures with databse so it will start to be complicated
25

Obviously you have to start with (and mind the GO here):

USE master
GO

But you have to do it like this:

IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'MyTestDataBase')
BEGIN
  CREATE DATABASE MyTestDataBase;
END;
GO

Mind the GO again. If you don't use GO SSMS (or any other client) will still think your batch is not completed, your database is still not created and therefore not available for further use, and you get the error message you posted.

Now you can start using your just created database with:

USE MyTestDataBase;
GO

Again, mind the GO statement. In this case it is inadmissible because it is not possible to combine CREATE DATABASE and CREATE TABLE statements in one batch. So after the GO continue with:

IF OBJECT_ID('MyTestTable', 'U') IS NULL
BEGIN
  CREATE TABLE dbo.MyTestTable
  (
    Id   INT PRIMARY KEY IDENTITY(1, 1)
  , Name VARCHAR(100)
  );
END;

As already mentioned by others it is good practice to check if every table exists and do a create action if it doesn't and alter action if it does (or just do nothing). But if you really don't want to check if each table exists, for instance when you are sure you need to start from scratch, you could start with dropping the database if it exists:

IF EXISTS (SELECT * FROM sys.databases WHERE name = 'MyTestDataBase')
BEGIN
    DROP DATABASE MyTestDataBase;  
END;
CREATE DATABASE MyTestDataBase;
GO

Comments

3

You can try this.

IF NOT EXISTS (
        SELECT *
        FROM sys.databases
        WHERE name = 'DataBase'
        )
BEGIN
    CREATE DATABASE [DataBase]
END
GO

USE [DataBase]
GO

IF NOT EXISTS (
        SELECT 1
        FROM sys.tables
        WHERE name = 'TableName'
            AND type = 'U'
        )
BEGIN
    CREATE TABLE TableName (
        Id INT PRIMARY KEY IDENTITY(1, 1)
        ,Name VARCHAR(100)
        )
END

enter image description here

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.