CREATE TABLE [SD].[Size] (
	[CeatNd] [varchar] (50)  NULL ,
	[Tab] [varchar] (50)   NULL ,
	[Size] [int] NULL 
) ON [PRIMARY]
GO
CREATE TABLE [SD].[Prim] (
	[PrimNd] [varchar] (50)   NULL ,
	[CreatNd] [varchar] (50)   NULL ,
	[Tab] [varchar] (50)   NULL 
) ON [PRIMARY]
GO

CREATE TABLE [SD].[RP] (
	[SgmNd] [varchar] (50)   NULL ,
	[CreatNd] [varchar] (50)   NULL ,
	[Tab] [varchar] (50)   NULL 
) ON [PRIMARY]
GO

CREATE TABLE [SD].[NDB] (
	[Node] [varchar] (50)   NULL ,
	[NDBType] [varchar] (50)   NULL 
) ON [PRIMARY]
go

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE add_key  @table varchar(50), @key varchar(50) AS
declare @alter varchar(5000)
set @alter=
'
alter table sd.'+@table+ '  add CONSTRAINT pk_'+@table+' PRIMARY KEY   ( ' +@key+ ' )    ON [PRIMARY]  '

execute (@alter)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO




CREATE PROCEDURE linkedtable @originalsite varchar (50), @originaltable varchar(50), @targetable varchar(50), @site varchar(50), @key varchar(50), @moy int, @moydata sql_variant, @all_keys varchar(50)/*, @lastdata sql_variant */AS

declare @query varchar(5000), @trigger varchar(50), @proc varchar(50),
@moy1 int, @original varchar(50), @sdc_db varchar (50), @sdc_table varchar (50),
@data bigint,@index varchar(50),@index_query varchar(1000), @local varchar(50)

set @local=@@servername+'.'+db_name()+'.sd'
select @trigger = 'split_trigger_'+@targetable
select @proc ='triggers'
set @original=substring(@originalsite, 1, len(@originalsite)-3)
select @moy1=@moy--+1

set @data=cast(@moydata as bigint)* 6

--alter table ' +@targetable+ '  add  constraint exd_check_'+@targetable+' check ( ' +@key+ ' > ' +cast(@moydata as varchar(5000))+ ' and ' +@key+ ' <= '+cast(@data as varchar(5000))+ ' )
set @query = 
' 
select top ' + cast(@moy as varchar(5000))+'   with ties *  into sd.'  +@targetable+ ' from '+@originaltable+ ' order by '+@key+' desc
alter table sd.' +@targetable+ '  add CONSTRAINT pk_'+@targetable+' PRIMARY KEY   ( ' +@all_keys+ ' )  
alter table sd.' +@targetable+ '  add  constraint exd_check_'+@targetable+' check ( ' +@key+ ' > =' +cast(@moydata as varchar(5000))+ ' and ' +@key+ ' <= '+cast(@data as varchar(5000))+ ' )
execute  ' +@proc+ ' '''+@targetable+''' , '''+@site+''' , '''+@trigger+'''   
' 
execute (@query)

create table #index_table1 (index_name varchar(50), index_description varchar(500),index_key varchar(50))
set @index_query='
insert into #index_table1 exec sp_helpindex ''sd.' +@targetable+  ''''
exec (@index_query)
delete from #index_table1 where index_name like 'pk%'

