Database creation script

  • Hi,

    I've never scripted a creation of a database and it's schema, but I am trying to achieve this now.

    I have started to put the script together and wondered if the following two scenarios were possible and if so could the following be possible and if so could you show me.

    Scenario 1

    Before the script runs it checks to see if a database of the same name exists. In this example the database name is "database1". If it does the script will stop and display a message along the lines of "You are trying to create a database, which name already exists. Please either drop or rename the current database and rerun this script."

    Scenario 2

    The more difficult one I think. Is that it checks to see if "database1" exists. If it does it displays a message along the lines of "The database you are trying to create already exists. Please confirm that you either want to Drop the Database" You then press a key on the keyboard to continue the script and the database is dropped. It will then tell you to rerun the script in order to create the "database1" database.

    Or you could even give them the option to either drop the database or rename it with "_todatdate" at the end of it, but have no idea if you can get the script to do different things with different keyboard strikes?

    Below is the script I have so far. Any help/advice would be appreciative.

    /*

    DATABASE1 CREATION SCRIPT

    Author: Ryan Keast

    The following script creates a DATABASE1 database.

    */

    /*DATABASE1 database creation */

    --This will create the database in the default location for the data file and log file

    ---------------------

    USE [master]

    go

    CREATE DATABASE [DATABASE1]

    go

    ---------------------

    /*Create Tables */

    USE [DATABASE1]

    SET ansi_nulls ON

    go

    SET quoted_identifier ON

    go

    CREATE TABLE [TABLE1]

    (

    [FIELD1] [VARCHAR] (50),

    [FIELD2] [VARCHAR] (100),

    [FIELD3] [VARCHAR] (100),

    [id1] [INT] IDENTITY(1, 1)

    )

    go

    SET ansi_padding OFF

    go

    CREATE INDEX [IDIndex]

    ON [TABLE1] (id1)

    go

  • You could build logic into the script, but theres no way to get a user to press a key to continue unless you wrote it in some sort of application.

    Something like this

    DECLARE

    @ExistsAction VARCHAR(10) = 'RENAME',

    @Database SYSNAME = 'AntTest',

    @sql NVARCHAR(MAX)

    IF EXISTS (SELECT name FROM master.sys.databases WHERE name = @Database)

    BEGIN

    IF @ExistsAction = 'DROP'

    BEGIN

    PRINT 'Database '+@Database+' already exists on the server, you have detailed to '+@ExistsAction+' the database'

    SET @sql = 'DROP DATABASE '+@Database

    EXEC SP_EXECUTESQL @sql

    END

    ELSE

    IF @ExistsAction = 'RENAME'

    BEGIN

    PRINT 'Database '+@Database+' already exists on the server, you have detailed to '+@ExistsAction+' the database'

    SET @sql = 'ALTER DATABASE '+@Database+' Modify Name = ['+@Database+'_Renamed_'+CONVERT(VARCHAR,GETDATE(),126)+']'

    SELECT @sql

    EXEC SP_EXECUTESQL @sql

    END

    ELSE

    PRINT 'Database '+@Database+' alread exists on the server, but you have not given a valid ExistsAction, the options are DROP to delete the database or RENAME to rename the databases with the current datetime stamp'

    END

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply