SQL - Create database script with database name as parameter

holemania
holemania used Ask the Experts™
on
I need help with a create script to create a blank new database.  How can I take the following script and create a parameter for database name so that it replace "DB1" with something I just have to type once as the parameter?  

USE [master]
GO
CREATE DATABASE [DB1] ON  PRIMARY
( NAME = N'DB1_Data', FILENAME = N'D:\Microsoft SQL Server\Data\DB1_Data.MDF' , SIZE = 78784KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON
( NAME = N'DB1_Log', FILENAME = N'D:\Microsoft SQL Server\Logs\DB1_log.ldf' , SIZE = 768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'DB1', @new_cmptlevel=80
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [DB1].[dbo].[sp_fulltext_database] @action = 'disable'
end
GO
ALTER DATABASE [DB1] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [DB1] SET ANSI_NULLS OFF
GO
ALTER DATABASE [DB1] SET ANSI_PADDING OFF
GO
ALTER DATABASE [DB1] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [DB1] SET ARITHABORT OFF
GO
ALTER DATABASE [DB1] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [DB1] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [DB1] SET AUTO_SHRINK ON
GO
ALTER DATABASE [DB1] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [DB1] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [DB1] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [DB1] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [DB1] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [DB1] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [DB1] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [DB1] SET  DISABLE_BROKER
GO
ALTER DATABASE [DB1] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [DB1] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [DB1] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [DB1] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [DB1] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [DB1] SET  READ_WRITE
GO
ALTER DATABASE [DB1] SET RECOVERY SIMPLE
GO
ALTER DATABASE [DB1] SET  MULTI_USER
GO
ALTER DATABASE [DB1] SET PAGE_VERIFY TORN_PAGE_DETECTION  
GO
ALTER DATABASE [DB1] SET DB_CHAINING OFF
GO
EXEC [DB1].sys.sp_addextendedproperty @name=N'NOTE', @value=N'TEST DATABASE'
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software Engineer
Commented:
Try this:

DECLARE @DB varchar(50)
set @DB = 'DB1'

USE [master]
exec('CREATE DATABASE [' + @DB + '] ON  PRIMARY 
( NAME = N''' + @DB + '_Data'', FILENAME = N''C:\tmp\' + @DB + '_Data.MDF'' , SIZE = 78784KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N''' + @DB + '_Log'', FILENAME = N''C:\tmp\' + @DB + '_log.ldf'' , SIZE = 768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS')
EXEC dbo.sp_dbcmptlevel @dbname=@DB, @new_cmptlevel=80
exec('IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
begin
EXEC [' + @DB + '].[dbo].[sp_fulltext_database] @action = ''disable''
end')
exec('ALTER DATABASE [' + @DB + '] SET ANSI_NULL_DEFAULT OFF ');
exec('ALTER DATABASE [' + @DB + '] SET ANSI_NULLS OFF ');
exec('ALTER DATABASE [' + @DB + '] SET ANSI_PADDING OFF ');
exec('ALTER DATABASE [' + @DB + '] SET ANSI_WARNINGS OFF ');
exec('ALTER DATABASE [' + @DB + '] SET ARITHABORT OFF ');
exec('ALTER DATABASE [' + @DB + '] SET AUTO_CLOSE OFF ');
exec('ALTER DATABASE [' + @DB + '] SET AUTO_CREATE_STATISTICS ON ');
exec('ALTER DATABASE [' + @DB + '] SET AUTO_SHRINK ON ');
exec('ALTER DATABASE [' + @DB + '] SET AUTO_UPDATE_STATISTICS ON ');
exec('ALTER DATABASE [' + @DB + '] SET CURSOR_CLOSE_ON_COMMIT OFF ');
exec('ALTER DATABASE [' + @DB + '] SET CURSOR_DEFAULT  GLOBAL ');
exec('ALTER DATABASE [' + @DB + '] SET CONCAT_NULL_YIELDS_NULL OFF ');
exec('ALTER DATABASE [' + @DB + '] SET NUMERIC_ROUNDABORT OFF ');
exec('ALTER DATABASE [' + @DB + '] SET QUOTED_IDENTIFIER OFF ');
exec('ALTER DATABASE [' + @DB + '] SET RECURSIVE_TRIGGERS OFF ');
exec('ALTER DATABASE [' + @DB + '] SET  DISABLE_BROKER ');
exec('ALTER DATABASE [' + @DB + '] SET AUTO_UPDATE_STATISTICS_ASYNC OFF ');
exec('ALTER DATABASE [' + @DB + '] SET DATE_CORRELATION_OPTIMIZATION OFF ');
exec('ALTER DATABASE [' + @DB + '] SET TRUSTWORTHY OFF ');
exec('ALTER DATABASE [' + @DB + '] SET ALLOW_SNAPSHOT_ISOLATION OFF ');
exec('ALTER DATABASE [' + @DB + '] SET PARAMETERIZATION SIMPLE ');
exec('ALTER DATABASE [' + @DB + '] SET  READ_WRITE ');
exec('ALTER DATABASE [' + @DB + '] SET RECOVERY SIMPLE ');
exec('ALTER DATABASE [' + @DB + '] SET  MULTI_USER ');
exec('ALTER DATABASE [' + @DB + '] SET PAGE_VERIFY TORN_PAGE_DETECTION  ');
exec('ALTER DATABASE [' + @DB + '] SET DB_CHAINING OFF ');
exec('EXEC [' + @DB + '].sys.sp_addextendedproperty @name=N''NOTE'', @value=N''TEST DATABASE''')

Open in new window


This works for me in the Management console.
Anuradha GoliSystems Development / Support Specialist
Commented:
Declare @dbname nvarchar(50)
set @dbname = 'DB1'
Declare @dbQuery nvarchar(max)
set @dbQuery = 'CREATE DATABASE ['''+@dbname+'''] ON  PRIMARY 
( NAME = N'''+@dbname+'_Data'', FILENAME = N''D:\Microsoft SQL Server\Data\'+@dbname+'_Data.MDF'' , SIZE = 78784KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 LOG ON 
( NAME = N'''+@dbname+'_Log'', FILENAME = N''D:\Microsoft SQL Server\Logs\'+@dbname+'_log.ldf'' , SIZE = 768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%)
 COLLATE SQL_Latin1_General_CP1_CI_AS

EXEC dbo.sp_dbcmptlevel @dbname=N'''+@dbname+''', @new_cmptlevel=80

IF (1 = FULLTEXTSERVICEPROPERTY(''IsFullTextInstalled''))
begin
EXEC ['+@dbname+'].[dbo].[sp_fulltext_database] @action = ''disable''
end

ALTER DATABASE ['+@dbname+'] SET ANSI_NULL_DEFAULT OFF 

ALTER DATABASE ['+@dbname+'] SET ANSI_NULLS OFF 

ALTER DATABASE ['+@dbname+'] SET ANSI_PADDING OFF 

ALTER DATABASE ['+@dbname+'] SET ANSI_WARNINGS OFF 

ALTER DATABASE ['+@dbname+'] SET ARITHABORT OFF 

ALTER DATABASE ['+@dbname+'] SET AUTO_CLOSE OFF 

ALTER DATABASE ['+@dbname+'] SET AUTO_CREATE_STATISTICS ON 

ALTER DATABASE ['+@dbname+'] SET AUTO_SHRINK ON 

ALTER DATABASE ['+@dbname+'] SET AUTO_UPDATE_STATISTICS ON 

ALTER DATABASE ['+@dbname+'] SET CURSOR_CLOSE_ON_COMMIT OFF 

ALTER DATABASE ['+@dbname+'] SET CURSOR_DEFAULT  GLOBAL 

ALTER DATABASE ['+@dbname+'] SET CONCAT_NULL_YIELDS_NULL OFF 

ALTER DATABASE ['+@dbname+'] SET NUMERIC_ROUNDABORT OFF 

ALTER DATABASE ['+@dbname+'] SET QUOTED_IDENTIFIER OFF 

ALTER DATABASE ['+@dbname+'] SET RECURSIVE_TRIGGERS OFF 

ALTER DATABASE ['+@dbname+'] SET  DISABLE_BROKER 

ALTER DATABASE ['+@dbname+'] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 

ALTER DATABASE ['+@dbname+'] SET DATE_CORRELATION_OPTIMIZATION OFF 

ALTER DATABASE ['+@dbname+'] SET TRUSTWORTHY OFF 

ALTER DATABASE ['+@dbname+'] SET ALLOW_SNAPSHOT_ISOLATION OFF 

ALTER DATABASE ['+@dbname+'] SET PARAMETERIZATION SIMPLE 

ALTER DATABASE ['+@dbname+'] SET  READ_WRITE 

ALTER DATABASE ['+@dbname+'] SET RECOVERY SIMPLE 

ALTER DATABASE ['+@dbname+'] SET  MULTI_USER 

ALTER DATABASE ['+@dbname+'] SET PAGE_VERIFY TORN_PAGE_DETECTION  

ALTER DATABASE ['+@dbname+'] SET DB_CHAINING OFF 

EXEC ['+@dbname+'].sys.sp_addextendedproperty @name=N''NOTE'', @value=N''TEST DATABASE'''

select @dbQuery

Open in new window

Author

Commented:
Thank you.  I am out of the office this entire week and can't verify.  I will test it next week and get back.

Author

Commented:
Thank you.  That worked beautifully.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial