Create database if db not exist

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


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

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

Tags:

Sql

Sql Server