Samuele E. Locatelli
421 lines
7.6 KiB
Transact-SQL
421 lines
7.6 KiB
Transact-SQL
/****************************************************************
|
|
* SteamWare s.r.l.
|
|
*
|
|
* Setup iniziale DB applicazione WebGIM con eliminazione oggetti
|
|
*
|
|
* modificato: 2009-01-23 S.E.L.
|
|
*
|
|
****************************************************************/
|
|
|
|
/* elimino funzioni */
|
|
SET xact_abort ON
|
|
GO
|
|
|
|
BEGIN TRANSACTION
|
|
GO
|
|
|
|
IF NOT object_id('f_padLeft') IS NULL
|
|
DROP FUNCTION f_padLeft
|
|
GO
|
|
|
|
IF NOT object_id('rightDivision') IS NULL
|
|
DROP FUNCTION rightDivision
|
|
GO
|
|
|
|
COMMIT
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
/* elimino stored */
|
|
SET xact_abort ON
|
|
GO
|
|
|
|
BEGIN TRANSACTION
|
|
GO
|
|
|
|
IF NOT object_id('sp_clonaImpianto') IS NULL
|
|
DROP PROCEDURE sp_clonaImpianto
|
|
GO
|
|
|
|
IF NOT object_id('sp_clonaMacchina') IS NULL
|
|
DROP PROCEDURE sp_clonaMacchina
|
|
GO
|
|
|
|
IF NOT object_id('sp_elencoIntMTBF_MTTR') IS NULL
|
|
DROP PROCEDURE sp_elencoIntMTBF_MTTR
|
|
GO
|
|
|
|
IF NOT object_id('sp_insRichiesta') IS NULL
|
|
DROP PROCEDURE sp_insRichiesta
|
|
GO
|
|
|
|
IF NOT object_id('sp_procStatEv') IS NULL
|
|
DROP PROCEDURE sp_procStatEv
|
|
GO
|
|
|
|
IF NOT object_id('sp_setPresaInCarico') IS NULL
|
|
DROP PROCEDURE sp_setPresaInCarico
|
|
GO
|
|
|
|
IF NOT object_id('sp_spostaMacchina') IS NULL
|
|
DROP PROCEDURE sp_spostaMacchina
|
|
GO
|
|
|
|
IF NOT object_id('sp_updateIntervento') IS NULL
|
|
DROP PROCEDURE sp_updateIntervento
|
|
GO
|
|
|
|
IF NOT object_id('sp_updateInterventoFull') IS NULL
|
|
DROP PROCEDURE sp_updateInterventoFull
|
|
GO
|
|
|
|
COMMIT
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/* elimino le viste... */
|
|
SET xact_abort ON
|
|
GO
|
|
|
|
BEGIN TRANSACTION
|
|
GO
|
|
|
|
IF NOT object_id('v_datiMTBFMTTR') IS NULL
|
|
DROP VIEW v_datiMTBFMTTR
|
|
GO
|
|
|
|
IF NOT object_id('v_elencoImpiantiMacchine') IS NULL
|
|
DROP VIEW v_elencoImpiantiMacchine
|
|
GO
|
|
|
|
IF NOT object_id('v_elencoIntervFilt') IS NULL
|
|
DROP VIEW v_elencoIntervFilt
|
|
GO
|
|
|
|
IF NOT object_id('v_interventiErogati') IS NULL
|
|
DROP VIEW v_interventiErogati
|
|
GO
|
|
|
|
IF NOT object_id('v_intervExp') IS NULL
|
|
DROP VIEW v_intervExp
|
|
GO
|
|
|
|
IF NOT object_id('v_macchine') IS NULL
|
|
DROP VIEW v_macchine
|
|
GO
|
|
|
|
IF NOT object_id('v_mtzProgExp') IS NULL
|
|
DROP VIEW v_mtzProgExp
|
|
GO
|
|
|
|
IF NOT object_id('v_selAmbiti') IS NULL
|
|
DROP VIEW v_selAmbiti
|
|
GO
|
|
|
|
IF NOT object_id('v_selCausaliMacchine') IS NULL
|
|
DROP VIEW v_selCausaliMacchine
|
|
GO
|
|
|
|
IF NOT object_id('v_selFamMacc') IS NULL
|
|
DROP VIEW v_selFamMacc
|
|
GO
|
|
|
|
IF NOT object_id('v_selFreq') IS NULL
|
|
DROP VIEW v_selFreq
|
|
GO
|
|
|
|
IF NOT object_id('v_selImpianti') IS NULL
|
|
DROP VIEW v_selImpianti
|
|
GO
|
|
|
|
IF NOT object_id('v_selMacchine') IS NULL
|
|
DROP VIEW v_selMacchine
|
|
GO
|
|
|
|
IF NOT object_id('v_selPeriodiTrad') IS NULL
|
|
DROP VIEW v_selPeriodiTrad
|
|
GO
|
|
|
|
IF NOT object_id('v_selPrior') IS NULL
|
|
DROP VIEW v_selPrior
|
|
GO
|
|
|
|
IF NOT object_id('v_selSemafori') IS NULL
|
|
DROP VIEW v_selSemafori
|
|
GO
|
|
|
|
IF NOT object_id('v_selStato') IS NULL
|
|
DROP VIEW v_selStato
|
|
GO
|
|
|
|
IF NOT object_id('v_selTipoGuasto') IS NULL
|
|
DROP VIEW v_selTipoGuasto
|
|
GO
|
|
|
|
IF NOT object_id('v_vocabolario') IS NULL
|
|
DROP VIEW v_vocabolario
|
|
GO
|
|
|
|
COMMIT
|
|
GO
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
/*Svuoto eventuali tabelle già presenti...*/
|
|
SET xact_abort ON
|
|
GO
|
|
|
|
BEGIN TRANSACTION
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagAmbitoGuasto') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagAmbitoGuasto
|
|
GO
|
|
|
|
IF NOT object_id('AnagAmbitoGuasto') IS NULL
|
|
DROP TABLE AnagAmbitoGuasto
|
|
GO
|
|
|
|
IF NOT object_id('FK_Causali2FamMacchine_AnagCausali') IS NULL
|
|
|
|
ALTER TABLE Causali2FamMacchine DROP
|
|
CONSTRAINT FK_Causali2FamMacchine_AnagCausali
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagCausali') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagCausali
|
|
GO
|
|
|
|
IF NOT object_id('AnagCausali') IS NULL
|
|
DROP TABLE AnagCausali
|
|
GO
|
|
|
|
IF NOT object_id('FK_Tra_Ev2Stati_AnagEventi') IS NULL
|
|
|
|
ALTER TABLE Tra_Ev2Stati DROP
|
|
CONSTRAINT FK_Tra_Ev2Stati_AnagEventi
|
|
GO
|
|
|
|
IF NOT object_id('AnagEventi') IS NULL
|
|
DROP TABLE AnagEventi
|
|
GO
|
|
|
|
IF NOT object_id('FK_Causali2FamMacchine_AnagFamMacchine') IS NULL
|
|
|
|
ALTER TABLE Causali2FamMacchine DROP
|
|
CONSTRAINT FK_Causali2FamMacchine_AnagFamMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FK_Macchine2FamMacchine_AnagFamMacchine') IS NULL
|
|
|
|
ALTER TABLE Macchine2FamMacchine DROP
|
|
CONSTRAINT FK_Macchine2FamMacchine_AnagFamMacchine
|
|
GO
|
|
|
|
IF NOT object_id('AnagFamMacchine') IS NULL
|
|
DROP TABLE AnagFamMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FK_MtzProgrammata_AnagFrequenze') IS NULL
|
|
|
|
ALTER TABLE MtzProgrammata DROP
|
|
CONSTRAINT FK_MtzProgrammata_AnagFrequenze
|
|
GO
|
|
|
|
IF NOT object_id('AnagFrequenze') IS NULL
|
|
DROP TABLE AnagFrequenze
|
|
GO
|
|
|
|
IF NOT object_id('FK_FiltroImpianti_AnagImpianti') IS NULL
|
|
|
|
ALTER TABLE FiltroImpianti DROP
|
|
CONSTRAINT FK_FiltroImpianti_AnagImpianti
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagImpianti') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagImpianti
|
|
GO
|
|
|
|
IF NOT object_id('FK_AnagMacchine_AnagImpianti') IS NULL
|
|
|
|
ALTER TABLE AnagMacchine DROP
|
|
CONSTRAINT FK_AnagMacchine_AnagImpianti
|
|
GO
|
|
|
|
IF NOT object_id('AnagImpianti') IS NULL
|
|
DROP TABLE AnagImpianti
|
|
GO
|
|
|
|
IF NOT object_id('FK_MtzProgrammata_AnagMacchine') IS NULL
|
|
|
|
ALTER TABLE MtzProgrammata DROP
|
|
CONSTRAINT FK_MtzProgrammata_AnagMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FK_FiltroMacchine_AnagMacchine') IS NULL
|
|
|
|
ALTER TABLE FiltroMacchine DROP
|
|
CONSTRAINT FK_FiltroMacchine_AnagMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagMacchine') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FK_Macchine2FamMacchine_AnagMacchine') IS NULL
|
|
|
|
ALTER TABLE Macchine2FamMacchine DROP
|
|
CONSTRAINT FK_Macchine2FamMacchine_AnagMacchine
|
|
GO
|
|
|
|
IF NOT object_id('AnagMacchine') IS NULL
|
|
DROP TABLE AnagMacchine
|
|
GO
|
|
|
|
IF NOT object_id('AnagPeriodi') IS NULL
|
|
DROP TABLE AnagPeriodi
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagPriorita') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagPriorita
|
|
GO
|
|
|
|
IF NOT object_id('AnagPriorita') IS NULL
|
|
DROP TABLE AnagPriorita
|
|
GO
|
|
|
|
IF NOT object_id('AnagSemafori') IS NULL
|
|
DROP TABLE AnagSemafori
|
|
GO
|
|
|
|
IF NOT object_id('FK_Tra_Ev2Stati_AnagStati') IS NULL
|
|
|
|
ALTER TABLE Tra_Ev2Stati DROP
|
|
CONSTRAINT FK_Tra_Ev2Stati_AnagStati
|
|
GO
|
|
|
|
IF NOT object_id('FK_FiltroStati_AnagStati') IS NULL
|
|
|
|
ALTER TABLE FiltroStati DROP
|
|
CONSTRAINT FK_FiltroStati_AnagStati
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagStati') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagStati
|
|
GO
|
|
|
|
IF NOT object_id('AnagStati') IS NULL
|
|
DROP TABLE AnagStati
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventiMtz_AnagTipoGuasto') IS NULL
|
|
|
|
ALTER TABLE InterventiMtz DROP
|
|
CONSTRAINT FK_InterventiMtz_AnagTipoGuasto
|
|
GO
|
|
|
|
IF NOT object_id('AnagTipoGuasto') IS NULL
|
|
DROP TABLE AnagTipoGuasto
|
|
GO
|
|
|
|
IF NOT object_id('CalendFesteFerie') IS NULL
|
|
DROP TABLE CalendFesteFerie
|
|
GO
|
|
|
|
IF NOT object_id('Causali2FamMacchine') IS NULL
|
|
DROP TABLE Causali2FamMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FiltroImpianti') IS NULL
|
|
DROP TABLE FiltroImpianti
|
|
GO
|
|
|
|
IF NOT object_id('FiltroMacchine') IS NULL
|
|
DROP TABLE FiltroMacchine
|
|
GO
|
|
|
|
IF NOT object_id('FiltroStati') IS NULL
|
|
DROP TABLE FiltroStati
|
|
GO
|
|
|
|
IF NOT object_id('FK_InterventoOpMtz_InterventiMtz') IS NULL
|
|
|
|
ALTER TABLE InterventoOpMtz DROP
|
|
CONSTRAINT FK_InterventoOpMtz_InterventiMtz
|
|
GO
|
|
|
|
IF NOT object_id('InterventiMtz') IS NULL
|
|
DROP TABLE InterventiMtz
|
|
GO
|
|
|
|
IF NOT object_id('InterventoOpMtz') IS NULL
|
|
DROP TABLE InterventoOpMtz
|
|
GO
|
|
|
|
IF NOT object_id('LogUpdateDb') IS NULL
|
|
DROP TABLE LogUpdateDb
|
|
GO
|
|
|
|
IF NOT object_id('Macchine2FamMacchine') IS NULL
|
|
DROP TABLE Macchine2FamMacchine
|
|
GO
|
|
|
|
IF NOT object_id('MtzProgPending') IS NULL
|
|
DROP TABLE MtzProgPending
|
|
GO
|
|
|
|
IF NOT object_id('MtzProgrammata') IS NULL
|
|
DROP TABLE MtzProgrammata
|
|
GO
|
|
|
|
IF NOT object_id('OreAperturaSett') IS NULL
|
|
DROP TABLE OreAperturaSett
|
|
GO
|
|
|
|
IF NOT object_id('FK_Permessi2Funzione_Permessi') IS NULL
|
|
|
|
ALTER TABLE Permessi2Funzione DROP
|
|
CONSTRAINT FK_Permessi2Funzione_Permessi
|
|
GO
|
|
|
|
IF NOT object_id('Permessi') IS NULL
|
|
DROP TABLE Permessi
|
|
GO
|
|
|
|
IF NOT object_id('Permessi2Funzione') IS NULL
|
|
DROP TABLE Permessi2Funzione
|
|
GO
|
|
|
|
IF NOT object_id('Tra_Ev2Stati') IS NULL
|
|
DROP TABLE Tra_Ev2Stati
|
|
GO
|
|
|
|
COMMIT
|
|
GO
|