IF EXISTS (SELECT * FROM sys.foreign_keys WHERE parent_object_id = OBJECT_ID(N'[dbo].[EnderecoCadastro]') AND name = N'FK_EnderecoCadastro_enderecotipo') ALTER TABLE [dbo].[EnderecoCadastro] DROP CONSTRAINT [FK_EnderecoCadastro_enderecotipo] # IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[EnderecoTipo]') AND name = N'PK_EnderecoTipo') ALTER TABLE [dbo].[EnderecoTipo] DROP CONSTRAINT [PK_EnderecoTipo] # alter table EnderecoTipo alter column idEnderecoTipo numeric (10,0) not null # alter table EnderecoCadastro alter column idEnderecoTipo numeric (10,0) # ALTER TABLE EnderecoTipo ADD CONSTRAINT PK_EnderecoTipo PRIMARY KEY CLUSTERED ([idEnderecoTipo]) # ALTER TABLE EnderecoCadastro add CONSTRAINT FK_EnderecoCadastro_enderecotipo FOREIGN KEY (idenderecotipo) REFERENCES dbo.enderecotipo (idenderecotipo) # CREATE TABLE ISSPRECADASTRO ( IDPRECADASTRO NUMERIC(10,0) NOT NULL, CPFREQUERENTE VARCHAR(14) NOT NULL, DATAREQUISICAO DATETIME NOT NULL, CPFAUDITOR VARCHAR (14), DATAAUDITORIA DATETIME, SITUACAO CHAR(1) NOT NULL DEFAULT 'P', PES_INSCRICAOMUNICIPAL CHAR(8), PES_IDCADASTROGERAL NUMERIC(10,0), PES_PESSOA NUMERIC(10,0), PES_CNPJCPF VARCHAR(14) NOT NULL, PES_NOME VARCHAR(150) NOT NULL, PES_NOMEFANTASIA VARCHAR(60), PES_TIPOPESSOA CHAR(1) NOT NULL, PES_INSCRICAOESTADUAL VARCHAR (12), PES_NIRE VARCHAR (11), PES_DATAABERTURACNPJ DATETIME, MOB_DATAABERTURA DATETIME, MOB_NUMEROALVARA VARCHAR (10), MOB_DATAALVARA DATETIME, MOB_DATAVALIDADEALVARA DATETIME, MOB_IDNATUREZAJURIDICA INT, RGF_IDREGIMEFISCAL INT, RGF_DATAINICIO DATETIME, CONSTRAINT PK_ISSPRECADASTRO PRIMARY KEY (IDPRECADASTRO), CONSTRAINT FK_ISSPRECADASTRO_CG FOREIGN KEY (PES_IDCADASTROGERAL) REFERENCES CADASTROGERAL (IDCADASTROGERALISS), CONSTRAINT FK_ISSPRECADASTRO_PESSOA FOREIGN KEY (PES_PESSOA) REFERENCES CGCM (PESSOA), CONSTRAINT CK_ISSPRECADASTRO_TIPOPESSOA CHECK (PES_TIPOPESSOA IN ('F', 'J')), CONSTRAINT FK_ISSPRECADASTRO_NATUREZAJUR FOREIGN KEY (MOB_IDNATUREZAJURIDICA) REFERENCES NATUREZAJURIDICA (IDNATUREZAJURIDICA), CONSTRAINT FK_ISSPRECADASTRO_REGIMEFISCAL FOREIGN KEY (RGF_IDREGIMEFISCAL) REFERENCES REGIMEFISCAL (IDREGIMEFISCAL), CONSTRAINT CK_ISSPRECADASTRO_SITUACAO CHECK (SITUACAO IN ('P', 'A', 'N')) ) # CREATE TABLE ISSPRECADASTROENDERECO ( IDPRECADASTROENDERECO NUMERIC(10,0) NOT NULL, IDPRECADASTRO NUMERIC(10,0) NOT NULL, END_IDENDERECOTIPO NUMERIC(10,0) NOT NULL, END_IDBAIRRO INT, END_IDLOGRADOURO INT, END_COMPLEMENTO VARCHAR(60), END_NUMERO VARCHAR (8), END_CEP NUMERIC(10,0), CONSTRAINT PK_ISSPRECADASTROENDERECO PRIMARY KEY (IDPRECADASTROENDERECO), CONSTRAINT FK_ISSPRECADASTROEND_PRECAD FOREIGN KEY (IDPRECADASTRO) REFERENCES ISSPRECADASTRO (IDPRECADASTRO), CONSTRAINT FK_ISSPRECADASTRO_ENDTIPO FOREIGN KEY (END_IDENDERECOTIPO) REFERENCES ENDERECOTIPO(IDENDERECOTIPO), CONSTRAINT FK_ISSPRECADASTRO_ENDBAIRRO FOREIGN KEY(END_IDBAIRRO) REFERENCES BAIRRO (IDBAIRRO), CONSTRAINT FK_ISSPRECADASTRO_ENDLOGRAD FOREIGN KEY(END_IDLOGRADOURO) REFERENCES LOGRADOURO(IDLOGRADOURO) ) # CREATE TABLE ISSPRECADASTROCNAE ( IDPRECADASTROCNAE NUMERIC(10,0) NOT NULL, IDPRECADASTRO NUMERIC(10,0) NOT NULL, CNAE_CODIGO CHAR(7), CONSTRAINT PK_ISSPRECADASTROCNAE PRIMARY KEY (IDPRECADASTROCNAE), CONSTRAINT FK_ISSPRECADASTROCNAE_PRECAD FOREIGN KEY (IDPRECADASTRO) REFERENCES ISSPRECADASTRO (IDPRECADASTRO), CONSTRAINT FK_ISSPRECADASTROCNAE_CNAE FOREIGN KEY (CNAE_CODIGO) REFERENCES CNAENACIONAL (CODIGO) ) # CREATE TABLE ISSPRECADASTROCSOCIALALT ( IDPRECADASTROCSOCIALALT NUMERIC(10, 0) NOT NULL, IDPRECADASTRO NUMERIC(10,0) NOT NULL, CSS_CARTORIO VARCHAR (100), CSS_AVERBACAO VARCHAR (100), CSS_NUMEROALTERACAO INT, CSS_CAPITALSOCIAL NUMERIC (14,2), CSS_VALORCOTAS NUMERIC (14,2), CSS_IDTIPOCAPITALSOCIAL BIGINT, CSS_DATAAVERBACAO DATETIME, CSS_OBSERVACAO TEXT, CSS_JUNTACOMERCIAL VARCHAR (12), CSS_DATAABERTURAJUNTA DATETIME, CONSTRAINT PK_ISSPRECADASTROCSOCIALALT PRIMARY KEY (IDPRECADASTROCSOCIALALT), CONSTRAINT FK_ISSPRECADASTROCSA_PRECAD FOREIGN KEY (IDPRECADASTRO) REFERENCES ISSPRECADASTRO (IDPRECADASTRO), CONSTRAINT FK_ISSPRECADASTRO_TIPOCAPSOC FOREIGN KEY (CSS_IDTIPOCAPITALSOCIAL) REFERENCES TIPOCAPITALSOCIAL (ID) ) # CREATE TABLE ISSPRECADASTROSOCIO ( IDPRECADASTROSOCIO NUMERIC(10,0) NOT NULL, IDPRECADASTROCSOCIALALT NUMERIC(10, 0) NOT NULL, CSO_PESSOA NUMERIC(10,0) NULL, CSO_ADMINISTRADOR INT NOT NULL, CSO_PERCENTUALCAPITAL NUMERIC(14,2) NOT NULL, CSO_IDTIPOSOCIO NUMERIC(10,0) NOT NULL, PES_NOME VARCHAR(150) NOT NULL, PES_NOMEFANTASIA VARCHAR(60), PES_TIPOPESSOA CHAR(1) NOT NULL, PES_CNPJCPF VARCHAR(14) NOT NULl, PES_TELCOMERCIALCOR VARCHAR(30), PES_FAXCOR VARCHAR(30), PES_TELCELULARCOR VARCHAR(30), PES_EMAILCOR VARCHAR(100), PES_DATANASCIMENTO DATETIME NOT NULL, PES_NIRE VARCHAR(11), PES_INSCRICAOESTADUAL VARCHAR(12), PES_DATAABERTURACNPJ DATETIME, END_IDENDERECOTIPO NUMERIC(10,0) NOT NULL, END_IDBAIRRO INT, END_IDLOGRADOURO INT, END_COMPLEMENTO VARCHAR(60), END_NUMERO VARCHAR (8), END_CEP NUMERIC(10,0), CONSTRAINT PK_ISSPRECADASTROSOCIO PRIMARY KEY (IDPRECADASTROSOCIO), CONSTRAINT FK_ISSPRECADASTROSOCIO_CSA FOREIGN KEY (IDPRECADASTROCSOCIALALT) REFERENCES ISSPRECADASTROCSOCIALALT (IDPRECADASTROCSOCIALALT), CONSTRAINT FK_ISSPRECADASTROSOCIO_PESSOA FOREIGN KEY (CSO_PESSOA) REFERENCES CGCM (PESSOA), CONSTRAINT FK_ISSPRECADASTROSOCIO_TIPO FOREIGN KEY (CSO_IDTIPOSOCIO) REFERENCES TIPOSOCIO (CODIGO), CONSTRAINT CK_ISSPRECADASTROSOCIO_TPPES CHECK (PES_TIPOPESSOA IN ('F', 'J')), CONSTRAINT FK_ISSPRECADSOCIO_ENDTIPO FOREIGN KEY (END_IDENDERECOTIPO) REFERENCES ENDERECOTIPO(IDENDERECOTIPO), CONSTRAINT FK_ISSPRECADSOCIO_ENDBAIRRO FOREIGN KEY(END_IDBAIRRO) REFERENCES BAIRRO (IDBAIRRO), CONSTRAINT FK_ISSPRECADSOCIO_ENDLOGRAD FOREIGN KEY(END_IDLOGRADOURO) REFERENCES LOGRADOURO(IDLOGRADOURO) ) # sp_RENAME 'ISSAGENDAMENTO.CPFSOCIO', 'CPFREQUERENTE' , 'COLUMN' # ALTER TABLE ISSHISTAGENDAMENTO ADD CPFREQUERENTE CHAR(11) # UPDATE ISSHISTAGENDAMENTO SET CPFREQUERENTE = (SELECT C.CGCCPF FROM CONTRATOSOCIALSOCIO CSO JOIN CGCM C ON C.CODIGO = CSO.CGCM WHERE CSO.IDSOCIO = ISSHISTAGENDAMENTO.IDSOCIO) # ALTER TABLE ISSHISTAGENDAMENTO DROP CONSTRAINT FK_HISTAGENDAMENTO_SOCIO # DECLARE @constraint_name as varchar(1000) BEGIN select @constraint_name = 'ALTER TABLE ISSHISTAGENDAMENTO DROP CONSTRAINT ' + dc.name from sys.default_constraints dc where dc.parent_object_id = OBJECT_ID(N'[dbo].[ISSHISTAGENDAMENTO]') and dc.name like 'DF__ISSHISTAG__IDSOC__%' exec(@constraint_name) END # ALTER TABLE ISSHISTAGENDAMENTO DROP COLUMN IDSOCIO # ALTER TABLE ISSAGENDAMENTO DROP CONSTRAINT FK_AGENDAMENTO_SOCIO # DECLARE @constraint_name as varchar(1000) BEGIN select @constraint_name = 'ALTER TABLE ISSAGENDAMENTO DROP CONSTRAINT ' + dc.name from sys.default_constraints dc where dc.parent_object_id = OBJECT_ID(N'[dbo].[ISSAGENDAMENTO]') and dc.name like 'DF__ISSAGENDA__IDSOC__%' exec(@constraint_name) END # ALTER TABLE ISSAGENDAMENTO DROP COLUMN IDSOCIO # ALTER TABLE ISSAGENDAMENTO ALTER COLUMN IDCADASTRO NUMERIC(10,0) NULL # AlTER TABLE ISSAGENDAMENTO ADD IDPRECADASTRO NUMERIC(10, 0) # ALTER TABLE ISSAGENDAMENTO ADD CONSTRAINT FK_ISSAGENDAMENTO_PRECAD FOREIGN KEY (IDPRECADASTRO) REFERENCES ISSPRECADASTRO (IDPRECADASTRO) # CREATE TABLE ISSPRECADASTROSOCIDENTPRO ( IDPRECADASTROSOCIDENTPRO NUMERIC(10,0) NOT NULL, IDPRECADASTROSOCIO NUMERIC(10,0) NOT NULL, IPR_CODIGOORGAOEMISSOR INT NOT NULL, IPR_IDENTIFICACAOPROFISSIONAL VARCHAR(20), IPR_NROINSCRICAO VARCHAR(20), IPR_DATAEMISSAO DATETIME, IPR_UF CHAR(2), IPR_PAIS NUMERIC(10,0), IPR_CATEGORIAHABILITACAO VARCHAR(10), IPR_VALIDADE_HABILITACAO DATETIME, CONSTRAINT PK_ISSPRECADASTROSOCIDENTPRO PRIMARY KEY (IDPRECADASTROSOCIDENTPRO), CONSTRAINT FK_ISSPRECADSOCIDENTPRO_SOC FOREIGN KEY (IDPRECADASTROSOCIO) REFERENCES ISSPRECADASTROSOCIO (IDPRECADASTROSOCIO), CONSTRAINT FK_ISSPRECADSOCIDENTPRO_ORGAO FOREIGN KEY (IPR_CODIGOORGAOEMISSOR) REFERENCES ORGAOEMISSOR (CODIGO), CONSTRAINT FK_ISSPRECADSOCIDENTPRO_UF FOREIGN KEY (IPR_PAIS, IPR_UF) REFERENCES UNIDADEFEDERACAO(PAIS, UNIDADEFEDERACAO), CONSTRAINT UK_ISSPRECADSOCIDENTPRO_ORGAO UNIQUE (IDPRECADASTROSOCIO, IPR_CODIGOORGAOEMISSOR) ) # ALTER TABLE ISSPRECADASTROENDERECO DROP CONSTRAINT FK_ISSPRECADASTRO_ENDBAIRRO # ALTER TABLE ISSPRECADASTROENDERECO DROP CONSTRAINT FK_ISSPRECADASTRO_ENDLOGRAD # ALTER TABLE ISSPRECADASTROENDERECO DROP COLUMN END_IDBAIRRO # ALTER TABLE ISSPRECADASTROENDERECO DROP COLUMN END_IDLOGRADOURO # CREATE TABLE ISSPRECADASTROBAIRRO ( IDPRECADASTROBAIRRO NUMERIC(10,0) NOT NULL, BAI_IDBAIRRO INT, BAI_CIDADE NUMERIC(10,0) NOT NULL, BAI_DESCRICAO VARCHAR(60) NOT NULL, CONSTRAINT PK_ISSPRECADASTROBAIRRO PRIMARY KEY (IDPRECADASTROBAIRRO), CONSTRAINT FK_ISSPRECADBAIRRO FOREIGN KEY (BAI_IDBAIRRO) REFERENCES BAIRRO (IDBAIRRO), CONSTRAINT FK_ISSPRECADBAIRRO_CIDADE FOREIGN KEY (BAI_CIDADE) REFERENCES CIDADE (CIDADE) ) # CREATE TABLE ISSPRECADASTROLOGRADOURO ( IDPRECADASTROLOGRADOURO NUMERIC(10,0) NOT NULL, LOG_IDLOGRADOURO INT, LOG_CIDADE NUMERIC(10,0) NOT NULL, LOG_TIPOLOGRADOURO NUMERIC(10,0) NOT NULL, LOG_DESCRICAO VARCHAR(60) NULL, CONSTRAINT PK_ISSPRECADASTROLOGRADOURO PRIMARY KEY (IDPRECADASTROLOGRADOURO), CONSTRAINT FK_ISSPRECADASTROLOG_LOG FOREIGN KEY (LOG_IDLOGRADOURO) REFERENCES LOGRADOURO (IDLOGRADOURO), CONSTRAINT FK_ISSPRECADASTROLOG_CID FOREIGN KEY (LOG_CIDADE) REFERENCES CIDADE (CIDADE), CONSTRAINT FK_ISSPRECADASTROLOG_TIP FOREIGN KEY (LOG_TIPOLOGRADOURO) REFERENCES TIPOLOGRADOURO (TIPOLOGRADOURO) ) # ALTER TABLE ISSPRECADASTROENDERECO ADD IDPRECADASTROBAIRRO NUMERIC(10,0) # ALTER TABLE ISSPRECADASTROENDERECO ADD IDPRECADASTROLOGRADOURO NUMERIC(10,0) # ALTER TABLE ISSPRECADASTROENDERECO ADD CONSTRAINT FK_ISSPRECADASTROEND_BAI FOREIGN KEY (IDPRECADASTROBAIRRO) REFERENCES ISSPRECADASTROBAIRRO (IDPRECADASTROBAIRRO) # ALTER TABLE ISSPRECADASTROENDERECO ADD CONSTRAINT FK_ISSPRECADASTROEND_LOG FOREIGN KEY (IDPRECADASTROLOGRADOURO) REFERENCES ISSPRECADASTROLOGRADOURO (IDPRECADASTROLOGRADOURO) # ALTER TABLE ISSPRECADASTROCSOCIALALT ADD CSS_SEQUENCIA INT # ALTER TABLE ISSPRECADASTROCSOCIALALT ADD CONSTRAINT FK_ISSPRECADASTROCSA_CS FOREIGN KEY (CSS_SEQUENCIA) REFERENCES CONTRATOSOCIALALTERACAO (SEQUENCIA) # ALTER TABLE ISSPRECADASTROSOCIO ADD CSO_SEQUENCIA INT # ALTER TABLE ISSPRECADASTROSOCIO DROP CONSTRAINT FK_ISSPRECADSOCIO_ENDBAIRRO # ALTER TABLE ISSPRECADASTROSOCIO DROP CONSTRAINT FK_ISSPRECADSOCIO_ENDLOGRAD # ALTER TABLE ISSPRECADASTROSOCIO DROP COLUMN END_IDBAIRRO # ALTER TABLE ISSPRECADASTROSOCIO DROP COLUMN END_IDLOGRADOURO # ALTER TABLE ISSPRECADASTROSOCIO ADD IDPRECADASTROBAIRRO NUMERIC(10,0) # ALTER TABLE ISSPRECADASTROSOCIO ADD IDPRECADASTROLOGRADOURO NUMERIC(10,0) # ALTER TABLE ISSPRECADASTROSOCIO ADD CONSTRAINT FK_ISSPRECADASTROSOCIO_BAI FOREIGN KEY (IDPRECADASTROBAIRRO) REFERENCES ISSPRECADASTROBAIRRO (IDPRECADASTROBAIRRO) # ALTER TABLE ISSPRECADASTROSOCIO ADD CONSTRAINT FK_ISSPRECADASTROSOCIO_LOG FOREIGN KEY (IDPRECADASTROLOGRADOURO) REFERENCES ISSPRECADASTROLOGRADOURO (IDPRECADASTROLOGRADOURO) # ALTER TABLE ISSPRECADASTRO ADD MOB_ISENCAO CHAR(1) #