/*Drop tmp table BASE_DONNEES_DOCUMENTAIRE*/
if exists (select * from sysobjects where name='BASE_DONNEES_DOCUMENTAIRE' and xtype='U') drop table BASE_DONNEES_DOCUMENTAIRE

<SEPARATEUR>
select CODE_ART as BDD_CODE_ARTICLE,CODE_FOU as BDD_CODE_FOURNISSEUR,NO_PAGE as BDD_NUMERO_PAGE,TITRE as BDD_TITRE,m.CONTMEMO as BDD_TEXTE,LANGUE as BDD_CODE_LANGUE,NUMORDRE as BDD_NUMERO_ORDRE
into BASE_DONNEES_DOCUMENTAIRE
from <PREFIXE_HA>BDD b with (nolock)
left outer join <PREFIXE_HA>MEMOS m on m.NODEMEMO=b.NM_TEXTE 

<SEPARATEUR>
/*Adding default values if missing*/
if exists(select left(name, charindex('_',name)-1) from tempdb..sysobjects where charindex('_',name) > 0 and xtype = 'u' and not object_id('tempdb..'+name) is null and left(name, charindex('_',name)-1)='#tmp') drop table #tmp

select distinct 'ALTER TABLE '+TABLE_NAME+' ADD CONSTRAINT DF_'+TABLE_NAME+'_' +COLUMN_NAME 
+case when DATA_TYPE like '%char' then  ' DEFAULT ''''  '  
when DATA_TYPE = 'float' or DATA_TYPE ='decimal' or DATA_TYPE = 'int' then  ' DEFAULT 0  '  
when DATA_TYPE = 'datetime' then  ' DEFAULT ''19000101''  ' 
when DATA_TYPE = 'binary' then  ' DEFAULT 0x46  '  
else  ' DEFAULT ''''  '  end
+' FOR '+COLUMN_NAME as req 
into #tmp    
from INFORMATION_SCHEMA.COLUMNS i where DATA_TYPE in ('varchar','char','float','datetime','decimal','int','binary') and COLUMN_DEFAULT is null and i.TABLE_NAME in (SELECT  name FROM  sys.tables)   and TABLE_NAME+COLUMN_NAME not in (select TABLE_NAME+COLUMN_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE) 


DECLARE @Sql NVARCHAR(MAX);
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR 
    (SELECT req      FROM #tmp  )

OPEN Cur
FETCH NEXT FROM Cur INTO @Sql

WHILE (@@FETCH_STATUS = 0)
BEGIN
    --Exec sp_executesql @Sql
	print @Sql
    EXEC ( @Sql )

    FETCH NEXT FROM Cur INTO @Sql 
END
CLOSE Cur
DEALLOCATE Cur;      


   


