sysobjects EXISTS table function 지우고 생성하기

테이블이 존재하면 지우고자 할때, 또는 SQL 함수(Function)이 존재할 경우 지우고 생성하고 자 할때 사용한다.

Function 함수가 존재할 경우 지우고 생성

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'H3P_ITEM_POSITION' AND type = 'FN')
BEGIN
DROP FUNCTION FN_StrCustomCmp
END
GO

Table 테이블이 존재할 경우 지우고 생성

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'H3P_ITEM_POSITION') AND type = (N'U'))
BEGIN
DROP TABLE H3P_ITEM_POSITION;
END
GO

외국 포럼 참고
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144683

I'm using a product called Mirth, which is an open source swiss army knife used in medical IT for transmitting, receiving, and transforming patient data in several different formats. The logging for Mirth is stored in a database. It comes with a database called Derby, but the creators of Mirth advise that for any production installation Derby will not be good enough in the long run.

They supply a number of scripts to create the database structure for a number of different databases including SQL Server 2000. I'm hoping to use our now retired SQL Server 2000 instance for Mirth, along with other things. Can someone advise me on the script. Does it look innocuous? I was about to run it but got nervous when I saw the first line was "DROP TABLE SCHEMA_INFO". I've created a new database called Mirth and I assume that all of this will run against that database, but I'd like to be sure.

Thanks,

Greg

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'SCHEMA_INFO') AND type = (N'U'))
	DROP TABLE SCHEMA_INFO;

CREATE TABLE SCHEMA_INFO
	(VERSION VARCHAR(40));
	
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'EVENT') AND type = (N'U'))
    DROP TABLE EVENT

CREATE TABLE EVENT
	(ID INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	DATE_CREATED DATETIME DEFAULT GETDATE(),
	EVENT TEXT NOT NULL,
	EVENT_LEVEL VARCHAR(40) NOT NULL,
	DESCRIPTION TEXT,
	ATTRIBUTES TEXT)

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'MESSAGE') AND type = (N'U'))
    ALTER TABLE MESSAGE DROP CONSTRAINT CHANNEL_ID_FK
    
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CHANNEL_STATISTICS') AND type = (N'U'))
    ALTER TABLE CHANNEL_STATISTICS DROP CONSTRAINT CHANNEL_STATS_ID_FK

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CHANNEL') AND type = (N'U'))
    DROP TABLE CHANNEL

CREATE TABLE CHANNEL
	(ID VARCHAR(255) NOT NULL PRIMARY KEY,
	NAME VARCHAR(40) NOT NULL,
	DESCRIPTION TEXT,
	IS_ENABLED SMALLINT,
	VERSION VARCHAR(40),
	REVISION INTEGER,
	LAST_MODIFIED DATETIME DEFAULT GETDATE(),
	SOURCE_CONNECTOR TEXT,
	DESTINATION_CONNECTORS TEXT,
	PROPERTIES TEXT,
	PREPROCESSING_SCRIPT TEXT,
	POSTPROCESSING_SCRIPT TEXT,
	DEPLOY_SCRIPT TEXT,
	SHUTDOWN_SCRIPT TEXT);
	
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CHANNEL_STATISTICS') AND type = (N'U'))
    DROP TABLE CHANNEL_STATISTICS
	
CREATE TABLE CHANNEL_STATISTICS
	(SERVER_ID VARCHAR(255) NOT NULL,
	CHANNEL_ID VARCHAR(255) NOT NULL,
	RECEIVED INTEGER,
	FILTERED INTEGER,
	SENT INTEGER,
	ERROR INTEGER,
	QUEUED INTEGER,
	ALERTED INTEGER,
	PRIMARY KEY(SERVER_ID, CHANNEL_ID))
	
ALTER TABLE CHANNEL_STATISTICS
ADD CONSTRAINT CHANNEL_STATS_ID_FK
FOREIGN KEY (CHANNEL_ID)
REFERENCES CHANNEL (ID) ON DELETE CASCADE

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'ATTACHMENT') AND type = (N'U'))
    DROP TABLE ATTACHMENT

