
select table_name nomtab,column_name nomcam,data_type tipo,rtrim(COLUMN_NAME)+' '+RTRIM(DATA_TYPE)+
case when DATA_TYPE IN ('char','varchar') then '('+rtrim(cast(CHARACTER_MAXIMUM_LENGTH as CHAR))+')'
when DATA_TYPE ='numeric' then '('+RTRIM(cast(NUMERIC_PRECISION as CHAR))+','+rtrim(cast(NUMERIC_SCALE as CHAR))+')'
else '' end campo into #tablas_siav
from "sanluis".INFORMATION_SCHEMA.COLUMNS where 
left(TABLE_NAME,4)='come' and CHARINDEX('_log',table_name)=0
order by table_name,ORDINAL_POSITION

select * from #tablas_siav
drop table #tablas_siav
select nomtab,nomcam,tipo,REPLACE(campo,',',';') from #tablas_siav
--- nos traemos la estructura actual 
CREATE TABLE #TABLAS_ant (NOMTAB CHAR(10),NOMCAM CHAR(50),TIPO CHAR(20),CAMPO CHAR(50))
BULK
INSERT #TABLAS_ant from 'F:\backup\cedis\estructuraZamoraActual.csv'
WITH
( FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')
--DROP TABLE #TABLAS_ant
--DROP TABLE #tablas_act

--- prepara tabla campos actuales para trabajarla 
select replace(NOMTAB,'´++','') nomtab,NOMCAM,TIPO,REPLACE(CAMPO,';',',') as campo 
  into #tablas_act from #TABLAS_ant

--- crea una tabla unica fusionando las anteriores con las actuales
---  para poder detectar los campos faltantes y los cambios nuevos
select act.nomtab nomtab,act.nomcam nomcam,act.tipo tipo,act.campo campo,
ant.nomtab tabant,ant.nomcam camant,ant.tipo tipoant,ant.campo campoant 
into #tablas from #TABLAS_act act 
left join #tablas_siav ant on act.nomtab=ant.nomtab and 
act.nomcam=ant.nomcam 

--- genera quiery para cambiar collation y tamaņo de campos alfa-num
select 'alter TABLE dbo.'+nomtab+' ALTER COLUMN '+campo+
' COLLATE Modern_Spanish_CI_AS ' as dato from #tablas
where (tipo='char' or tipo='varchar' or tipo='text')
order by nomtab 

--- genera query para cambiar los campos numericos que sean diferentes
select 'alter TABLE dbo.'+nomtab+' ALTER COLUMN '+campo
 as dato from #tablas
where (tipo<>'char' and tipo<>'varchar' and tipo<>'text') and 
campo<>campoant AND campoant IS NOT NULL

select * from #tablas where campo<>campoant
select * from #tablas where tipo<>tipoant
order by nomtab 

--- query para crear las nuevas tablas 
select NOMTAB AS TABNUE INTO #TABLASNUEVAS  from #tablas where nomcam ='key_pri' and camant is null
order by nomtab
DECLARE @NOMTAB CHAR(20),
		@NOMCAM CHAR(30),
		@CAMPO CHAR(59),
		@LEIDA CHAR(20),
		@DATOS VARCHAR(MAX)
DECLARE TABCREAR CURSOR FOR 
SELECT nomtab,nomcam,campo  FROM #TABLAS JOIN #TABLASNUEVAS ON NOMTAB=TABNUE
ORDER BY NOMTAB
OPEN TABCREAR
FETCH NEXT FROM TABCREAR INTO @NOMTAB,@NOMCAM,@CAMPO
SET @LEIDA=@NOMTAB
SET @DATOS=' CREATE TABLE '+RTRIM(@LEIDA)+' ( '
WHILE (@@FETCH_STATUS=0)
	BEGIN
	  IF @LEIDA<>@NOMTAB
	     BEGIN	 
		   SET @DATOS=@DATOS+') '+CHAR(13)+
		   ' CREATE UNIQUE CLUSTERED INDEX [KEY_PRI] ON [dbo].['+rtrim(@leida)+']'+
           ' ([KEY_PRI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE '+
           '= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING '+
           '= OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] '+
           CHAR(13)+
		   +' CREATE TABLE '+RTRIM(@NOMTAB)+' ( '
		   SET @LEIDA=@NOMTAB
		 END
	  IF UPPER(RTRIM(@NOMCAM))='KEY_PRI'
	       SET @DATOS=@DATOS+'KEY_PRI INT IDENTITY ,'
	  IF UPPER(RTRIM(@NOMCAM))='TIE_UAC' 
	       SET @DATOS=@DATOS+'TIE_UAC DATETIME DEFAULT getdate(),' 
	  IF UPPER(RTRIM(@NOMCAM))<>'KEY_PRI' AND UPPER(RTRIM(@NOMCAM))<>'TIE_UAC'
	  	   SET @DATOS=@DATOS+RTRIM(@CAMPO)+' ,'

	  FETCH NEXT FROM TABCREAR INTO @NOMTAB,@NOMCAM,@CAMPO
	END
sET @DATOS=@DATOS+') '+CHAR(13)+
		   ' CREATE UNIQUE CLUSTERED INDEX [KEY_PRI] ON [dbo].['+rtrim(@leida)+']'+
           ' ([KEY_PRI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE '+
           '= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING '+
           '= OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] '+
           CHAR(13)	   
set @DATOS=REPLACE(@datos,',)',')')

PRINT @DATOS

DEALLOCATE TABCREAR
---
--- genera query para aumentar campos nuevos

select 'alter TABLE dbo.'+nomtab+' ADD '+campo+case 
when tipo='char' or tipo='varchar' or tipo='text' then 
' COLLATE Modern_Spanish_CI_AS ' else ' ' end  as dato from #tablas
LEFT JOIN #TABLASNUEVAS ON NOMTAB=TABNUE
where camant is null AND TABNUE IS NULL
order by nomtab 


--- genera query para eliminar campos que ya no se utilizan

select 'alter TABLE dbo.'+nomtab+' drop '+camant+' ' as dato from #tablas
where campo is null
order by nomtab 

select * from #tablas where campo is null


