Search code examples
sql-servervariablessqlcmd

Variables declaration in Create Database Script

I have a large database creation query the beginning of which is as follows:

USE Master
GO

IF EXISTS(SELECT * FROM sys.sysdatabases where name = 'MyDatabase')
    DROP DATABASE MyDatabase
GO
CREATE DATABASE MyDatabase
GO
USE MyDatabase
GO

I want to declare a variable at the beginning like this:

DECLARE @MainDB VARCHAR(30) = NULL
USE Master
GO

IF EXISTS(SELECT * FROM sys.sysdatabases where name = @MainDB)
    DROP DATABASE @MainDB
GO
CREATE DATABASE @MainDB
GO
USE @MainDB
GO

I would execute this query from the command line with the new database name being assigned using the sqlcmd tool. however sql is telling me that the variable @MainDB is not declared. Is this something I can do? If not how would you recommend I work around this problem?

Solution

  • Found a way to make cmdline variables equal t-sql variables

    USE master
GO
DECLARE @Mydb VARCHAR(30) = "$(mydb)"

IF EXISTS(SELECT * FROM sys.sysdatabases where name = @Mydb)
    print @Mydb
    Execute('create database ' + @Mydb)

    The batch file I run from looks like this.

    sqlcmd -S %1 -i CreateDatabases.sql -v mydb="%2"

    I can now run from sqlcmd and enter my server for %1 and desired DB name for %2.

    thanks everyone for the replies they all helped me find the right solution.