CREATE TABLE ATTACHMENT
    (ID VARCHAR(255) NOT NULL PRIMARY KEY,
     MESSAGE_ID VARCHAR(255) NOT NULL,
     ATTACHMENT_DATA IMAGE,
     ATTACHMENT_SIZE INTEGER,
     ATTACHMENT_TYPE VARCHAR(40));

CREATE INDEX ATTACHMENT_INDEX1 ON ATTACHMENT(MESSAGE_ID);

CREATE INDEX ATTACHMENT_INDEX2 ON ATTACHMENT(ID); 

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'MESSAGE') AND type = (N'U'))
    DROP TABLE MESSAGE

CREATE TABLE MESSAGE
	(SEQUENCE_ID INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	ID VARCHAR(255) NOT NULL,
	SERVER_ID VARCHAR(255) NOT NULL,
	CHANNEL_ID VARCHAR(255) NOT NULL,
	SOURCE VARCHAR(255),
	TYPE VARCHAR(255),
	DATE_CREATED DATETIME NOT NULL,
	VERSION VARCHAR(40),
	IS_ENCRYPTED SMALLINT NOT NULL,
	STATUS VARCHAR(40),
	RAW_DATA TEXT,
	RAW_DATA_PROTOCOL VARCHAR(40),
	TRANSFORMED_DATA TEXT,
	TRANSFORMED_DATA_PROTOCOL VARCHAR(40),
	ENCODED_DATA TEXT,
	ENCODED_DATA_PROTOCOL VARCHAR(40),
	CONNECTOR_MAP TEXT,
	CHANNEL_MAP TEXT,
	RESPONSE_MAP TEXT,
	CONNECTOR_NAME VARCHAR(255),
	ERRORS TEXT,
	CORRELATION_ID VARCHAR(255),
    ATTACHMENT SMALLINT,	
	UNIQUE (ID))
	
ALTER TABLE MESSAGE
ADD CONSTRAINT CHANNEL_ID_FK
FOREIGN KEY (CHANNEL_ID)
REFERENCES CHANNEL (ID) ON DELETE CASCADE

CREATE INDEX MESSAGE_INDEX1 ON MESSAGE(CHANNEL_ID, DATE_CREATED)

CREATE INDEX MESSAGE_INDEX2 ON MESSAGE(CHANNEL_ID, DATE_CREATED, CONNECTOR_NAME)

CREATE INDEX MESSAGE_INDEX3 ON MESSAGE(CHANNEL_ID, DATE_CREATED, RAW_DATA_PROTOCOL)

CREATE INDEX MESSAGE_INDEX4 ON MESSAGE(CHANNEL_ID, DATE_CREATED, SOURCE)

CREATE INDEX MESSAGE_INDEX5 ON MESSAGE(CHANNEL_ID, DATE_CREATED, STATUS)

CREATE INDEX MESSAGE_INDEX6 ON MESSAGE(CHANNEL_ID, DATE_CREATED, TYPE)

CREATE INDEX MESSAGE_INDEX7 ON MESSAGE(CORRELATION_ID, CONNECTOR_NAME)

CREATE INDEX MESSAGE_INDEX8 ON MESSAGE(ATTACHMENT);

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'SCRIPT') AND type = (N'U'))
    DROP TABLE SCRIPT

CREATE TABLE SCRIPT
	(ID VARCHAR(255) NOT NULL PRIMARY KEY,
	SCRIPT TEXT)

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'TEMPLATE') AND type = (N'U'))
    DROP TABLE TEMPLATE

CREATE TABLE TEMPLATE
	(ID VARCHAR(255) NOT NULL PRIMARY KEY,
	TEMPLATE TEXT)
	
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'PERSON') AND type = (N'U'))
    DROP TABLE PERSON