if ( exists (select * from #index_table1)) 
begin
DECLARE c CURSOR FOR

SELECT index_name,index_key FROM #index_table1

OPEN c

FETCH NEXT FROM c
INTO @index, @key
set @query = 
'CREATE  INDEX ' +@index+ ' ON sd.'+@targetable+ ' ( '+@key+' )' 

execute (@query)


WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM c
INTO @index, @key

set @query = 
'CREATE  INDEX ' +@index+ ' ON sd.'+@targetable+ ' ( '+@key+' )' 

execute (@query)

FETCH NEXT FROM c INTO @index, @key
END

CLOSE c
DEALLOCATE c
end

--set @moy=(@moy+1)*2
--select @moy
execute  sdc @originalsite, @site, @targetable

exec split_table1 @targetable , @local

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE sdc @originalsite varchar(50), @site varchar(50), @originaltable varchar(50) AS

declare @query varchar(5000), @sdc_site varchar(50), @query1 varchar(5000), 
             @sdc_db varchar(50) , @sdc_table varchar(50), @nb_image int, @sd_table varchar(50), @primnd varchar(50), @creatnd varchar(50) 

set @sdc_db=db_name()
set @sdc_site=@originalsite+ '.prim'
set @site=SUBSTRING ( @site , 1 , CHARINDEX('.', substring(@site,1,len(@site)))-1 )
set @sd_table=substring(@originaltable,CHARINDEX('_', substring(@originaltable,2,1000))+2,1000) 

create table #t (prim varchar(50) not null, creatnd varchar(50) not null, tab varchar(50) not null)

set xact_abort on
set @query=
'print 656545646546554 
insert into sd.prim select * from ' +@originalsite+ '.prim where tab='''+@sd_table+'''
insert into  #t select * from ' +@originalsite+ '.prim
insert into sd.size select * from ' +@originalsite+ '.size where tab='''+@sd_table+'''
'

execute (@query)
set @creatnd=(select creatnd from sd.prim where tab=@sd_table)
set @primnd=(select primnd from sd.prim where tab=@sd_table)
--select "creatnd"=@creatnd
--set @sdc_db=substring(@originalsite, 1, len(@originalsite)-3)
--select "rp"=@sdc_db

set @query1 =
  '   insert into ' +@primnd+'.'+@sdc_db+ '.sd.rp values ('''+@site+''' , '''+@creatnd+''', '''+@sd_table+''') 
'--  execute maj_vue '''+@originaltable+''' ,  '''+@sdc_db+'''
execute (@query1)
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





CREATE PROCEDURE maj_table @key varchar(50), @originalsite varchar(50), @originaltable varchar(50), @firstdata sql_variant, @moydata sql_variant AS

declare @query varchar(5000), @proc varchar(50), @trigger varchar(50)

set @proc='triggers'
set @trigger='split_trigger_'+@originaltable
--alter table ' +@originaltable+ '  add CONSTRAINT c_'+@originaltable+'  PRIMARY KEY   ( '+@key+' )  
set @query=
'
alter table sd.' +@originaltable+ '  add  constraint exd_check_'+@originaltable+' check  ( ' +@key+ ' < '+cast(@moydata as varchar(500))+ ' )
  ' 
--execute  ' +@proc+ ' '''+@originaltable+''' ,  '''+@originalsite+''' , '''+@trigger+''' 
exec (@query)
--( ' +@key+ ' >= ' +cast(@firstdata as varchar(500))+ ' and ' +@key+ ' < '+cast(@moydata as varchar(500))+ ' )

GO



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE maj_vue @table varchar(50), @sdc_db varchar(50) 
AS

begin
DECLARE @db varchar(40), @query nvarchar(4000), @rp varchar(5000), 
        @view nvarchar(4000), @exec varchar(5000), @view_name varchar(50), @n int
set @n=1
set @view_name=@table+'_view'
create table #t (db_s varchar(50))
set @rp=
'
insert into #t select db_s from ' +@sdc_db+ '.dbo.sd_rp where sd_table= ''' +@table+ '''
'
exec (@rp)


if ( exists (select * from #t)) 
begin
DECLARE c CURSOR FOR
SELECT * FROM #t

OPEN c

FETCH NEXT FROM c
INTO @db

set @query = 'select * from ' +@db+ '.dbo.' +@table

WHILE @@FETCH_STATUS = 0
BEGIN

FETCH NEXT FROM c
INTO @db

set @query= @query+' 
union all select * from ' +@db+ '.dbo.'+@table

set @n=@n+1

FETCH NEXT FROM c INTO @db
END

CLOSE c
DEALLOCATE c

set @view= 'alter view '+@view_name+ ' as '+@query

--Execute ' +@sdc_db+'.dbo.sp_executesql N''' +@view+  '''
set @exec=
'Execute ' +@sdc_db+'.dbo.sp_executesql N''' +@view+  '''
 update ' +@sdc_db+ '.dbo.c_image set nbre_image='+cast(@n as varchar(10))+ ' where tab='''+@table+''' 
'
exec (@exec)
print 'tt a fait'
--select @exec


end
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE p_GetPrimaryKey
(
@TableName sysname, @key varchar(50) output
)
AS
BEGIN

SELECT @key=ccu.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES AS t
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc
ON t.TABLE_SCHEMA = tc.TABLE_SCHEMA
AND t.TABLE_NAME = tc.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu
ON tc.TABLE_SCHEMA = ccu.TABLE_SCHEMA
AND tc.TABLE_NAME = ccu.TABLE_NAME
AND tc.CONSTRAINT_SCHEMA = ccu.CONSTRAINT_SCHEMA
AND tc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
WHERE tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND t.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)), 'IsMSShipped') = 0
AND t.TABLE_NAME = @TableName

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE split_table1 @originaltable varchar(50), @originalsite varchar(50) AS

declare  @query varchar(5000), @targetable varchar(50), @linkedtable varchar (50), @site varchar(50), @trigger_name varchar (50), 
             @proc_linkedtable varchar(50), @proc_trigger varchar(50), 
             @size int,  @moy int, @site_origine varchar(50),@temp_table varchar(50),
             @firstdata sql_variant, @moydata sql_variant, @taille_link int, @sdc_db varchar(50), 
             @sdc_table varchar(50), @origine_vue varchar(50), @query_site varchar(5000),@query_size1 varchar(5000),@all_keys varchar (50),
             @query_size varchar(5000), @limit varchar(5000), @key varchar(50), @cursor varchar(50), @index_query varchar(5000),@sd_table varchar(50),@sdb varchar(50),
@local varchar(50), @creatnd varchar(5000), @cursor_moydata int, @server varchar(50), @peer varchar(50),@sdb_node  varchar(500), @primary varchar(50)

set @sdb=db_name()
set @local=@@servername
select @primary=node from msdb.dbo.metabase
--enlever 'dbo' du nom de la base
select @site_origine=SUBSTRING ( @originalsite , 1 , CHARINDEX('.', substring(@originalsite,1,len(@originalsite)-3))-1 )


select @trigger_name = 'split_trigger'--nom du trigger sur la table
set @sd_table=substring(@originaltable,CHARINDEX('_', substring(@originaltable,2,1000))+2,1000) 

select @moy=size/2 from sd.size where tab=@sd_table

select @moy=@moy+1 --rajouter 1 a la taille moyenne pk elle sera transfTrTe aprFs dans une autre table
--set @originaltable='sd.'+@originaltable
--exec p_GetPrimaryKey @originaltable, @key output
create table #index_table1 (index_name varchar(50), index_description varchar(500),index_key varchar(50))
set @index_query=' insert into #index_table1 exec sp_helpindex ''sd.' +@originaltable+  ''' '
exec (@index_query)
delete from #index_table1 where index_name not like 'pk%'
if (select charindex(',', index_key) from #index_table1 )!=0
  begin
    select  @key=substring(index_key,1,CHARINDEX(',', index_key)-1)from #index_table1 
    select @all_keys=index_key from #index_table1
  end
else
 begin
  select @key=(select index_key from #index_table1)

  select @all_keys=@key
 end 

create table #t1 (size int)
create table #t2 (firstdata sql_variant)
set @limit=
'insert into #t1 select count(*) from sd.' +@originaltable+ '
insert into #t2 select top 1  '+@key+ ' from  sd.' +@originaltable+ ' order by '+@key+  ' asc
'
execute (@limit)

set @firstdata=(select firstdata from #t2)
set @size=(select size from #t1)

--create table cursor_tab ( '+@key+' numeric(5,10 NOT NULL )
set @query_size=
'create table moydata ( '+@key+' sql_variant NOT NULL )
create table cursor_tab ( '+@key+' sql_variant NOT NULL )
'
exec(@query_size)
set @cursor_moydata=@moy+1
set @query_size1=
'insert into cursor_tab select top '+cast(@cursor_moydata as varchar(500))+' '+@key+'  from sd.' +@originaltable+ ' order by '+@key+' asc 
insert into moydata select top 1 '+@key+' from cursor_tab order by '+@key+' desc
'
exec (@query_size1)

set @moydata=(select * from moydata)

drop table cursor_tab
drop table moydata
---------------------------------------------------
select @taille_link=@size-@moy --@taille_link est la taille du nouveau segment de l'Tclatement
------------------test de la taille du segment----------------

set @server='server'
set @peer='peer'

----------------primary ndb--------------------
set @sdb_node='(select node from '+@primary+'.metabase.sd.sdb where sdb_name=''' +@sdb+ ''')'
--select @sdb_node=node from dell1.metabase.sd.sdb where sdb_name=@sdb
--------------
-------------------------
---------------------test verrou exclusif--------------------------------
--update sd.rp set tab=@sd_table where tab=@sd_table
--------------------------------------------------------
create table #t (node varchar(50))
set @query_site=
'if ( ' +cast(@size as varchar(50))+ ' > (select size from sd.size where tab= ''' +@sd_table+ ''' )) 
begin
insert into #t select top 1 node from '+@sdb_node+'.'+@sdb+ '.sd.ndb where not exists 
(select sgmnd from  '+@sdb_node+'.'+@sdb+ '.sd.rp where tab= '''+@sd_table+ ''' and sgmnd = node) and (ndbtype='''+@server+ ''' or ndbtype='''+@peer+ ''') 
end

'
execute (@query_site)

select @site= (select node from #t)
select @site=@site+'.'+@sdb

set @targetable= @site+'.'+@sdb+ '.sd.'+@originaltable
set @proc_trigger = @site+'.triggers'--nom de la PS qui rajoute un trigger sur le nouveau segment
set @proc_linkedtable =@site+ '.dbo.linkedtable'--nom de la PS de l'Tclatement sur le nouveau site
set @linkedtable=@local+'.'+@sdb+'.sd.'+@originaltable
set @originalsite=@local+'.'+@sdb+'.sd'
create table #creatnd ( creatnd varchar(50))
set @creatnd=(select creatnd from sd.prim where tab=@sd_table)


set @query=
'exec  ' +@proc_linkedtable+ ' '''+@originalsite+''' ,  '''+@linkedtable+''' , '''+@originaltable+''' ,  ''' +@site+'''  ,  ''' +@key+''' , ' +cast(@taille_link as varchar(500))+ ' , ' +cast(@moydata as varchar(500))+ ' ,  ''' +@all_keys+''' 
delete from sd.' +@originaltable+ ' where '+@key+ ' in (select top  ' +cast(@taille_link as varchar(500))+@key+ '  from sd.'+@originaltable+'  order by '+@key+ ' desc)
exec maj_table  '''+@key+''' , '''+@site_origine+''' , '''+@originaltable+''' , '''+cast(@firstdata as varchar(500))+''' , '''+cast(@moydata as varchar(500))+'''
'
execute (@query)
GO


SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE triggers @targetable varchar(50), @originalsite varchar (50), @trigger varchar(50) AS

declare @query varchar (5000), @proc_split varchar(50),@db varchar(50), @job varchar(50)
--set @db=@originalsite+'.dbo'
set @proc_split='split_table'
--' +@proc_split+ ' '''+@targetable+''' ,  '''+@originalsite+''

set @job='split'
--set @node_ndb=@ndb+'.'+@db+'.sd'
set @db=@originalsite+'.sd'
set @query=
'
CREATE TRIGGER split_trigger' +@targetable+ ' ON sd.'+@targetable+'
after INSERT
AS
create table job (segment varchar(50), ndb varchar(50))
insert into job (segment, ndb) values ('''+@targetable+ ''', '''+@db+ ''')
EXEC msdb..sp_start_job  '''+@job+'''
'
exec (@query)

/*set @query =
'CREATE TRIGGER ' +@trigger+ ' ON sd.' +@targetable+ '
after INSERT
AS
execute ' +@proc_split+ ' '''+@targetable+''' ,  '''+@db+''' '

execute (@query)*/

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO