SET NOCOUNT ON

BEGIN TRAN

BEGIN TRY

	IF SCHEMA_ID(N'DBO') IS NULL EXECUTE(N'CREATE SCHEMA DBO');

	/*
	-------------------------------------------------------------------------------------------------------------------------------------
	-- CURSOR COM A ESTRUTURA DAS TABELAS
	-------------------------------------------------------------------------------------------------------------------------------------
	*/


	DECLARE @TABELAS TABLE (TABLE_NAME VARCHAR(100)
							, COLUMN_NAME VARCHAR(100)
							, ORDINAL_POSITION INT
							, COLUMN_DEFAULT VARCHAR(100)
							, IS_NULLABLE VARCHAR(10)
							, DATA_TYPE VARCHAR(100)
							, CHARACTER_MAXIMUM_LENGTH INT
							, NUMERIC_PRECISION INT
							, NUMERIC_SCALE INT
							,PRIMARYKEY INT
							,IDENT VARCHAR(100))
	-- LJ_ETL_ARQUIVO
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'ID_ETL_TIPO',  1, '', 'NO', 'SMALLINT', 0, 5, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'CODIGO_FILIAL',  2, '', 'NO', 'CHAR', 6, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'ID_ETL_ARQUIVO',  3, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'DATA_HORA_CRIACAO',  4, '', 'NO', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'XML_ARQUIVO',  5, '', 'NO', 'VARCHAR(MAX)', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'PROCESSADO',  6, '', 'NO', 'SMALLINT', 0, 5, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'DATA_HORA_PROXIMO_PROCESSO',  7, '', 'YES', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'VERSAO',  8, '', 'YES', 'VARCHAR', 10, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'PREFIXO_ERP_ORIGEM',  9, 'DEFAULT('''')', 'NO', 'VARCHAR', 5, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'ID_MSG_ENSEMBLE',  10, '', 'YES', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'PRIORIDADE',  11, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'DATA_HORA_INICIO',  12, '', 'YES', 'DATETIME', 0, 0, 0, 0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ARQUIVO',  'QTDE_REGISTROS',  13, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0, 0,'')

	-- LJ_ETL_CARGA
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'ID_TABELA',  1, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'TABELA',  2, '', 'NO', 'VARCHAR', 100, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'TABELA_JOIN',  3, '', 'NO', 'VARCHAR', 500, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'SOMENTE_MASTER',  4, '', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'DATA_INICIAL_VENDAS',  5, '', 'YES', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'DATA_HORA_ULTIMA_CARGA',  6, '', 'YES', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'FILTRO',  7, '', 'YES', 'VARCHAR', 8000, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA',  'INATIVO',  8, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')

	-- LJ_ETL_CARGA_COLUNA
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'ID_TABELA_COLUNA',  1, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'ID_TABELA',  2, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'COLUNA',  3, '', 'NO', 'VARCHAR', 200, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'CHAVE',  4, '', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'TIPO_DADO',  5, '', 'NO', 'NVARCHAR', 26, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'TAMANHO',  6, '', 'YES', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'NUMERICO_PRECISAO',  7, '', 'YES', 'TINYINT', 0, 3, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CARGA_COLUNA',  'NUMERICO_ESCALA',  8, '', 'YES', 'INT', 0, 10, 0,0,'')

	-- LJ_ETL_CONFERENCIA
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CONFERENCIA',  'ID',  1, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CONFERENCIA',  'CODIGO_FILIAL',  2, '', 'NO', 'NVARCHAR', 6, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CONFERENCIA',  'FILIAL',  3, '', 'NO', 'NVARCHAR', 25, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_CONFERENCIA',  'DATA_MOVIMENTO',  4, '', 'NO', 'DATETIME', 0, 0, 0,0,'')

	-- LJ_ETL_ESTATISTICA
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ESTATISTICA',  'CODIGO_FILIAL',  1, '', 'NO', 'CHAR', 6, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ESTATISTICA',  'DATA_ESTATISTICA',  2, '', 'NO', 'DATETIME', 0, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ESTATISTICA',  'ID_ETL_TIPO',  3, '', 'NO', 'SMALLINT', 0, 5, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ESTATISTICA',  'QTDE_PROCESSADA',  4, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ESTATISTICA',  'QTDE_ERRO',  5, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_ESTATISTICA',  'DATA_HORA_ULTIMO_PROCESSO',  6, '', 'YES', 'DATETIME', 0, 0, 0,0,'')

	-- LJ_ETL_LOG
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG',  'ID_ETL_LOG',  1, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG',  'CODIGO_FILIAL',  2, '', 'YES', 'CHAR', 6, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG',  'METODO',  3, '', 'NO', 'VARCHAR', 500, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG',  'ERRO',  4, '', 'YES', 'TEXT', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG',  'DATA_HORA_LOG',  5, '', 'YES', 'DATETIME', 0, 0, 0,0,'')

	-- LJ_ETL_LOG_STATUS
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'ID_ETL_LOG_STATUS',  1, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'CODIGO_FILIAL',  2, '', 'NO', 'CHAR', 6, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'ID_ETL_ARQUIVO',  3, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'ID_ETL_TIPO',  4, '', 'NO', 'SMALLINT', 0, 5, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'COD_RETORNO',  5, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'CRITICA_PROCESSO',  6, '', 'NO', 'TEXT', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'XML_ARQUIVO',  7, '', 'YES', 'VARCHAR(MAX)', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_LOG_STATUS',  'QTDE_REGISTROS',  8, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0, 0,'')

	-- LJ_ETL_MONITOR
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'CODIGO_FILIAL',  1, '', 'NO', 'NVARCHAR', 6, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'FILIAL',  2, '', 'NO', 'NVARCHAR', 25, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'EMPRESA',  3, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'UF',  4, '', 'NO', 'CHAR', 2, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA',  5, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'MES',  6, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'ANO',  7, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA1',  8, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA2',  9, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA3',  10, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA4',  11, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA5',  12, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA6',  13, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA7',  14, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA8',  15, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA9',  16, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA10',  17, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA11',  18, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA12',  19, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA13',  20, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA14',  21, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA15',  22, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA16',  23, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA17',  24, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA18',  25, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA19',  26, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA20',  27, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA21',  28, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA22',  29, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA23',  30, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA24',  31, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA25',  32, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA26',  33, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA27',  34, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA28',  35, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA29',  36, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA30',  37, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'DIA31',  38, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR',  'ID',  39, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')

	-- LJ_ETL_MONITOR_DETALHES
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'CODIGO_FILIAL',  1, '', 'NO', 'NVARCHAR', 6, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'FILIAL',  2, '', 'NO', 'NVARCHAR', 25, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'LANCAMENTO_CAIXA',  3, '', 'YES', 'NVARCHAR', 7, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'MENSAGEM',  4, '', 'YES', 'NVARCHAR(MAX)', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'ID_ETL_ARQUIVO',  5, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'ID_ETL_PROCESSO',  6, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'TICKET',  7, '', 'YES', 'NVARCHAR', 8, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'TERMINAL',  8, '', 'YES', 'NVARCHAR', 3, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'STATUS_MOVIMENTO',  9, '', 'YES', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'ORIGEM',  10, '', 'YES', 'NVARCHAR', 100, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'DATA_PROCESSO',  11, '', 'YES', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'DIA',  12, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'MES',  13, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'ANO',  14, '', 'NO', 'INT', 0, 10, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_MONITOR_DETALHES',  'ID',  15, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')

	-- LJ_ETL_PROCESSO
	INSERT INTO @TABELAS VALUES ('LJ_ETL_PROCESSO',  'ID_ETL_PROCESSO',  1, '', 'NO', 'INT', 0, 10, 0,1,'IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_PROCESSO',  'CRITICA_PROCESSO',  2, '', 'YES', 'TEXT', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_PROCESSO',  'ID_ETL_ARQUIVO',  3, '', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_PROCESSO',  'COD_RETORNO',  4, 'DEFAULT(0)', 'NO', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_PROCESSO',  'DATA_HORA_PROCESSO',  5, '', 'NO', 'DATETIME', 0, 0, 0,0,'')

	-- LJ_ETL_REPOSITORIO
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'ID',  1, '', 'NO', 'BIGINT', 0, 19, 0,1,'IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'TABELA',  2, '', 'NO', 'VARCHAR', 50, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'FILTRO',  3, '', 'NO', 'VARCHAR', 500, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'ID_ETL_TIPO',  4, '', 'NO', 'SMALLINT', 0, 5, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'INDICA_EXCLUSAO',  5, '', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'CODIGO_FILIAL',  6, '', 'YES', 'CHAR', 6, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'DATA_HORA_CRIACAO',  7, 'DEFAULT GETDATE()', 'NO', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'VERSAO',  8, '', 'YES', 'VARCHAR', 50, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_ETL_REPOSITORIO',  'USUARIO',  9, 'DEFAULT SYSTEM_USER', 'YES', 'VARCHAR', 100, 0, 0,0,'')

	-- LJ_LX_ETL_TIPO
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'DESC_ETL_TIPO',  1, '', 'NO', 'VARCHAR', 40, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'EXCLUI_PROCESSO_CONCLUIDO',  2, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'ID_ETL_TIPO',  3, '', 'NO', 'SMALLINT', 0, 5, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'INATIVO',  4, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'ORIGEM',  5, 'DEFAULT(1)', 'NO', 'TINYINT', 0, 3, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'DESC_EAD',  6, '', 'YES', 'VARCHAR', 150, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'DESC_EAD_DESTINO',  7, '', 'YES', 'VARCHAR', 150, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'UTILIZA_LOTE',  8, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'ULTIMO_LOTE',  9, 'DEFAULT(0)', 'YES', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'PERIODO_FECHAMENTO_LOTE',  10, 'DEFAULT(0)', 'YES', 'INT', 0, 10, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'DATA_HORA_ULTIMO_LOTE',  11, '', 'YES', 'DATETIME', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'FILTRO',  12, 'DEFAULT('''')', 'YES', 'TEXT', '', 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'INDICA_SOMENTE_MASTER',  13, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'IND_SOMENTE_ENTIDADE_PRINCIPAL',  14, 'DEFAULT(0)', 'NO', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'TOTAL_REGISTROS_ARQUIVO',  15, '', 'YES', 'TINYINT', 0, 3, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'CTRL_EMPRESA',  16, '', 'YES', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'CTRL_REDE',  17, '', 'YES', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'CTRL_FILIAL',  18, '', 'YES', 'BIT', 0, 0, 0,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'INICIO_PROCESSAMENTO_LOTE',  19, 'DEFAULT(0.0)', 'NO', 'DECIMAL', 0, 5, 2,0,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO',  'FIM_PROCESSAMENTO_LOTE',  20, 'DEFAULT(23.59)', 'NO', 'DECIMAL', 0, 5, 2,0,'')
	INSERT INTO @TABELAS VALUES('LJ_LX_ETL_TIPO','ORDEM',21,'','YES','INT','','','','','')
	
	-- LJ_LX_ETL_TIPO_FILIAL
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO_FILIAL',  'ID_ETL_TIPO',  1, '', 'NO', 'SMALLINT', 0, 5, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO_FILIAL',  'CODIGO_FILIAL',  2, '', 'NO', 'NVARCHAR', 6, 0, 0,1,'')
	INSERT INTO @TABELAS VALUES ('LJ_LX_ETL_TIPO_FILIAL',  'ULTIMO_LOTE',  3, '', 'NO', 'INT', 0, 10, 0,0,'')

	-- LJ_ETL_TIPO_COMPONENTE
	INSERT INTO @TABELAS VALUES('LJ_ETL_TIPO_COMPONENTE','ID_ETL_TIPO_COMPONENTE','1','','NO','SMALLINT','0','5','0','1','IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES('LJ_ETL_TIPO_COMPONENTE','ID_ETL_TIPO','2','','NO','SMALLINT','0','5','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_TIPO_COMPONENTE','DESC_EAD','3','','YES','VARCHAR','150','0','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_TIPO_COMPONENTE','DESC_EAD_DESTINO','4','','YES','VARCHAR','150','0','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_TIPO_COMPONENTE','VERSAO','5','','YES','VARCHAR','150','0','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_TIPO_COMPONENTE','INATIVO','6','','NO','BIT','','0','0','0','')

	-- LJ_ETL_VERSAO_LOJA
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','ID_VERSAO_LOJA','1','','NO','INT','','','0','1','IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','VERSAO','2','','YES','VARCHAR','50','0','0','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','CODIGO_FILIAL','3','','NO','CHAR','6','0','0','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','FILIAL','4','','YES','VARCHAR','50','0','0','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','INATIVO','5','DEFAULT (0)','YES','BIT','','0','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','DATA_HORA_COMUNICACAO','6','DEFAULT (getdate())','YES','datetime','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_VERSAO_LOJA','VERSAOBUILD','7','','YES','VARCHAR','60','0','0','','')
	
	-- LJ_ETL_INSTANCIA	
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA','ID_ETL_INSTANCIA','1','','NO','smallint','','','','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA','DESCRICAO','2','','NO','VARCHAR','50','0','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA','FATOR_PROCESSADOR','3','DEFAULT (10)','NO','smallint','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA','PERIODO_ARQUIVO','4','DEFAULT (0)','NO','smallint','','','','','')

	-- LJ_ETL_INSTANCIA_ACAO	
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO','ID_ETL_INSTANCIA_ACAO','1','','NO','smallint','','','','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO','ID_ETL_INSTANCIA','2','','NO','smallint','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO','DESCRICAO','3','','NO','VARCHAR','50','0','0','0','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO','QTDE_REGISTRO','4','DEFAULT (100)','NO','smallint','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO','QTDE_REPETICAO','5','DEFAULT (1000)','NO','smallint','','','','','')

	-- LJ_ETL_INSTANCIA_ACAO_TIPO	
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO_TIPO','ID_ETL_INSTANCIA_ACAO','1','','NO','smallint','','','','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_ACAO_TIPO','ID_ETL_TIPO','2','','NO','smallint','','','','1','')

	-- LJ_ETL_INSTANCIA_LOJA
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_LOJA','ID_ETL_INSTANCIA','1','','NO','smallint','','','','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_INSTANCIA_LOJA','CODIGO_FILIAL','2','','NO','CHAR','6','','','1','')

	--- LJ_ETL_ESTATISTICA_ARQUIVO
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','ID_ETL_ESTATISTICA_ARQUIVO','1','','NO','INT','','','','1','IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','ID_ETL_TIPO','2','','NO','smallint','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','ID_ETL_ARQUIVO','3','','YES','INT','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','CODIGO_FILIAL','4','','NO','CHAR','6','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','DATA_ESTATISTICA','5','DEFAULT (getdate())','NO','datetime','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','DATA_HORA_INICIO','6','DEFAULT (getdate())','NO','datetime','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','DATA_HORA_FIM','7','','NO','datetime','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','QTDE_REGISTROS','8','','NO','INT','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','SUCESSO','9','DEFAULT (1)','NO','BIT','','','','','')

	--- LJ_ETL_LOJA_VENDA_NAO_FINALIZADA
	INSERT INTO @TABELAS VALUES('LJ_ETL_LOJA_VENDA_NAO_FINALIZADA','CODIGO_FILIAL','1','','NO','CHAR','6','','','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_LOJA_VENDA_NAO_FINALIZADA','TICKET','2','','NO','CHAR','8','','','1','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_LOJA_VENDA_NAO_FINALIZADA','DATA_VENDA','3','','NO','datetime','','','','1','')

	--- LJ_ETL_REQUISICAO_LOJA
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','ID_ETL_REQUISICAO_LOJA','1','','NO','INT','','','','1','IDENTITY(1,1)')
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','CODIGO_FILIAL','2','','NO','CHAR','6','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','DATA_HORA_REQUISICAO','3','','NO','datetime','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','DATA_HORA_CONCLUSAO','4','','YES','datetime','','','','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','STATUS_REQUISICAO','5','DEFAULT(0)','NO','SMALLINT','0','5','0','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','ERRO_REQUISICAO','6','','YES','TEXT','','0','0','','')
	INSERT INTO @TABELAS VALUES('LJ_ETL_REQUISICAO_LOJA','FILTRO','7','DEFAULT('''')','NO','VARCHAR','500','0','0','0','')



	-------------------------------------------------------------------------------------------------------
	--------------------------- ESTRUTURA DE NOMES DAS TABELAS------------------------------------------------
	-------------------------------------------------------------------------------------------------------
	DECLARE @TABELA TABLE (TABLE_NAME VARCHAR(100))
	INSERT INTO @TABELA VALUES ('LJ_ETL_ARQUIVO')

	-- LJ_ETL_CARGA
	INSERT INTO @TABELA VALUES ('LJ_ETL_CARGA')

	-- LJ_ETL_CARGA_COLUNA
	INSERT INTO @TABELA VALUES ('LJ_ETL_CARGA_COLUNA')

	-- LJ_ETL_CONFERENCIA
	INSERT INTO @TABELA VALUES ('LJ_ETL_CONFERENCIA')

	-- LJ_ETL_ESTATISTICA
	INSERT INTO @TABELA VALUES ('LJ_ETL_ESTATISTICA')

	-- LJ_ETL_LOG
	INSERT INTO @TABELA VALUES ('LJ_ETL_LOG')

	-- LJ_ETL_LOG_STATUS
	INSERT INTO @TABELA VALUES ('LJ_ETL_LOG_STATUS')

	-- LJ_ETL_MONITOR
	INSERT INTO @TABELA VALUES ('LJ_ETL_MONITOR')

	-- LJ_ETL_MONITOR_DETALHES
	INSERT INTO @TABELA VALUES ('LJ_ETL_MONITOR_DETALHES')

	-- LJ_ETL_PROCESSO
	INSERT INTO @TABELA VALUES ('LJ_ETL_PROCESSO')

	-- LJ_ETL_REPOSITORIO
	INSERT INTO @TABELA VALUES ('LJ_ETL_REPOSITORIO')

	-- LJ_LX_ETL_TIPO
	INSERT INTO @TABELA VALUES ('LJ_LX_ETL_TIPO')

	-- LJ_LX_ETL_TIPO_FILIAL
	INSERT INTO @TABELA VALUES ('LJ_LX_ETL_TIPO_FILIAL')

	-- LJ_ETL_TIPO_COMPONENTE
	INSERT INTO @TABELA VALUES ('LJ_ETL_TIPO_COMPONENTE')

	-- LJ_ETL_VERSAO_LOJA
	INSERT INTO @TABELA VALUES ('LJ_ETL_VERSAO_LOJA')

	-- LJ_ETL_INSTANCIA
	INSERT INTO @TABELA VALUES ('LJ_ETL_INSTANCIA')


	-- LJ_ETL_INSTANCIA_ACAO
	INSERT INTO @TABELA VALUES ('LJ_ETL_INSTANCIA_ACAO')

	
	-- LJ_ETL_INSTANCIA_ACAO_TIPO
	INSERT INTO @TABELA VALUES ('LJ_ETL_INSTANCIA_ACAO_TIPO')

		-- LJ_ETL_INSTANCIA_LOJA
	INSERT INTO @TABELA VALUES ('LJ_ETL_INSTANCIA_LOJA')
	
	--- LJ_ETL_ESTATISTICA_ARQUIVO
	INSERT INTO @TABELA VALUES ('LJ_ETL_ESTATISTICA_ARQUIVO')


	--- LJ_ETL_LOJA_VENDA_NAO_FINALIZADA
	INSERT INTO @TABELA VALUES ('LJ_ETL_LOJA_VENDA_NAO_FINALIZADA')

	       --- LJ_ETL_REQUISICAO_LOJA
    INSERT INTO @TABELA VALUES ('LJ_ETL_REQUISICAO_LOJA')


	-------------------------------------------------------------------------------------------------------
	--------------------------- ESTRUTURA DAS  FOREIGN KEY ------------------------------------------------
	-------------------------------------------------------------------------------------------------------
	DECLARE @FOREIGNKEY TABLE (TABLE_NAME VARCHAR(100)
							, CONSTRAINT_NAME VARCHAR(100)						
							, FOREIGNKEY_NAME VARCHAR(100)
							,REFERENCES_NAME VARCHAR(100)
							)

	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ARQUIVO','FK_LJ_ETL_ARQUIVO_LJ_LX_ETL_TIPO','ID_ETL_TIPO','LJ_LX_ETL_TIPO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_CARGA_COLUNA','FK_LJ_ETL_CARGA_COLUNA_LJ_ETL_CARGA','ID_TABELA','LJ_ETL_CARGA')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ESTATISTICA','XFK2777_LJ_ETL_ESTATISTICA','ID_ETL_TIPO','LJ_LX_ETL_TIPO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_PROCESSO','XFK2590_LJ_ETL_PROCESSO','ID_ETL_ARQUIVO','LJ_ETL_ARQUIVO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_LX_ETL_TIPO_FILIAL','FK_LJ_LX_ETL_TIPO_FILIAL_LJ_LX_ETL_TIPO','ID_ETL_TIPO','LJ_LX_ETL_TIPO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ARQUIVO','FK_LJ_ETL_ARQUIVO_LOJAS_VAREJO','CODIGO_FILIAL','LOJAS_VAREJO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ESTATISTICA','FK_LJ_ETL_ESTATISTICA_LOJAS_VAREJO','CODIGO_FILIAL','LOJAS_VAREJO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_TIPO_COMPONENTE','FK_LJ_ETL_TIPO_COMPONENTE_LJ_LX_ETL_TIPO','ID_ETL_TIPO','LJ_LX_ETL_TIPO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_INSTANCIA_ACAO','FK_LJ_ETL_INSTANCIA_ACAO_LJ_ETL_INSTANCIA','ID_ETL_INSTANCIA','LJ_ETL_INSTANCIA')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_INSTANCIA_ACAO_TIPO','FK_LJ_ETL_INSTANCIA_ACAO_TIPO_LJ_ETL_INSTANCIA_ACAO','ID_ETL_INSTANCIA_ACAO','LJ_ETL_INSTANCIA_ACAO')
	INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_INSTANCIA_LOJA','FK_LJ_ETL_INSTANCIA_LOJA_LJ_ETL_INSTANCIA','ID_ETL_INSTANCIA','LJ_ETL_INSTANCIA')

	--INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','FK_LJ_ETL_ESTATISTICA_ARQUIVO_LJ_ETL_ARQUIVO','ID_ETL_ARQUIVO','LJ_ETL_ARQUIVO')
	--- comentado pois criamos esta fk no script 004
	--INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','FK_LJ_ETL_ESTATISTICA_ARQUIVO_LJ_LX_ETL_TIPO','ID_ETL_TIPO','LJ_LX_ETL_TIPO')
	--INSERT INTO @FOREIGNKEY VALUES('LJ_ETL_ESTATISTICA_ARQUIVO','FK_LJ_ETL_ESTATISTICA_ARQUIVO_LOJAS_VAREJO','CODIGO_FILIAL','LOJAS_VAREJO')



	-------------------------------------------------------------------------------------------------------
	--------------------------- ESTRUTURA DOS  INDEX ------------------------------------------------
	-------------------------------------------------------------------------------------------------------

	DECLARE @INDEX_TABLE TABLE (INDEX_NAME VARCHAR(100)
							,TABLE_NAME VARCHAR(100)						
							, COLUMN_NAME VARCHAR(100)
							, INCLUDE_NAME VARCHAR(500	)
							)

	INSERT INTO @INDEX_TABLE VALUES('IX_ID_ETL_TIPO','LJ_ETL_ARQUIVO','ID_ETL_TIPO','')

	INSERT INTO @INDEX_TABLE VALUES('IX_LJ_ETL_ARQUIVO','LJ_ETL_ARQUIVO','CODIGO_FILIAL,VERSAO,PRIORIDADE','[DATA_HORA_PROXIMO_PROCESSO],[ID_ETL_TIPO],[PROCESSADO],XML_ARQUIVO')

	INSERT INTO @INDEX_TABLE VALUES('IX_ID_ETL_ARQUIVO','LJ_ETL_PROCESSO','ID_ETL_ARQUIVO','[COD_RETORNO]')

	INSERT INTO @INDEX_TABLE VALUES('IX_FILTRO','LJ_ETL_REPOSITORIO','FILTRO','')

	INSERT INTO @INDEX_TABLE VALUES('IX_TIPO_FILTRO_VERSAO','LJ_ETL_REPOSITORIO','ID_ETL_TIPO ASC, FILTRO ASC, VERSAO','')

	INSERT INTO @INDEX_TABLE VALUES('IX_ID_ETL_TIPO','LJ_ETL_REPOSITORIO','ID_ETL_TIPO','')	

	INSERT INTO @INDEX_TABLE VALUES('IX_LJ_ETL_ESTATISTICA','LJ_ETL_ESTATISTICA','CODIGO_FILIAL ASC,DATA_ESTATISTICA ASC,ID_ETL_TIPO','')

	INSERT INTO @INDEX_TABLE VALUES('IX_LJ_ETL_INSTANCIA','LJ_ETL_INSTANCIA_ACAO','ID_ETL_INSTANCIA','')
		
	INSERT INTO @INDEX_TABLE VALUES('IX_ETL_TIPO','LJ_ETL_INSTANCIA_ACAO_TIPO','ID_ETL_TIPO','')

	INSERT INTO @INDEX_TABLE VALUES('IX_ORIGEM_INATIVO','LJ_LX_ETL_TIPO','INATIVO,ORIGEM','')
	INSERT INTO @INDEX_TABLE VALUES('IX_ETL_TIPO','LJ_ETL_TIPO_COMPONENTE','INATIVO,DESC_EAD_DESTINO,DESC_EAD,VERSAO','')

	INSERT INTO @INDEX_TABLE VALUES('IX_TABELA_DATA_HORA_CRIACAO','LJ_ETL_REPOSITORIO','TABELA,DATA_HORA_CRIACAO, VERSAO','FILTRO, ID_ETL_TIPO')


	--tabela de outro sistema
	--INSERT INTO @INDEX_TABLE VALUES('IX_LOJAS_ENTRADAS_ETL','LOJA_ENTRADAS','ENTRADA_CONFERIDA','[ROMANEIO_PRODUTO],[FILIAL]')
	--


	--/*
	----------------------------------------------------------------------------------------------------------------------------------------------------------
	---- CRIAÇÃO DOS CAMPOS PARA ATUALIZAÇÃO DA VERSÃO
	----------------------------------------------------------------------------------------------------------------------------------------------------------
	--*/


	DECLARE	@TABLE_NAME VARCHAR(100)
	SET  @TABLE_NAME = ''
	DECLARE @EXCLUIU BIT
	SET @EXCLUIU = 0
			
	DECLARE curTables CURSOR FOR
	SELECT		*
		FROM	@TABELA

	OPEN curTables

	FETCH NEXT FROM curTables INTO	@TABLE_NAME
								
	WHILE @@FETCH_STATUS = 0
	BEGIN

		DECLARE @SQLEXEC VARCHAR(MAX)
		SET @SQLEXEC = ''

		--SE NAO EXISTIR A TABELA, ELE CRIA. ELSE DELETA TODAS AS CONS,FORN E PRIM
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].['+ @TABLE_NAME +']') AND type in (N'U'))
			BEGIN
			
				SET @SQLEXEC = 'CREATE TABLE [dbo].['+ @TABLE_NAME +']( '
				
				DECLARE
				--@TABLE_NAME		
				@COLUMN_NAME VARCHAR(100)
				, @ORDINAL_POSITION INT
				, @COLUMN_DEFAULT VARCHAR(100)
				, @IS_NULLABLE VARCHAR(10)
				, @DATA_TYPE VARCHAR(100)
				, @CHARACTER_MAXIMUM_LENGTH INT
				, @NUMERIC_PRECISION INT
				, @NUMERIC_SCALE INT
				, @PRIMARYKEY INT
				, @IDENT VARCHAR(100)

				DECLARE curTablesCol CURSOR FOR
				SELECT		*
					FROM	@TABELAS T WHERE T.TABLE_NAME = @TABLE_NAME

				DECLARE @TOTALCOLUNA INT
				SET @TOTALCOLUNA = 0
				SET @TOTALCOLUNA = (SELECT COUNT(TAB.COLUMN_NAME) FROM @TABELAS TAB WHERE TAB.TABLE_NAME = @TABLE_NAME)
				DECLARE @CONTADOR  AS INT
				SET @CONTADOR = 0

				OPEN curTablesCol

				FETCH NEXT FROM curTablesCol INTO	@TABLE_NAME
												, @COLUMN_NAME
												, @ORDINAL_POSITION
												, @COLUMN_DEFAULT
												, @IS_NULLABLE
												, @DATA_TYPE
												, @CHARACTER_MAXIMUM_LENGTH
												, @NUMERIC_PRECISION
												, @NUMERIC_SCALE
												, @PRIMARYKEY
												, @IDENT

				WHILE @@FETCH_STATUS = 0
				BEGIN
					SET @CONTADOR = @CONTADOR + 1

					SET @SQLEXEC = @SQLEXEC + @COLUMN_NAME
					IF(@CHARACTER_MAXIMUM_LENGTH = 0 AND UPPER(@DATA_TYPE) != 'DECIMAL' AND UPPER(@DATA_TYPE) != 'NUMERIC')
						BEGIN
							SET @SQLEXEC = @SQLEXEC + ' ' + @DATA_TYPE
						END
					ELSE
						BEGIN
							IF(UPPER(@DATA_TYPE) = 'DECIMAL' OR UPPER(@DATA_TYPE) = 'NUMERIC')
								BEGIN
									SET @SQLEXEC = @SQLEXEC + ' ' + @DATA_TYPE + '(' + CAST(@NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(@NUMERIC_SCALE AS VARCHAR) + ')'
								END
							ELSE
								BEGIN
									SET @SQLEXEC = @SQLEXEC + ' ' + @DATA_TYPE + '(' + CAST(@CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
								END
						END

					IF(@IDENT != '')
						BEGIN
							SET @SQLEXEC = @SQLEXEC + ' ' + @IDENT
						END
					IF(@COLUMN_DEFAULT != '')
						BEGIN
							SET @SQLEXEC = @SQLEXEC + ' CONSTRAINT [DF_' + @TABLE_NAME + '_' + @COLUMN_NAME + '] ' + @COLUMN_DEFAULT
						END
					IF(UPPER(@IS_NULLABLE) = 'YES')
						BEGIN
							SET @SQLEXEC = @SQLEXEC + ' ' + 'NULL'
						END					
					ELSE
						BEGIN
							SET @SQLEXEC = @SQLEXEC + ' ' + 'NOT NULL'
						END
														
					SET @SQLEXEC = @SQLEXEC + ','	
									
					IF(@TOTALCOLUNA = @CONTADOR)
						BEGIN
							DECLARE @PRIMARY VARCHAR(MAX)
							SET @PRIMARY = ''
										
							IF((SELECT COUNT(P.COLUMN_NAME) FROM @TABELAS P WHERE P.TABLE_NAME = @TABLE_NAME AND P.PRIMARYKEY = 1) >= 2)
								BEGIN
									SELECT @PRIMARY =  @PRIMARY +  COALESCE(P.COLUMN_NAME, '') + ',' FROM @TABELAS P WHERE P.TABLE_NAME = @TABLE_NAME AND P.PRIMARYKEY = 1
									set  @PRIMARY = SUBSTRING(@PRIMARY,1, LEN(@PRIMARY)-1)
								END
							ELSE
								BEGIN
									SET @PRIMARY = (SELECT P.COLUMN_NAME FROM @TABELAS P WHERE P.TABLE_NAME = @TABLE_NAME AND P.PRIMARYKEY = 1)
								END

							SET @SQLEXEC = @SQLEXEC + 'CONSTRAINT [PK_'+ @TABLE_NAME +'] PRIMARY KEY CLUSTERED 
										(
											'+ @PRIMARY + ' ASC
										)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])' 

							--SET @SQLEXEC = SUBSTRING(@SQLEXEC,1, LEN(@SQLEXEC)-1)
										
						
							EXEC(@SQLEXEC)
						END

					FETCH NEXT FROM curTablesCol INTO	@TABLE_NAME
					, @COLUMN_NAME
					, @ORDINAL_POSITION
					, @COLUMN_DEFAULT
					, @IS_NULLABLE
					, @DATA_TYPE
					, @CHARACTER_MAXIMUM_LENGTH
					, @NUMERIC_PRECISION
					, @NUMERIC_SCALE
					, @PRIMARYKEY
					, @IDENT

				END
								
				CLOSE curTablesCol;
				DEALLOCATE curTablesCol;
							
			END -- END DO BEGIN DO IF NOT EXISTE TABELA

		ELSE -- ELSE(JA EXISTE A TABELA)

			BEGIN

					IF(@EXCLUIU != 1)
					BEGIN

						--------------------------------------------------------------------------------------------------------------------------------------------------------
						----------------------------------- EXCLUSÃO DOS ÍNDICES -----------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------

						PRINT('EXCLUSÃO DOS ÍNDICES ')
						BEGIN

							DECLARE @INDEX_NAME_INDEX VARCHAR(100)
							SET @INDEX_NAME_INDEX =''
							DECLARE curNOMEI CURSOR FOR
							SELECT T.INDEX_NAME FROM @INDEX_TABLE T
											
							OPEN curNOMEI

							FETCH NEXT FROM curNOMEI INTO @INDEX_NAME_INDEX

							WHILE @@FETCH_STATUS = 0
							BEGIN


								DECLARE @TableNamei VARCHAR(1000), @IndexNameI VARCHAR(1000)

								DECLARE curIndices CURSOR FOR
								SELECT		ISNULL(OBJECT_NAME(object_id), '') As TableNamei, ISNULL(name, '') As IndexNameI
									FROM	sys.indexes 
									WHERE	name = @INDEX_NAME_INDEX															 
											AND is_primary_key = 0
									ORDER BY 1, 2

								OPEN curIndices

								FETCH NEXT FROM curIndices INTO @TableNamei, @IndexNameI

									WHILE @@FETCH_STATUS = 0
										BEGIN
											IF (@IndexNameI <> '' AND @TableNamei <> '')
												BEGIN
													EXEC ('DROP INDEX ' + @IndexNameI + ' ON [dbo].[' + @TableNamei + ']')
												END

											FETCH NEXT FROM curIndices INTO @TableNamei, @IndexNameI

										END
															 
										CLOSE curIndices;
										DEALLOCATE curIndices;

										FETCH NEXT FROM curNOMEI INTO @INDEX_NAME_INDEX

							END
					 
							CLOSE curNOMEI;
							DEALLOCATE curNOMEI;

							--// trecho adicionado para remover index antigo
							IF EXISTS(SELECT ISNULL(OBJECT_NAME(object_id), '') As TableNamei, ISNULL(name, '') As IndexNameI FROM sys.indexes WHERE name = 'XFK2590_LJ_ETL_PROCESSO' AND is_primary_key = 0) 												 
							BEGIN
								EXEC ('DROP INDEX ' + 'XFK2590_LJ_ETL_PROCESSO' + ' ON [dbo].[' + 'LJ_ETL_PROCESSO' + ']')
							END
							
						END
						PRINT('EXCLUSÃO DOS ÍNDICES CONCLUIDO')


						--------------------------------------------------------------------------------------------------------------------------------------------------------
						----------------------------------- EXCLUSÃO DAS FOREN KEY----------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------

						PRINT('EXCLUSÃO DAS FOREN KEY ')				
						BEGIN
							DECLARE @NOMETABELAF VARCHAR(100)
							SET @NOMETABELAF =''
							
							DECLARE curNOMEF CURSOR FOR
							SELECT T.TABLE_NAME FROM @TABELA T
							
							OPEN curNOMEF

							FETCH NEXT FROM curNOMEF INTO @NOMETABELAF

							WHILE @@FETCH_STATUS = 0
							BEGIN

									
								DECLARE @TableNameFK VARCHAR(1000), @ForenkeyNameFK VARCHAR(1000)

								DECLARE curForenKey CURSOR FOR
								SELECT		ISNULL(OBJECT_NAME(parent_object_id), '') As TableNameFK, ISNULL(name, '') As ForenkeyNameFK
									FROM	sys.objects 
									WHERE	parent_object_id IN (OBJECT_ID(@NOMETABELAF)
											) 
											AND type = 'F'
									ORDER BY 1, 2

								OPEN curForenKey

								FETCH NEXT FROM curForenKey INTO @TableNameFK, @ForenkeyNameFK

								WHILE @@FETCH_STATUS = 0
								BEGIN

									IF (@TableNameFK <> '' AND @ForenkeyNameFK <> '')
										BEGIN
											EXEC ('ALTER TABLE [dbo].[' + @TableNameFK + '] DROP CONSTRAINT [' + @ForenkeyNameFK + ']')
										END

									FETCH NEXT FROM curForenKey INTO @TableNameFK, @ForenkeyNameFK

								END
		 
								CLOSE curForenKey;
								DEALLOCATE curForenKey;

								FETCH NEXT FROM curNOMEF INTO @NOMETABELAF

							END
		 
							CLOSE curNOMEF;
							DEALLOCATE curNOMEF;
						END	 
						PRINT('EXCLUSÃO DAS FOREN KEY CONCLUIDO ')


						----------------------------------------------------------------------------------------------------------------------------------------------------------
						----------------------------------- EXCLUSÃO DAS CONSTRAINT'S DOS DEFAULT's ------------------------------------------------------------------------------
						----------------------------------------------------------------------------------------------------------------------------------------------------------

						PRINT('EXCLUSÃO DAS CONSTRAINTS DOS DEFAULTs')
						BEGIN
							DECLARE @NOMETABELACD VARCHAR(100)
							SET @NOMETABELACD =''

							DECLARE curNOMECD CURSOR FOR
							SELECT T.TABLE_NAME FROM @TABELA T
													
							OPEN curNOMECD

							FETCH NEXT FROM curNOMECD INTO @NOMETABELACD

							WHILE @@FETCH_STATUS = 0
							BEGIN
								DECLARE @TableNameC VARCHAR(1000), @DefaultNameC VARCHAR(1000)

								DECLARE curDefaults CURSOR FOR
								SELECT		ISNULL(OBJECT_NAME(parent_object_id), '') As TableNameC, ISNULL(name, '') As DefaultNameC
									FROM	sys.objects 
									WHERE	parent_object_id IN (
												OBJECT_ID(@NOMETABELACD)
											) 
											AND type = 'D'
									ORDER BY 1, 2

								OPEN curDefaults

								FETCH NEXT FROM curDefaults INTO @TableNameC, @DefaultNameC

								WHILE @@FETCH_STATUS = 0
								BEGIN

									IF (@TableNameC <> '' AND @DefaultNameC <> '')
										BEGIN
											EXEC ('ALTER TABLE [dbo].[' + @TableNameC + '] DROP CONSTRAINT [' + @DefaultNameC + ']')
										END

									FETCH NEXT FROM curDefaults INTO @TableNameC, @DefaultNameC

								END
								 
								CLOSE curDefaults;
								DEALLOCATE curDefaults;

								FETCH NEXT FROM curNOMECD INTO @NOMETABELACD

							END -- end begin while do curNomeCD

							CLOSE curNOMECD;
							DEALLOCATE curNOMECD;
						END								 
						PRINT('EXCLUSÃO DAS CONSTRAINTS DOS DEFAULTs CONCLUIDO')



						--------------------------------------------------------------------------------------------------------------------------------------------------------
						----------------------------------- EXCLUSÃO DAS CONSTRAINTS PK GERAL ----------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------

							
						PRINT('EXCLUSÃO DAS CONSTRAINTS PK Geral ')
						BEGIN
							DECLARE	@TABLE_NAMEGeral VARCHAR(100)
							SET @TABLE_NAMEGeral = ''
							DECLARE curTABELAEXPKGERAL CURSOR FOR
							SELECT * FROM @TABELA

							OPEN curTABELAEXPKGERAL
	
							FETCH NEXT FROM curTABELAEXPKGERAL INTO	@TABLE_NAMEGeral
														
							WHILE @@FETCH_STATUS = 0	
							BEGIN
								IF EXISTS(SELECT * FROM sys.objects WHERE parent_object_id = OBJECT_ID(N'[dbo].['+ @TABLE_NAMEGeral+']') AND TYPE = 'PK') 
								BEGIN 
									DECLARE @SQLEXECEXCONSTRAINTGERAL VARCHAR(MAX)
									SET @SQLEXECEXCONSTRAINTGERAL = ''
									DECLARE @IndexNameGeral VARCHAR(1000)
		
									DECLARE curExcluir CURSOR FOR
									SELECT name as IndexNameGeral FROM sys.objects WHERE parent_object_id = OBJECT_ID(N'[dbo].['+ @TABLE_NAMEGeral+']') AND TYPE = 'PK'
		
									OPEN curExcluir

									FETCH NEXT FROM curExcluir INTO @IndexNameGeral
									WHILE @@FETCH_STATUS = 0
									BEGIN
									IF (@IndexNameGeral <> '')
										SET @SQLEXECEXCONSTRAINTGERAL = 'ALTER TABLE [dbo].[' +@TABLE_NAMEGeral+'] DROP CONSTRAINT ['+ @IndexNameGeral + ']'

										EXEC(@SQLEXECEXCONSTRAINTGERAL)
										FETCH NEXT FROM curExcluir INTO @IndexNameGeral

									END
		
									CLOSE curExcluir;
									DEALLOCATE curExcluir;

								END

								FETCH NEXT FROM curTABELAEXPKGERAL INTO	@TABLE_NAMEGeral 

							END  

							CLOSE curTABELAEXPKGERAL;
							DEALLOCATE curTABELAEXPKGERAL;

						END
						PRINT('EXCLUSÃO DAS CONSTRAINTS PK Geral CONCLUIDO ')


						--------------------------------------------------------------------------------------------------------------------------------------------------------
						----------------------------------- EXCLUSÃO DAS CONSTRAINTS PK ----------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------
					
						PRINT('EXCLUSÃO DAS CONSTRAINTS PK ')
						BEGIN

							DECLARE	@TABLE_NAMEConsPK VARCHAR(100)
							SET @TABLE_NAMEConsPK = '' 
							DECLARE curTABELAEXPK CURSOR FOR
							SELECT * FROM @TABELA

						
							OPEN curTABELAEXPK
	
							FETCH NEXT FROM curTABELAEXPK INTO	@TABLE_NAMEConsPK
																				
														
							WHILE @@FETCH_STATUS = 0	
							BEGIN
								IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PK_'+ @TABLE_NAMEConsPK + ']') AND parent_object_id = OBJECT_ID(N'[dbo].['+ @TABLE_NAMEConsPK+']') AND TYPE = 'PK') 
								BEGIN 
								DECLARE @SQLEXECEXCONSTRAINTPK VARCHAR(MAX)
								SET @SQLEXECEXCONSTRAINTPK = ''
		
								SET @SQLEXECEXCONSTRAINTPK = 'ALTER TABLE [dbo].[' +@TABLE_NAMEConsPK+'] DROP CONSTRAINT [PK_'+ @TABLE_NAMEConsPK+ ']'

								EXEC(@SQLEXECEXCONSTRAINTPK)
		
								END

								FETCH NEXT FROM curTABELAEXPK INTO	@TABLE_NAMEConsPK 

							END  

							CLOSE curTABELAEXPK;
							DEALLOCATE curTABELAEXPK;

						END
						PRINT('EXCLUSÃO DAS CONSTRAINTS PK CONCLUIDO ')

						--------------------------------------------------------------------------------------------------------------------------------------------------------
						----------------------------------- EXCLUSÃO DOS CAMPOS QU NÂO SERÂO MAIS USADOS ----------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------

							
						PRINT('EXCLUSÃO DOS CAMPOS')
						BEGIN
							DECLARE	@TABLE_NAME_EX_C VARCHAR(100)
							SET  @TABLE_NAME_EX_C= '' 
							DECLARE	@COLUMN_NAME_EX_C VARCHAR(100)
							SET @COLUMN_NAME_EX_C = '' 
							DECLARE curTABELACAMPOS CURSOR FOR
							SELECT		TABLE_NAME, COLUMN_NAME
								FROM	INFORMATION_SCHEMA.COLUMNS 
								WHERE	TABLE_NAME IN (SELECT DISTINCT TABLE_NAME FROM @TABELAS)
										AND COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM	@TABELAS)

							OPEN curTABELACAMPOS
	
							FETCH NEXT FROM curTABELACAMPOS INTO @TABLE_NAME_EX_C, @COLUMN_NAME_EX_C
																			
														
							WHILE @@FETCH_STATUS = 0	
							BEGIN
								DECLARE @SQLEXECEXCAMPOS VARCHAR(MAX)
								SET @SQLEXECEXCAMPOS = ''
		
								SET @SQLEXECEXCAMPOS = 'ALTER TABLE [dbo].[' +@TABLE_NAME_EX_C+'] DROP COLUMN ['+ @COLUMN_NAME_EX_C + ']'

								PRINT('EXCLUINDO CAMPO ' +@TABLE_NAME_EX_C+'.'+@COLUMN_NAME_EX_C)

								EXEC(@SQLEXECEXCAMPOS)
		
								FETCH NEXT FROM curTABELACAMPOS INTO @TABLE_NAME_EX_C, @COLUMN_NAME_EX_C

							END  

							CLOSE curTABELACAMPOS;
							DEALLOCATE curTABELACAMPOS;
						END
						PRINT('EXCLUSÃO DOS CAMPOS CONCLUÍDO')

						--------------------------------------------------------------------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------
						--------------------------------------------------------------------------------------------------------------------------------------------------------

						SET @EXCLUIU = 1
	
					END -- end begin do if excluiu

					PRINT('CRIAÇÃO DAS COLUNAS')

					DECLARE	@TABLE_NAMEAlter VARCHAR(100)			 
							,@COLUMN_NAMEAlter VARCHAR(100)
							,@ORDINAL_POSITIONAlter INT
							,@COLUMN_DEFAULTAlter VARCHAR(100)
							,@IS_NULLABLEAlter VARCHAR(10)
							,@DATA_TYPEAlter VARCHAR(100)
							,@CHARACTER_MAXIMUM_LENGTHAlter INT
							,@NUMERIC_PRECISIONAlter INT
							,@NUMERIC_SCALEAlter INT
							,@PRIMARYKEYAlter INT
							,@IDENTAlter VARCHAR(100)

					DECLARE curTablesColALTER CURSOR FOR
					SELECT		*
						FROM	@TABELAS T WHERE T.TABLE_NAME = @TABLE_NAME

					DECLARE @TOTALCOLUNAALTER INT
					SET @TOTALCOLUNAALTER = 0
					SET @TOTALCOLUNAALTER = (SELECT COUNT(TAB.COLUMN_NAME) FROM @TABELAS TAB WHERE TAB.TABLE_NAME = @TABLE_NAME)
					DECLARE @CONTADORALTER  AS INT
					SET @CONTADORALTER = 0

					OPEN curTablesColALTER

					FETCH NEXT FROM curTablesColALTER INTO	@TABLE_NAMEAlter
													, @COLUMN_NAMEAlter
													, @ORDINAL_POSITIONAlter
													, @COLUMN_DEFAULTAlter
													, @IS_NULLABLEAlter
													, @DATA_TYPEAlter
													, @CHARACTER_MAXIMUM_LENGTHAlter
													, @NUMERIC_PRECISIONAlter
													, @NUMERIC_SCALEAlter
													, @PRIMARYKEYAlter
													, @IDENTAlter

					WHILE @@FETCH_STATUS = 0
					BEGIN

						SET @CONTADORALTER = @CONTADORALTER + 1

							--VERIFICO SE EXISTE A COLUNA QUE ESTA NA TABELA DO MODELO
						IF EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAMEAlter AND COLUMN_NAME = @COLUMN_NAMEAlter)
							BEGIN
								
								DECLARE @SQL VARCHAR(MAX)
								SET @SQL =''
								
								-- VERIFICO SE A COLUNA NÃO TEM TODOS OS PARAMETROS IGUAIS AO MODELO
								IF NOT EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAMEAlter AND COLUMN_NAME = @COLUMN_NAMEAlter AND UPPER(DATA_TYPE) = UPPER(@DATA_TYPEAlter) AND UPPER(IS_NULLABLE) = UPPER(@IS_NULLABLEAlter) AND ISNULL(CHARACTER_MAXIMUM_LENGTH,0) = (SELECT CASE UPPER(@DATA_TYPEAlter)  WHEN 'TEXT' THEN 2147483647 ELSE ISNULL(CHARACTER_MAXIMUM_LENGTH,0) END) AND ISNULL(NUMERIC_PRECISION,0) = @NUMERIC_PRECISIONAlter AND ISNULL(NUMERIC_SCALE,0) = @NUMERIC_SCALEAlter)
									BEGIN
										SET @SQL = ' ALTER TABLE '+ @TABLE_NAMEAlter + ' ALTER COLUMN ' + @COLUMN_NAMEAlter 
										IF(@CHARACTER_MAXIMUM_LENGTHAlter = 0 AND UPPER(@DATA_TYPEAlter) != 'DECIMAL' AND UPPER(@DATA_TYPEAlter) != 'NUMERIC')
											BEGIN
												SET @SQL = @SQL + ' ' + @DATA_TYPEAlter
											END
										ELSE
											BEGIN
												IF(UPPER(@DATA_TYPEAlter) = 'DECIMAL' OR UPPER(@DATA_TYPEAlter) = 'NUMERIC')
													BEGIN
														SET @SQL = @SQL + ' ' + @DATA_TYPEAlter + '(' + CAST(@NUMERIC_PRECISIONAlter AS VARCHAR) + ',' + CAST(@NUMERIC_SCALEAlter AS VARCHAR) + ')'
													END
												ELSE
													BEGIN
														SET @SQL = @SQL + ' ' + @DATA_TYPEAlter + '(' + CAST(@CHARACTER_MAXIMUM_LENGTHAlter AS VARCHAR) + ')'
													END
											END
											--IF(@IDENTAlter != '') --##comentado pois o sql nao consegue setar para identity no alter, entao ele mantem o identity da coluna 
											--	BEGIN
											--		SET @SQL += ' ' + @IDENTAlter
											--	END							
										IF(UPPER(@IS_NULLABLEAlter) = 'YES')
											BEGIN
												SET @SQL = @SQL + ' ' + 'NULL'
											END
											ELSE
												BEGIN
													SET @SQL = @SQL + ' ' + 'NOT NULL'
												END
										EXEC(@SQL)
								END --FIM DO IF
										
							END	--FIM DO IF
						ELSE
							BEGIN
							
							--IF NOT EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TABLE_NAME AND COLUMN_NAME = @COLUMN_NAME AND UPPER(DATA_TYPE) = UPPER(@DATA_TYPE) OR ISNULL(CHARACTER_MAXIMUM_LENGTH,0) = (SELECT CASE UPPER(@DATA_TYPE)  WHEN 'TEXT' THEN 2147483647 ELSE ISNULL(CHARACTER_MAXIMUM_LENGTH,0) END) OR NUMERIC_PRECISION = @NUMERIC_PRECISION OR NUMERIC_SCALE = @NUMERIC_SCALE)
								BEGIN
									SET @SQL = 'ALTER TABLE ' + @TABLE_NAMEAlter + ' ADD ' + @COLUMN_NAMEAlter + ' '
									IF(@CHARACTER_MAXIMUM_LENGTHAlter = 0 AND UPPER(@DATA_TYPEAlter) != 'DECIMAL' AND UPPER(@DATA_TYPEAlter) != 'NUMERIC')
										BEGIN
											SET @SQL = @SQL + ' ' + @DATA_TYPEAlter
										END
									ELSE
										IF(UPPER(@DATA_TYPEAlter) = 'DECIMAL' OR UPPER(@DATA_TYPEAlter) = 'NUMERIC')
											BEGIN
												SET @SQL = @SQL + ' ' + @DATA_TYPEAlter + '(' + CAST(@NUMERIC_PRECISIONAlter AS VARCHAR) + ',' + CAST(@NUMERIC_SCALEAlter AS VARCHAR) + ')'
											END
										ELSE
											BEGIN
												SET @SQL = @SQL +  ' ' + @DATA_TYPEAlter + '(' + CAST(@CHARACTER_MAXIMUM_LENGTHAlter AS VARCHAR) + ')'
											END
										IF(@COLUMN_DEFAULTAlter != '')
										BEGIN
											SET @SQL = @SQL + ' ' + @COLUMN_DEFAULTAlter
										END
										IF(UPPER(@IS_NULLABLEAlter) = 'YES')
											BEGIN
												SET @SQL = @SQL + ' ' + 'NULL'
											END
										ELSE
											BEGIN
												SET @SQL = @SQL + ' ' + 'NOT NULL'
											END
										EXEC(@SQL)
								END
							END -- FIM DO BEGIN DO ELSE

						FETCH NEXT FROM curTablesColALTER INTO	@TABLE_NAMEAlter
						, @COLUMN_NAMEAlter
						, @ORDINAL_POSITIONAlter
						, @COLUMN_DEFAULTAlter
						, @IS_NULLABLEAlter
						, @DATA_TYPEAlter
						, @CHARACTER_MAXIMUM_LENGTHAlter
						, @NUMERIC_PRECISIONAlter
						, @NUMERIC_SCALEAlter
						, @PRIMARYKEYAlter
						, @IDENTAlter
								
								
					END -- END BEGIN WHILE curTablesColALTER
							
					CLOSE curTablesColALTER;
					DEALLOCATE curTablesColALTER;
		 
		 
					PRINT('CRIAÇÃO DAS COLUNAS CONCLUIDO')

		END --end begin do else
 
		FETCH NEXT FROM curTables INTO	@TABLE_NAME
		
	END -- end begin while curTables
		
	CLOSE curTables;
	DEALLOCATE curTables;
					

	----------------------------------------------------------------------------------------------------------------------------------------------------------
	---- CRIAÇÃO DAS CONSTRAINT DEFAULT
	----------------------------------------------------------------------------------------------------------------------------------------------------------
	IF(@EXCLUIU = 1)
	BEGIN
	
		PRINT('CRIAÇÃO DAS CONSTRAINT DEFAULT')
		BEGIN 
			DECLARE		
				@TABLE_NAMEDF VARCHAR(100)
				, @COLUMN_NAMEDF VARCHAR(100)
				, @ORDINAL_POSITIONDF INT
				, @COLUMN_DEFAULTDF VARCHAR(100)
				, @IS_NULLABLEDF VARCHAR(10)
				, @DATA_TYPEDF VARCHAR(100)
				, @CHARACTER_MAXIMUM_LENGTHDF INT
				, @NUMERIC_PRECISIONDF INT
				, @NUMERIC_SCALEDF INT
				,@PRIMARYKEYDF INT
				,@IDENTDF VARCHAR(100)

			DECLARE curCONSTRAINTDF CURSOR FOR
			SELECT * FROM @TABELAS TAB WHERE TAB.COLUMN_DEFAULT != ''
						
			OPEN curCONSTRAINTDF

			FETCH NEXT FROM curCONSTRAINTDF INTO	@TABLE_NAMEDF
			, @COLUMN_NAMEDF
			, @ORDINAL_POSITIONDF
			, @COLUMN_DEFAULTDF
			, @IS_NULLABLEDF
			, @DATA_TYPEDF
			, @CHARACTER_MAXIMUM_LENGTHDF
			, @NUMERIC_PRECISIONDF
			, @NUMERIC_SCALEDF
			,@PRIMARYKEYDF
			,@IDENTDF
														
			WHILE @@FETCH_STATUS = 0
			BEGIN

				DECLARE @SQLEXECCONSTRAINTDF VARCHAR(MAX)
				SET @SQLEXECCONSTRAINTDF = ''
		
				IF NOT EXISTS(SELECT * FROM sys.default_constraints d INNER JOIN sys.columns c ON d.parent_column_id = c.column_id WHERE d.parent_object_id = OBJECT_ID(@TABLE_NAMEDF) AND c.name = @COLUMN_NAMEDF)
				BEGIN
					IF NOT EXISTS(SELECT * FROM	sys.objects	WHERE parent_object_id IN (OBJECT_ID(@TABLE_NAMEDF)) and upper(name)  like '%'+Upper(@COLUMN_NAMEDF)+'%' or UPPER(name) = UPPER('[DF_' + @TABLE_NAMEDF + '_' + @COLUMN_NAMEDF +']') AND type = 'D')
					BEGIN
						SET @SQLEXECCONSTRAINTDF =  @SQLEXECCONSTRAINTDF + 'ALTER TABLE [dbo].[' + @TABLE_NAMEDF + ']  WITH CHECK ADD  CONSTRAINT [DF_' + @TABLE_NAMEDF + '_' + @COLUMN_NAMEDF + '] ' + @COLUMN_DEFAULTDF + ' FOR [' + @COLUMN_NAMEDF + ']'
						EXEC(@SQLEXECCONSTRAINTDF)
					END
				END
								
				FETCH NEXT FROM curCONSTRAINTDF INTO	@TABLE_NAMEDF
				, @COLUMN_NAMEDF
				, @ORDINAL_POSITIONDF
				, @COLUMN_DEFAULTDF
				, @IS_NULLABLEDF
				, @DATA_TYPEDF
				, @CHARACTER_MAXIMUM_LENGTHDF
				, @NUMERIC_PRECISIONDF
				, @NUMERIC_SCALEDF
				,@PRIMARYKEYDF
				,@IDENTDF


			END

			CLOSE curCONSTRAINTDF;
			DEALLOCATE curCONSTRAINTDF;

		END
		PRINT('CRIAÇÃO DAS CONSTRAINT CONCLUIDO DEFAULT')

	END

	--------------------------------------------------------------------------------------------------------------------------------------------------------
	-- CRIAÇÃO DAS CONSTRAINT PK
	--------------------------------------------------------------------------------------------------------------------------------------------------------

	IF(@EXCLUIU = 1)
	BEGIN

		PRINT('CRIAÇÃO DAS CONSTRAINT PK')

		DECLARE	@TABLE_NAMEPK VARCHAR(100)
		SET @TABLE_NAMEPK = ''
			DECLARE curTABELAPK CURSOR FOR
								SELECT * FROM @TABELA
						
		OPEN curTABELAPK
	
		FETCH NEXT FROM curTABELAPK INTO	@TABLE_NAMEPK
														
		WHILE @@FETCH_STATUS = 0
		BEGIN

			IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PK_'+ @TABLE_NAMEPK + ']')) --AND parent_object_id = OBJECT_ID(N'[dbo].['+ @TABLE_NAMEPK+']'))
			BEGIN --EXLUIR		
				DECLARE @PRIMARYALTER VARCHAR(MAX)
				SET @PRIMARYALTER = ''
				DECLARE @SQLEXECCONSTRAINTPK VARCHAR(MAX)
				SET @SQLEXECCONSTRAINTPK = ''
				IF((SELECT COUNT(P.COLUMN_NAME) FROM @TABELAS P WHERE P.TABLE_NAME = @TABLE_NAMEPK AND P.PRIMARYKEY = 1) >= 2)
					BEGIN
							SELECT @PRIMARYALTER =  @PRIMARYALTER +  COALESCE(P.COLUMN_NAME, '') + ',' FROM @TABELAS P WHERE P.TABLE_NAME = @TABLE_NAMEPK AND P.PRIMARYKEY = 1
																		SET  @PRIMARYALTER = SUBSTRING(@PRIMARYALTER,1, LEN(@PRIMARYALTER)-1)
					END
				ELSE
					BEGIN
						SET @PRIMARYALTER = (SELECT P.COLUMN_NAME FROM @TABELAS P WHERE P.TABLE_NAME = @TABLE_NAMEPK AND P.PRIMARYKEY = 1)
					END


				SET @SQLEXECCONSTRAINTPK = @SQLEXECCONSTRAINTPK + 'ALTER TABLE [dbo].[' + @TABLE_NAMEPK + ']  WITH CHECK ADD CONSTRAINT [PK_'+ @TABLE_NAMEPK +'] PRIMARY KEY CLUSTERED 
				(
					'+ @PRIMARYALTER + ' ASC
				)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]' 
				--SET @SQL = SUBSTRING(@SQLEXEC,1, LEN(@SQLEXEC)-1)

				EXEC(@SQLEXECCONSTRAINTPK)
		
			END 
			FETCH NEXT FROM curTABELAPK INTO	@TABLE_NAMEPK

		END -- FIM DO BEGIN DO WHILE

		CLOSE curTABELAPK;
		DEALLOCATE curTABELAPK;
	
		PRINT('CRIAÇÃO DAS CONSTRAINT CONCLUIDO PK')

	END

	--------------------------------------------------------------------------------------------------------------------------------------------------------
	-- CRIAÇÃO DAS FOREIGN KEY
	--------------------------------------------------------------------------------------------------------------------------------------------------------

	PRINT('CRIAÇÃO DAS FOREIGN KEY')
	BEGIN 


		DECLARE	@TABLE_NAMEFK	VARCHAR(100)
				,@CONSTRAINT_NAME VARCHAR(100)						
				, @FOREIGNKEY_NAME VARCHAR(100)
				,@REFERENCES_NAME VARCHAR(100)

		DECLARE curFOREIGNKEY CURSOR FOR
		SELECT		*
			FROM	@FOREIGNKEY 
						
		OPEN curFOREIGNKEY

		FETCH NEXT FROM curFOREIGNKEY INTO	@TABLE_NAMEFK
										, @CONSTRAINT_NAME
										, @FOREIGNKEY_NAME
										, @REFERENCES_NAME
														
														
		WHILE @@FETCH_STATUS = 0
		BEGIN
			DECLARE @SQLEXECFORN VARCHAR(MAX)
			SET @SQLEXECFORN = ''
							
			SET @SQLEXECFORN = @SQLEXECFORN + 'ALTER TABLE [dbo].[' + @TABLE_NAMEFK + ']  WITH CHECK ADD  CONSTRAINT [' + @CONSTRAINT_NAME + '] FOREIGN KEY([' + @FOREIGNKEY_NAME + ']) REFERENCES [dbo].[' + @REFERENCES_NAME
				+ '] ([' + @FOREIGNKEY_NAME +'])ON DELETE CASCADE'

			FETCH NEXT FROM curFOREIGNKEY INTO	@TABLE_NAMEFK
											, @CONSTRAINT_NAME
											, @FOREIGNKEY_NAME
											, @REFERENCES_NAME
															


			EXEC(@SQLEXECFORN)

		END

		CLOSE curFOREIGNKEY;
		DEALLOCATE curFOREIGNKEY;

	END
	PRINT('CRIAÇÃO DAS FOREIGN KEY CONCLUIDO')

	--------------------------------------------------------------------------------------------------------------------------------------------------------
	-- CRIAÇÃO DOS ÍNDICES
	--------------------------------------------------------------------------------------------------------------------------------------------------------

	PRINT('CRIAÇÃO DOS ÍNDICES')
	BEGIN

		DECLARE 			
				@INDEX_NAME VARCHAR(100)
				,@TABLE_NAMEIX VARCHAR(100)						
				,@COLUMN_NAMEIX VARCHAR(100)
				,@INCLUDE_NAME VARCHAR(100)

		DECLARE curINDEXTABLE CURSOR FOR
		SELECT		*
			FROM	@INDEX_TABLE 

						
		OPEN curINDEXTABLE

		FETCH NEXT FROM curINDEXTABLE INTO	@INDEX_NAME
										,@TABLE_NAMEIX
										, @COLUMN_NAMEIX
										, @INCLUDE_NAME
														
														
		WHILE @@FETCH_STATUS = 0
			BEGIN

				DECLARE @SQLEXECINDEX VARCHAR(MAX)
				SET @SQLEXECINDEX = ''
								
				SET @SQLEXECINDEX = @SQLEXECINDEX + 'CREATE NONCLUSTERED INDEX [' + @INDEX_NAME + '] ON [dbo].[' + @TABLE_NAMEIX + '](' + @COLUMN_NAMEIX + ' ASC )'
					IF(@INCLUDE_NAME != '')
						BEGIN
							SET @SQLEXECINDEX = @SQLEXECINDEX + 'INCLUDE (' + @INCLUDE_NAME + ')'
						END
				SET @SQLEXECINDEX = @SQLEXECINDEX + ' WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'

				FETCH NEXT FROM curINDEXTABLE INTO	@INDEX_NAME
												, @TABLE_NAMEIX
												, @COLUMN_NAMEIX
												, @INCLUDE_NAME

				EXEC(@SQLEXECINDEX)

			END

			CLOSE curINDEXTABLE;
			DEALLOCATE curINDEXTABLE;

	END
	PRINT('CRIAÇÃO DOS ÍNDICES CONCLUIDO')		

	PRINT 'COMMIT';
	COMMIT;

	PRINT 'ALTERAÇÕES EXTRAS TABELA LJ_ETL_REPOSITORIO'

	IF EXISTS(SELECT 1 FROM   INFORMATION_SCHEMA.TABLES WHERE  TABLE_NAME = 'LJ_ETL_REPOSITORIO')
	BEGIN
	
		EXEC('ALTER TABLE dbo.LJ_ETL_REPOSITORIO SET (LOCK_ESCALATION = TABLE)')

		IF EXISTS(SELECT TOP 1 1 FROM SYS.INDEXES WHERE NAME = 'IX_TABELA_DATA_HORA_CRIACAO' )
			BEGIN
				EXEC('DROP INDEX [IX_TABELA_DATA_HORA_CRIACAO] ON [dbo].[LJ_ETL_REPOSITORIO]')
			END

		EXEC('CREATE NONCLUSTERED INDEX [IX_TABELA_DATA_HORA_CRIACAO] ON [dbo].[LJ_ETL_REPOSITORIO] ([TABELA], [DATA_HORA_CRIACAO], [VERSAO]) INCLUDE ([FILTRO], [ID_ETL_TIPO]) WITH (FILLFACTOR=90)')

		IF NOT EXISTS (SELECT 1 FROM SYS.sysobjects WHERE NAME = 'DF_LJ_ETL_REPOSITORIO_USUARIO' AND TYPE = 'D' AND  OBJECT_NAME(parent_obj) = 'LJ_ETL_REPOSITORIO')
			BEGIN
				EXEC('ALTER TABLE dbo.LJ_ETL_REPOSITORIO ADD CONSTRAINT DF_LJ_ETL_REPOSITORIO_USUARIO DEFAULT SYSTEM_USER FOR USUARIO')
			END

	END

	PRINT 'ALTERAÇÕES EXTRAS TABELA LJ_ETL_ARQUIVO'

	IF EXISTS(SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LJ_ETL_ARQUIVO' )
	BEGIN
	
		IF EXISTS(SELECT TOP 1 1 FROM SYS.INDEXES WHERE NAME = 'IX_LJ_ETL_ARQUIVO' )
		BEGIN
			EXEC('DROP INDEX [IX_LJ_ETL_ARQUIVO] ON [dbo].[LJ_ETL_ARQUIVO]')
		END

		EXEC('CREATE NONCLUSTERED INDEX [IX_LJ_ETL_ARQUIVO] ON [dbo].[LJ_ETL_ARQUIVO] 
		(
			   [CODIGO_FILIAL],
			   [VERSAO],
			   [PRIORIDADE]
		) INCLUDE ( [DATA_HORA_PROXIMO_PROCESSO], [ID_ETL_TIPO], [PROCESSADO], XML_ARQUIVO) WITH (FILLFACTOR = 90)')

	END

	--####################### comentado pois não vamos utilizar agora

	--IF NOT EXISTS (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LJ_ETL_ESTATISTICA_ARQUIVO' )
	--BEGIN

		
	--	EXEC('
	--		ALTER TABLE [dbo].[LJ_ETL_ESTATISTICA_ARQUIVO]  WITH CHECK ADD  CONSTRAINT [FK_LJ_ETL_ESTATISTICA_ARQUIVO_LJ_LX_ETL_TIPO] FOREIGN KEY([ID_ETL_TIPO])
	--		REFERENCES [dbo].[LJ_LX_ETL_TIPO] ([ID_ETL_TIPO])
	--		ON UPDATE CASCADE
	--		--ON DELETE CASCADE
	--		NOT FOR REPLICATION 
	--	')

	--	EXEC('
	--		ALTER TABLE [dbo].[LJ_ETL_ESTATISTICA_ARQUIVO] CHECK CONSTRAINT [FK_LJ_ETL_ESTATISTICA_ARQUIVO_LJ_LX_ETL_TIPO]
	--	')

	--END

	----#########################

	----#######################

	--IF NOT EXISTS (SELECT TOP 1 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'LJ_ETL_ESTATISTICA_ARQUIVO' )
	--BEGIN

		
	--	EXEC('
			
	--		ALTER TABLE [dbo].[LJ_ETL_ESTATISTICA_ARQUIVO]  WITH CHECK ADD  CONSTRAINT [FK_LJ_ETL_ESTATISTICA_ARQUIVO_LOJAS_VAREJO] FOREIGN KEY([CODIGO_FILIAL])
	--		REFERENCES [dbo].[LOJAS_VAREJO] ([CODIGO_FILIAL])
	--		ON UPDATE CASCADE
	--		--ON DELETE CASCADE
	--		NOT FOR REPLICATION 
	--	')

	--	EXEC('
	--		ALTER TABLE [dbo].[LJ_ETL_ESTATISTICA_ARQUIVO] CHECK CONSTRAINT [FK_LJ_ETL_ESTATISTICA_ARQUIVO_LOJAS_VAREJO]
	--	')

	--END

	----#########################


END TRY
BEGIN CATCH

	PRINT 'ROLLBACK';
	ROLLBACK;


	IF CURSOR_STATUS('global','curTables') >= -1
		BEGIN
			CLOSE curTables;
			DEALLOCATE curTables;
		END


	IF CURSOR_STATUS('global','curTablesCol') >= -1
		BEGIN
			CLOSE curTablesCol;
			DEALLOCATE curTablesCol;
		END

	IF CURSOR_STATUS('global','curNOMEI') >= -1
		BEGIN
			CLOSE curNOMEI;
			DEALLOCATE curNOMEI;
		END

	IF CURSOR_STATUS('global','curIndices') >= -1
		BEGIN
			CLOSE curIndices;
			DEALLOCATE curIndices;
		END

	IF CURSOR_STATUS('global','curNOMEF') >= -1
		BEGIN
			CLOSE curNOMEF;
			DEALLOCATE curNOMEF;
		END

	IF CURSOR_STATUS('global','curForenKey') >= -1
		BEGIN
			CLOSE curForenKey;
			DEALLOCATE curForenKey;
		END

	IF CURSOR_STATUS('global','curNOMECD') >= -1
		BEGIN
			CLOSE curNOMECD;
			DEALLOCATE curNOMECD;
		END

	IF CURSOR_STATUS('global','curDefaults') >= -1
		BEGIN
			CLOSE curDefaults;
			DEALLOCATE curDefaults;
		END

	IF CURSOR_STATUS('global','curTABELAEXPKGERAL') >= -1
		BEGIN
			CLOSE curTABELAEXPKGERAL;
			DEALLOCATE curTABELAEXPKGERAL;
		END

	IF CURSOR_STATUS('global','curExcluir') >= -1
		BEGIN
			CLOSE curExcluir;
			DEALLOCATE curExcluir;
		END

	IF CURSOR_STATUS('global','curTABELAEXPK') >= -1
		BEGIN
			CLOSE curTABELAEXPK;
			DEALLOCATE curTABELAEXPK;
		END

	IF CURSOR_STATUS('global','curTABELACAMPOS') >= -1
		BEGIN
			CLOSE curTABELACAMPOS;
			DEALLOCATE curTABELACAMPOS;
		END

	IF CURSOR_STATUS('global','curTablesColALTER') >= -1
		BEGIN
			CLOSE curTablesColALTER;
			DEALLOCATE curTablesColALTER;
		END

	IF CURSOR_STATUS('global','curCONSTRAINTDF') >= -1
		BEGIN
			CLOSE curCONSTRAINTDF;
			DEALLOCATE curCONSTRAINTDF;
		END

	IF CURSOR_STATUS('global','curTABELAPK') >= -1
		BEGIN
			CLOSE curTABELAPK;
			DEALLOCATE curTABELAPK;
		END

	IF CURSOR_STATUS('global','curFOREIGNKEY') >= -1
		BEGIN
			CLOSE curFOREIGNKEY;
			DEALLOCATE curFOREIGNKEY;
		END

	IF CURSOR_STATUS('global','curINDEXTABLE') >= -1
		BEGIN
			CLOSE curINDEXTABLE;
			DEALLOCATE curINDEXTABLE;
		END


	SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage

END CATCH


