SQL script to create a database using a variable name

Question

I'm trying to create a generic script to create a database and want to pass the database name as a variable; but it's not working.

Here is my code so far

DECLARE @ID sysname; 

SET @ID = 'test'
SET @ID = QUOTENAME(@ID)

CREATE DATABASE @ID

ON  PRIMARY
( NAME = [' + @ID + '], FILENAME =  N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' +@ID+ '.mdf' , SIZE = 211968KB ,
 MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
   LOG ON 
( NAME = [' + @ID + '_log'], FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\'@ID + '_log.ldf' , SIZE = 149696KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

which gives me the following error:

Incorrect syntax near '@ID'.

yes not working (sorry), it gives error "Incorrect syntax near '@ID'." — Amanda
– Amanda, Commented Jan 23, 2014 at 10:30
Welcome to SO, BTW. :} What would help your question(s), is to provide a specific error message you've encountered, what you've tried to no avail etcetera. It will save those who would seek to help you from having to run what you've provided to see the error themselves, possibly see a different error or no error in their environment, and generally reduce friction to help you. — J0e3gan
– J0e3gan, Commented Jan 23, 2014 at 10:55

J0e3gan · Accepted Answer · 2016-02-18 23:28:48Z

2

If you can use SQLCMD mode in SSMS (SQLCMD Mode in the Query menu) or execute your script with sqlcmd.exe, you can use a SQLCMD scripting variable:

:setvar ID "test"

CREATE DATABASE [$(ID)]

ON PRIMARY ( NAME = ['$(ID)_Primary'], FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$(ID).mdf' , SIZE = 211968KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = ['$(ID)_log'], FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\$(ID)_log.ldf' , SIZE = 149696KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

From the command line, you would need to comment out the :setvar ID "test" statement (i.e. --:setvar ID "test") and run the script like so...

sqlcmd.exe -E -i YourScript.sql -v ID="real"

...assuming Windows authentication, a local, default instance of SQL Server, and YourScript.sql in the current directory.

The key is the -v command-line option to specify the name of the SQLCMD variable you wish to set and its value.

Using a SQLCMD variable like this is essentially what SSDT does under the hood.

edited Feb 18, 2016 at 23:28

answered Jan 23, 2014 at 10:43

J0e3gan

8,96810 gold badges58 silver badges81 bronze badges

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

1 Comment

Amanda Over a year ago

I'm trying to create in the same way, a login with access to datatabse that have the same variable name, but I'm getting error..please help , my code is USE master; GO DENY VIEW ANY DATABASE TO $(ID); GO the error is Incorrect syntax near '.'.

Collectives™ on Stack Overflow

SQL script to create a database using a variable name

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related