July 1, 2008 at 4:31 am
Hi Guys
Can anyone advise me on how to create a script to create a db, table and stored proc in one script. I have 2 scripts below that checks whether the db exist and prints a message whether it's been created or not:
--Create Database
IF Not EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'TestDB')
BEGIN
CREATE DATABASE TestDB on Primary
( NAME = Test_dat,
FILENAME = 'i:\Test_dat.MDF',
SIZE = 10,
MAXSIZE = Unlimited,
FILEGROWTH = 100MB)
LOG ON
(NAME = 'TestDB_log',
FILENAME = 'J:\Test_Log.LDF',
SIZE = 5MB,
MAXSIZE = Unlimited,
FILEGROWTH = 100MB)
Print 'Database creation done.'
END
ELSE Print 'Database already exists.'
--Create Table
IF Not EXISTS (select * from dbo.sysobjects where id = object_id(N'[dbo].[PropertyCollection]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
USE [TestDB]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON
CREATE TABLE [dbo].[TestTable](
[Column1] [datetime] NOT NULL,
[Column2] [varchar](128) NOT NULL,
[Column3] [varchar](256) NOT NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
Print 'Table creation done.'
END
ELSE Print 'Table already exists.'
Can anyone pls advise on concatenation? Please help.
Regards
Imke
July 1, 2008 at 6:27 am
You have to put a "GO" statement between these two scripts. This seperates the two scripts into seperate commands so that they are processed independently by the server. That allows the database to be created before the tables attempt to access it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 1, 2008 at 6:52 am
Thank you very much!!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply