0

Create a Database using SQL

by Jon 27. April 2011 22:37

Dynamic SQL saves the day.  I needed to script create some databases but I want to specify a number of parameters to be used as part of the Create Database Command:

  1. I want to specify where the database files will be created
  2. I want the all databases to be Prefixed with a standard set of characters
  3. I want to create one or more databases
------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- Please set the following Two Variables Correctly

Declare @InstallDbPath as nvarchar(500)
Declare @DbPrefix as nvarchar(50)
set @InstallDbPath = 'D:\Databases\QA\'
set @DbPrefix = 'QA_'

------------------------------------------------------------------------------------
------------------------------------------------------------------------------------
-- Dont Change any SQL after this line, just set the Variables at the above
-- Some Variables that we will use
Declare @InstallDbName as nvarchar(50)
Declare @SQLCmd as nvarchar(max)
Declare @DbName as nvarchar (50) -- DbName Changes, its the name of the database we want to create

-- TODO : Change this to run in a loop, reduce repeated SQL

------------------------------------------------------------------------------------
-- Change the Variable @DbName  to be the name of the databse you want to create

-- Create Database1
set @DbName = 'Database1'
set @SQLCmd = 'CREATE DATABASE ' + @DbPrefix + @DbName + ' ON PRIMARY
(NAME = ''' + @DbPrefix + @DbName + '_Data'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Data.mdf'', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
LOG ON
(NAME = ''' + @DbPrefix + @DbName + '_Log'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Log.mdf'', SIZE = 4096KB , MAXSIZE = 2GB, FILEGROWTH = 10%)' 
exec (@SQLCmd)

-- Create Database2
set @DbName = 'Database2'
set @SQLCmd = 'CREATE DATABASE ' + @DbPrefix + @DbName + ' ON PRIMARY
(NAME = ''' + @DbPrefix + @DbName + '_Data'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Data.mdf'', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
LOG ON
(NAME = ''' + @DbPrefix + @DbName + '_Log'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Log.mdf'', SIZE = 4096KB , MAXSIZE = 2GB, FILEGROWTH = 10%)' 
exec (@SQLCmd)

-- Create Database3
set @DbName = 'Database3'
set @SQLCmd = 'CREATE DATABASE ' + @DbPrefix + @DbName + ' ON PRIMARY
(NAME = ''' + @DbPrefix + @DbName + '_Data'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Data.mdf'', SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%) 
LOG ON
(NAME = ''' + @DbPrefix + @DbName + '_Log'' , FILENAME = ''' + @InstallDbPath + @DbPrefix + @DbName + '_Log.mdf'', SIZE = 4096KB , MAXSIZE = 2GB, FILEGROWTH = 10%)' 
exec (@SQLCmd)

Tags:

SQL

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Powered by BlogEngine.NET 2.0.0.36
Original Design by Laptop Geek, Adapted by onesoft, and finally some tiny tweaks by JonAlb