Can you parameterise a script to setup database\tables etc

[mdav2]

I have a script that works well with hardcoded values which I am trying to parameterise. I am new to SQL server but thought it must be possible. I've include some code which I thought may work but doesn't.

Basically I want to run the script to create the database and tables. Is this possible? I don't want to do it from a procedure.

-------------------------------------------------------
-- DECLARE VARIABLES USED IN SCRIPT (added by M.Davies)
-------------------------------------------------------
-- The Database Name
DECLARE @DataBaseName VARCHAR(20)
SELECT  @DataBaseName = 'Saw_Live'

-- The database file name
DECLARE @DatabASeFilename VARCHAR(20)
SELECT  @DatabASeFilename = 'Saw_Data'

-- The Database Location on the physical server (suffixed with MDF extension, EG. Saw_Live.MDF)
DECLARE @DatabASeFileLocation VARCHAR(250)
SELECT  @DatabASeFileLocation = 'E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Saw_Data.MDF'

-- The database log name
DECLARE @DatabASeLog VARCHAR(20)
SELECT  @DatabASeLog = 'Saw_Live_Log'

-- The DatabAse Log Filename\location on the physical server (suffixed with LDF extension, EG. Saw_Log.LDF)
DECLARE @DatabASeLogLocation VARCHAR(250)
SELECT  @DatabASeLogLocation = 'E:\Microsoft SQL Server\MSSQL.2\MSSQL\Data\Saw_Live_Log.LDF'

-- The DatabASe Login
DECLARE @DatabASeLogin VARCHAR(20)
SELECT  @DatabASeLogin = 'sawadmin'

-- The DatabASe Login PASsword
DECLARE @DatabASePASsword VARCHAR(20)
SELECT  @DatabASePASsword = 'systcent'
-------------------------------------------------------
-- DROP THE EXISTING DATABASE
-------------------------------------------------------
IF EXISTS (SELECT name FROM mASter.dbo.sysdatabASes WHERE name = @DataBaseName)
	DROP DATABASE [@DataBaseName]
GO
-------------------------------------------------------
-- CREATE DATABASE
-------------------------------------------------------
CREATE DATABASE [@DataBaseName]  ON 
  (NAME = @DatabASeFilename, 
  FILENAME = @DatabASeFileLocation , 
  SIZE = 110, 
  FILEGROWTH = 10%) 
LOG ON 
   (NAME = @DatabASeLog, 
   FILENAME = @DatabASeLogLocation, 
   SIZE = 1, 
   FILEGROWTH = 10%)
   COLLATE Latin1_General_CI_AS
GO

Mark Davies
Warwickshire County Council

 

[mrdenny]

You would need to use dynamic SQL to execute the actual CREATE DATABASE statement as you can't specifiy the database name as a dynamic value.


Also get rid of the GO statements between the statements. GO is a batch seperator which will wipe out the parameters values.

DECLARE @cmd VARCHAR(MAX)

SET @cmd = 'CREATE DATABASE [' + @DataBaseName + ']  ON   (NAME = ' + @DatabASeFilename + ',   FILENAME = ' + @DatabASeFileLocation + ' ,   SIZE = 110,   FILEGROWTH = 10%) LOG ON    (NAME = ' + @DatabASeLog + ',    FILENAME = ' + @DatabASeLogLocation + ',    SIZE = 1,    FILEGROWTH = 10%)   COLLATE Latin1_General_CI_AS'

EXEC (@cmd)

Also your database size and growth settings will kill your database performance. Set the database to a size which will hold a good amount of data and with a growth size which is large enough so that it doesn't happen all that often but doesn't take that long to complete.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[mdav2]

Thanks for the reply. Will remove all the "GO" statements from the script and look to use as many statements as possible with parameters. Although this is probably only going to be run as a one off was interested in learning what was possible for future, potential, work.

As regards to the growth size are there any reccomended numbers (probably depends upon number of expected transactions I would think). I'll have a look on the internet to try and find details.

Mark Davies
Warwickshire County Council

 

[mrdenny]

You'll have to find growth numbers which work for you. Normally you'll want to size the database big enough that it wouldn't need to autogrow (basically filling the drive). The autogrow should be a back stop in case you run out of space, not the normal way to grow the database.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog