How to run 'create database' as part of a script? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to run 'create database' as part of a script?

Author

Topic

neonsun
Starting Member

4 Posts

Posted - 2008-05-26 : 07:12:30

I'm trying to write a script can be run in two iterations on a system, based on the existence of a database. The first iteration will create the database if it doesn't exist, the second will create tables and content. However I'm unable to script this properly as 'create database' seems to break any loop I add it into since it apparently can not run in a transaction. I'm obviously missing something really fundamental here, does anyone have any idea how to make this work?

USE [MASTER]
IF NOT EXISTS(select 1 from [master]..[sysdatabases] where name='testdatabase')
BEGIN
CREATE DATABASE [testdatabase]
END
ELSE
BEGIN
CREATE TABLE [testdatabase].[dbo].[testtable](
[Id] [int] NOT NULL
)
END

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-05-26 : 09:11:05

try something like this

USE [MASTER]
IF NOT EXISTS(select 1 from [master]..[sysdatabases] where name='testdatabase')
BEGIN
CREATE DATABASE [testdatabase]
END

CREATE TABLE [testdatabase].[dbo].[testtable](
[Id] [int] NOT NULL
)

No need to loop

neonsun
Starting Member

4 Posts

Posted - 2008-05-26 : 10:36:58

Actually, I will need the loop since I'm running more steps in the second iteration - I just cut that from the example for making it simpler to reproduce. However, my DBA came up with the solution: The precompiler checks if the database exists so I had to work around this using a variable:

DECLARE @dbexists int
SET @dbexists=0

IF NOT EXISTS (SELECT 1 FROM [master]..[sysdatabases] where name='testdatabase')
BEGIN
create database [testdatabase];
SET @dbexists=1
END
IF not @dbexists=1
EXEC('CREATE TABLE testdatabase..testtable (test int)')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources