Script check database and table was existed

Script check database and table was existed

DECLARE @dbname nvarchar(128)
SET @dbname = N’dbTest5′

IF (EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE (‘[‘ + name + ‘]’ = @dbname OR name = @dbname)))
begin
use master
drop database dbtest5
end
go
create database dbtest5
go

use dbtest5
go

— Run this script at any time, either to:
— (a) Create the demo tables in a different database (see note in previous example)
— (b) Restore the demo tables to their original state

if exists (select * from sysobjects where name = ‘PurchaseItem’) drop table PurchaseItem
if exists (select * from sysobjects where name = ‘Purchase’) drop table Purchase
if exists (select * from sysobjects where name = ‘Customer’) drop table Customer
if exists (select * from sysobjects where name = ‘MedicalArticles’) drop table MedicalArticles
if exists (select * from sysobjects where name = ‘Product’) drop table Product
go

create table Customer
(
ID int not null primary key,
Name nvarchar(30) not null
)

create table Purchase
(
ID int not null primary key,
CustomerID int null references Customer (ID),
Date datetime not null,
Description varchar(30) not null,
Price decimal not null
)

create table PurchaseItem
(
ID int not null primary key,
PurchaseID int not null references Purchase (ID),
Detail varchar(30) not null,
Price decimal not null
)

create table MedicalArticles
(
ID int not null primary key,
Topic varchar (20),
Abstract nvarchar (2000)
)

create table Product
(
ID int not null primary key,
Description varchar(30) not null,
Discontinued bit not null,
LastSale datetime not null
)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s