WARNING:

Incorrect or inappropriate use of the following SQL scripts can cause issues for the Data Warehouse - including the corruption or loss of data!

These tasks therefore must be performed by a database administrator, or a by a similar technically skilled resource.

If you are unsure, then please contact the SAI360 help desk, or a SAI360 consultant.

Field Type

SQL script

ORACLE script

Date/Time

sp_RENAME 'ft_[component db identifier in dw].dwk_[column to rename i.e. previous db identifier in dw]', 'dwk_[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table ft_[component db identifier in dw] rename column dwk_[column to rename i.e. previous db identifier in dw] to dwk_[new column name i.e. db identifier in dw];

sp_RENAME 'se_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table se_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

Field Type

SQL script

ORACLE script

Single Lookup Lists

sp_RENAME 'ft_[component db identifier in dw].li_[column to rename i.e. previous db identifier in dw]', 'li_[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table ft_[component db identifier in dw] rename column li_[column to rename i.e. previous db identifier in dw] to li_[new column name i.e. db identifier in dw];

sp_RENAME 'ft_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table ft_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

sp_RENAME 'se_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table se_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

Field Type

SQL script

ORACLE script

Matrix

NOTE:
run for the Matrix itself, and also for the two matrix axis fields.

sp_RENAME 'ft_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table ft_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

sp_RENAME 'se_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table se_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

Field Type

SQL script

ORACLE script

Memo

sp_RENAME 'se_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table se_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

Field Type

SQL script

ORACLE script

Single Relationship

sp_RENAME 'ft_[component db identifier in dw].dwk_[column to rename i.e. previous db identifier in dw]', 'dwk_[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table ft_[component db identifier in dw] rename column dwk_[column to rename i.e. previous db identifier in dw] to dwk_[new column name i.e. db identifier in dw];

Field Type

SQL script

ORACLE script

Auto Number
Text
Long Text
Integer
Decimal

sp_RENAME 'ft_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table ft_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

sp_RENAME 'se_[component db identifier in dw].[column to rename i.e. previous db identifier in dw]', '[new column name i.e. db identifier in dw]' , 'COLUMN';

alter table se_[component db identifier in dw] rename column [column to rename i.e. previous db identifier in dw] to [new column name i.e. db identifier in dw];

Cleanup of

SQL script

ORACLE script

Components

DROP TABLE ft_[component to drop i.e. previous component db identifier in dw]

DROP TABLE ft_[component to drop i.e. previous component db identifier in dw] CASCADE CONSTRAINTS;

DROP TABLE hb_[component to drop i.e. previous component db identifier in dw]

DROP TABLE hb_[component to drop i.e. previous component db identifier in dw] CASCADE CONSTRAINTS;

DROP TABLE se_[component to drop i.e. previous component db identifier in dw]

DROP TABLE se_[component to drop i.e. previous component db identifier in dw] CASCADE CONSTRAINTS;

DROP VIEW rt_[component to drop i.e. previous component db identifier in dw]

DROP VIEW rt_[component to drop i.e. previous component db identifier in dw];

You may also have to run these scripts (only IF any of these tables exist)

DROP TABLE lk_[component to drop i.e. previous component db identifier in dw]

DROP TABLE lk_[component to drop i.e. previous component db identifier in dw] CASCADE CONSTRAINTS;

DROP TABLE ls_[component to drop i.e. previous component db identifier in dw]

DROP TABLE ls_[component to drop i.e. previous component db identifier in dw] CASCADE CONSTRAINTS;

Cleanup of

SQL script

ORACLE script

Multi Lookup Lists

Many - Many Relationship

DROP TABLE ls_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw]

DROP TABLE ls_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw] CASCADE CONSTRAINTS;

DROP TABLE lk_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw]

DROP TABLE lk_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw] CASCADE CONSTRAINTS;

DROP VIEW rt_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw]

DROP VIEW rt_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw];

Cleanup of

SQL script

ORACLE script

Single Relationship

DROP TABLE ls_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw]

DROP TABLE ls_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw] CASCADE CONSTRAINTS;

DROP TABLE lk_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw]

DROP TABLE lk_[component db identifier in dw]_[column to drop i.e. previous db identifier in dw] CASCADE CONSTRAINTS;

Cleanup of

SQL script

ORACLE script

Roll-up Relationship Tables

DROP TABLE dn_[component db identifier in dw]

DROP TABLE dn_[component db identifier in dw] CASCADE CONSTRAINTS;

DROP TABLE dn_[component db identifier in dw]_[roll up relationship db identifier in dw]_cl

DROP TABLE dn_[component db identifier in dw]_[roll up relationship db identifier in dw]_cl CASCADE CONSTRAINTS;

DROP TABLE dn_[component db identifier in dw]_[roll up relationship db identifier in dw]_rg

DROP TABLE dn_[component db identifier in dw]_[roll up relationship db identifier in dw]_rg CASCADE CONSTRAINTS;