October 15, 2015 at 1:43 am
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
October 15, 2015 at 2:06 am
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