CREATE TABLE PERSON
	(ID INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	USERNAME VARCHAR(40) NOT NULL,
	PASSWORD VARCHAR(40) NOT NULL,
	SALT VARCHAR(40) NOT NULL,
	FIRSTNAME VARCHAR(40),
	LASTNAME VARCHAR(40),
	ORGANIZATION VARCHAR(255),
	EMAIL VARCHAR(255),
	PHONENUMBER VARCHAR(40),
	DESCRIPTION VARCHAR(255),
	LAST_LOGIN DATETIME DEFAULT GETDATE(),
	LOGGED_IN SMALLINT NOT NULL)

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CHANNEL_ALERT') AND type = (N'U'))
    ALTER TABLE CHANNEL_ALERT DROP CONSTRAINT ALERT_ID_CA_FK

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'ALERT_EMAIL') AND type = (N'U'))
    ALTER TABLE ALERT_EMAIL DROP CONSTRAINT ALERT_ID_AE_FK

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'ALERT') AND type = (N'U'))
    DROP TABLE ALERT

CREATE TABLE ALERT
	(ID VARCHAR(255) NOT NULL PRIMARY KEY,
	NAME VARCHAR(40) NOT NULL,
	IS_ENABLED SMALLINT NOT NULL,
	EXPRESSION TEXT,
	TEMPLATE TEXT,
	SUBJECT VARCHAR(998))
	
IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CODE_TEMPLATE') AND type = (N'U'))
    DROP TABLE CODE_TEMPLATE;

CREATE TABLE CODE_TEMPLATE
	(ID VARCHAR(255) NOT NULL PRIMARY KEY,
	NAME VARCHAR(40) NOT NULL,
	CODE_SCOPE VARCHAR(40) NOT NULL,
	CODE_TYPE VARCHAR(40) NOT NULL,
	TOOLTIP VARCHAR(255) NOT NULL,
	CODE TEXT);	

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CHANNEL_ALERT') AND type = (N'U'))
    DROP TABLE CHANNEL_ALERT
	
CREATE TABLE CHANNEL_ALERT
	(CHANNEL_ID VARCHAR(255) NOT NULL,
	ALERT_ID VARCHAR(255) NOT NULL)
	
ALTER TABLE CHANNEL_ALERT
ADD CONSTRAINT ALERT_ID_CA_FK
FOREIGN KEY (ALERT_ID)
REFERENCES ALERT (ID) ON DELETE CASCADE

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'ALERT_EMAIL') AND type = (N'U'))
    DROP TABLE ALERT_EMAIL

CREATE TABLE ALERT_EMAIL
	(ALERT_ID VARCHAR(255) NOT NULL,
	EMAIL VARCHAR(255) NOT NULL)
	
ALTER TABLE ALERT_EMAIL
ADD CONSTRAINT ALERT_ID_AE_FK
FOREIGN KEY (ALERT_ID)
REFERENCES ALERT(ID) ON DELETE CASCADE

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'CONFIGURATION') AND type = (N'U'))
    DROP TABLE CONFIGURATION

CREATE TABLE CONFIGURATION
	(ID INTEGER IDENTITY (1, 1) NOT NULL PRIMARY KEY,
	DATE_CREATED DATETIME DEFAULT GETDATE(),
	DATA TEXT NOT NULL)

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'ENCRYPTION_KEY') AND type = (N'U'))
    DROP TABLE ENCRYPTION_KEY

CREATE TABLE ENCRYPTION_KEY
	(DATA TEXT NOT NULL)

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID = OBJECT_ID(N'PREFERENCES') AND type = (N'U'))
    DROP TABLE PREFERENCES;

CREATE TABLE PREFERENCES
	(PERSON_ID INTEGER NOT NULL,
	NAME VARCHAR(255) NOT NULL,
	VALUE TEXT);

ALTER TABLE PREFERENCES
ADD CONSTRAINT PERSON_ID_PR_FK
FOREIGN KEY (PERSON_ID)
REFERENCES PERSON (ID);

INSERT INTO PERSON (USERNAME, PASSWORD, SALT, LOGGED_IN) VALUES('admin', 'NdgB6ojoGb/uFa5amMEyBNG16mE=', 'Np+FZYzu4M0=', 0)

INSERT INTO SCHEMA_INFO (VERSION) VALUES ('6')

엔돌슨의 IT이야기

sysobjects EXISTS table function 지우고 생성하기