/******************************************************************************/
/* BANCO DE DADOS : MSSQL */
/* VERSÃO DO BANCO DE DADOS : 7.0/2000/2005 */
/* VERSÃO DA BASE DE DADOS : FULL */
/* DATA DE CRIAÇÃO : 29/03/2007 */
/* ULTIMA ATUALIZAÇÃO : 15/12/2009 */
/* ANALISTA RESPONSÁVEL : FABIO FUMIO */
/* OBJETIVO : GERAR RELACIONAMENTOS ENTRE AS TABELAS */
/******************************************************************************/
/*********************************************************/
/* TABELA :ZSYSCONSTRAINTS */
/* SP GERADORA DE SCRIPT (DROP) :SP_GERASCRIPTDROPFK */
/* SP GERADORA DE SCRIPT (ADD) :SP_GERASCRIPTADDFK */
/*********************************************************/
-----------------------------------------------------------
-- CRIAÇÃO DA TABELA
-----------------------------------------------------------
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE XTYPE = 'U'
AND NAME = 'ZSYSCONSTRAINTS')
BEGIN
DROP TABLE ZSYSCONSTRAINTS
END
GO
CREATE TABLE ZSYSCONSTRAINTS (
CONSTID INT,
NOMERELAC VARCHAR(150),
TABELAMAE VARCHAR(100),
TABELAFILHA VARCHAR(100),
CAMPOTABMAE VARCHAR(255),
CAMPOTABFILHA VARCHAR(255),
VERSAOBASE VARCHAR(10))
GO
-----------------------------------------------------------
-- GERAÇÃO DOS RELACIONAMENTOS - POPULANDO TABELA
-----------------------------------------------------------
DECLARE @NOMERELAC VARCHAR(150)
DECLARE @TABELAMAE VARCHAR(100)
DECLARE @TABELAFILHA VARCHAR(100)
DECLARE @CAMPOTABMAE VARCHAR(255)
DECLARE @CAMPOTABFILHA VARCHAR(255)
DECLARE @CONSTID INT
DECLARE @KEYNO INT
DECLARE @VERSAOBASE VARCHAR(10)
DECLARE CR_RELACIONAMENTOS CURSOR FOR
SELECT DADOSRELAC.NAME AS NOMERELACIONAMENTO, ORIGEM.NAME AS TABELAMAE, DESTINO.NAME AS TABELAFILHA,
CAMPOORIGEM.NAME AS CAMPOTABMAE, CAMPODESTINO.NAME AS CAMPOTABFILHA,
FOREIGNK.CONSTID, FOREIGNK.KEYNO
FROM SYSOBJECTS TABELA, -- PARA PESQUISA
SYSCONSTRAINTS RELAC, -- JOIN ENTRE A TABELA E OS DADOS DO RELACIONAMENTO,
SYSOBJECTS DADOSRELAC, -- DADOS DO RELACIONAMENTO,
SYSFOREIGNKEYS FOREIGNK, -- DADOS DA FOREIGN KEYS,
SYSOBJECTS ORIGEM, -- TABELA ORIGEM DO RELACIONAMENTO
SYSOBJECTS DESTINO, -- TABELA DESTINO DO RELACIONAMENTO
SYSCOLUMNS CAMPOORIGEM, -- CAMPO ORIGEM DO RELACIONAMENTO
SYSCOLUMNS CAMPODESTINO -- CAMPO DESTINO DO RELACIONAMENTO
WHERE TABELA.XTYPE = 'U'
AND TABELA.ID = RELAC.ID
AND RELAC.CONSTID = DADOSRELAC.ID
AND RELAC.CONSTID = FOREIGNK.CONSTID
AND FOREIGNK.FKEYID = ORIGEM.ID
AND FOREIGNK.RKEYID = DESTINO.ID
AND FOREIGNK.FKEY = CAMPOORIGEM.COLID
AND FOREIGNK.FKEYID = CAMPOORIGEM.ID
AND FOREIGNK.RKEY = CAMPODESTINO.COLID
AND FOREIGNK.RKEYID = CAMPODESTINO.ID
ORDER BY FOREIGNK.CONSTID, FOREIGNK.KEYNO
OPEN CR_RELACIONAMENTOS
FETCH NEXT FROM CR_RELACIONAMENTOS INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID, @KEYNO
SET @VERSAOBASE = (SELECT VERSAOBASE FROM GPARAMS)
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (SELECT *
FROM ZSYSCONSTRAINTS
WHERE CONSTID = @CONSTID)
BEGIN
INSERT INTO ZSYSCONSTRAINTS (CONSTID, NOMERELAC, TABELAMAE, TABELAFILHA, CAMPOTABMAE, CAMPOTABFILHA, VERSAOBASE)
VALUES (@CONSTID, @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @VERSAOBASE)
END
ELSE
BEGIN
UPDATE ZSYSCONSTRAINTS SET CAMPOTABMAE = CAMPOTABMAE+', '+@CAMPOTABMAE,
CAMPOTABFILHA = CAMPOTABFILHA+', '+@CAMPOTABFILHA
WHERE CONSTID = @CONSTID
END
FETCH NEXT FROM CR_RELACIONAMENTOS INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID, @KEYNO
END
CLOSE CR_RELACIONAMENTOS
DEALLOCATE CR_RELACIONAMENTOS
GO
-----------------------------------------------------------
-- CRIAÇÃO DAS PROCEDURES DE GERAÇÃO DE SCRIPT
-----------------------------------------------------------
-- Gera Script de Remoção de Constraints
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE XTYPE = 'P'
AND NAME = 'SP_GERASCRIPTDROPFK')
BEGIN
DROP PROCEDURE SP_GERASCRIPTDROPFK
END
GO
CREATE PROCEDURE SP_GERASCRIPTDROPFK AS
DECLARE @NOMERELAC VARCHAR(150)
DECLARE @TABELAMAE VARCHAR(100)
DECLARE @TABELAFILHA VARCHAR(100)
DECLARE @CAMPOTABMAE VARCHAR(255)
DECLARE @CAMPOTABFILHA VARCHAR(255)
DECLARE @CONSTID INT
DECLARE @KEYNO INT
DECLARE @VERSAOBASE VARCHAR(10)
DECLARE CR_GERADROP CURSOR FOR
SELECT NOMERELAC, TABELAMAE, TABELAFILHA, CAMPOTABMAE, CAMPOTABFILHA, CONSTID, VERSAOBASE
FROM ZSYSCONSTRAINTS
ORDER BY TABELAMAE
OPEN CR_GERADROP
FETCH NEXT FROM CR_GERADROP INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- '+@NOMERELAC+' ------ '+@VERSAOBASE
PRINT 'ALTER TABLE '+@TABELAMAE+' DROP CONSTRAINT '+@NOMERELAC
PRINT 'GO'
PRINT ' '
FETCH NEXT FROM CR_GERADROP INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
END
CLOSE CR_GERADROP
DEALLOCATE CR_GERADROP
GO
-- Gera Script de Adição de Constraints
IF EXISTS (SELECT *
FROM SYSOBJECTS
WHERE XTYPE = 'P'
AND NAME = 'SP_GERASCRIPTADDFK')
BEGIN
DROP PROCEDURE SP_GERASCRIPTADDFK
END
GO
CREATE PROCEDURE SP_GERASCRIPTADDFK AS
DECLARE @NOMERELAC VARCHAR(150)
DECLARE @TABELAMAE VARCHAR(100)
DECLARE @TABELAFILHA VARCHAR(100)
DECLARE @CAMPOTABMAE VARCHAR(255)
DECLARE @CAMPOTABFILHA VARCHAR(255)
DECLARE @CONSTID INT
DECLARE @KEYNO INT
DECLARE @VERSAOBASE VARCHAR(10)
DECLARE CR_GERAADD CURSOR FOR
SELECT NOMERELAC, TABELAMAE, TABELAFILHA, CAMPOTABMAE, CAMPOTABFILHA, CONSTID, VERSAOBASE
FROM ZSYSCONSTRAINTS
ORDER BY TABELAMAE
OPEN CR_GERAADD
FETCH NEXT FROM CR_GERAADD INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT '-- '+@NOMERELAC+' ------ '+@VERSAOBASE
PRINT 'ALTER TABLE '+@TABELAMAE+' ADD CONSTRAINT '+@NOMERELAC+' FOREIGN KEY ('+@CAMPOTABMAE+') REFERENCES '+@TABELAFILHA+'('+@CAMPOTABFILHA+')'
PRINT 'GO'
PRINT ' '
FETCH NEXT FROM CR_GERAADD INTO @NOMERELAC, @TABELAMAE, @TABELAFILHA, @CAMPOTABMAE, @CAMPOTABFILHA, @CONSTID,
@VERSAOBASE
END
CLOSE CR_GERAADD
DEALLOCATE CR_GERAADD
GO
-----------------------------------------------------------
-----------------------------------------------------------
/*
-- LISTA TABELAS QUE SE RELACIONAM COM A TABELA EM QUESTÃO, CONSEQUENTEMENTE NECESSITARÃO DE DE/PARA
SELECT TABELAMAE, CAMPOTABMAE
FROM ZSYSCONSTRAINTS
WHERE TABELAFILHA = 'GCCUSTO'
ORDER BY TABELAMAE
GO
-- GERA ESTRUTURA DE DE/PARA
SELECT 'UPDATE '+TABELAMAE+' SET '+SUBSTRING(CAMPOTABMAE,PATINDEX('%,%',CAMPOTABMAE)+1,LEN(CAMPOTABMAE))+' = #<CODIGO_NOVO># '+
' WHERE '+SUBSTRING(CAMPOTABMAE,1,PATINDEX('%,%',CAMPOTABMAE)-1)+' = #<COLIGADA>#'+
' AND'+SUBSTRING(CAMPOTABMAE,PATINDEX('%,%',CAMPOTABMAE)+1,LEN(CAMPOTABMAE))+' = #<CODIGO_ANTIGO>#'+CHAR(10)+'GO'
FROM ZSYSCONSTRAINTS
WHERE TABELAFILHA = 'GCCUSTO'
ORDER BY TABELAMAE
GO
*/
