CREATE TABLE Metabase ([Node] [varchar] (50))
go

CREATE PROCEDURE create_ndb @sdb_node varchar(50),@db varchar(50),@type varchar(50) AS

declare @query varchar(5000),@local varchar(50),@table varchar(5000),@insert varchar(5000),@primary varchar(50),@script varchar(5000),@exec varchar(50), @query_exec varchar(5000),
@server varchar(50),@peer varchar(50),@node varchar(50),@node_type varchar(5000)

set @local=@@servername

select @primary=node from msdb.dbo.metabase
set @server='server'
set @peer='peer'
set @query='
create database '+@db+'
'
exec (@query)
exec add_role @db


set @table='
CREATE TABLE  '+@db+ '.[SD].[SDBNode] ([Node] [varchar] (50) )
CREATE TABLE  '+@db+ '.[SD].[MDBNode] ([Node] [varchar] (50) )
'
exec (@table)
----------------------------
if (@type='client')
begin
 set @exec='master..xp_cmdshell' 
  set @script='osql /U sa /P /d' +@db+ '< c:\script\client.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)

create table #node_t  (node varchar(50))
set @node_type=
'insert into #node_t select node from '+@primary+'.metabase.sd.sdb where sdb_name= '''+@db+ ''' and ( ndbtype=  '''+@server+ '''  or  ndbtype=  '''+@peer+ ''') '

exec( @node_type)

if ( exists (select * from #node_t))
While ( exists (select * from #node_t))
 BEGIN
  set @node=(select top 1 node from #node_t)
  set @server =  'insert into ' +@db+'.sd.server values ('''+@node+''')   '
   exec (@server)
   delete from #node_t  where node=@node
  END

end
else if (@type='server')
begin
 set @exec='master..xp_cmdshell' 
  set @script='osql /U sa /P /d' +@db+ '< c:\script\server.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)
end
else if (@type='peer')
begin
 set @exec='master..xp_cmdshell' 
  set @script='osql /U sa /P /d' +@db+ '< c:\script\server.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)
  set @script='osql /U sa /P /d' +@db+ '< c:\script\client.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
	exec(@query_exec)
end
-------------------------------
set @insert='
insert into ' +@sdb_node+'.' +@db+'.sd.ndb values ('''+@local+ ''', '''+@type+''')
insert into ' +@db+'.sd.SDBNode values ('''+@sdb_node+''')
insert into ' +@db+'.sd.MDBNode values ('''+@primary+''')'
-----------------------------------------
exec (@insert)
GO


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE drop_ndb @ndb varchar(50) AS

declare @query varchar(5000)

set @query='
drop database ' +@ndb+'
'
exec (@query)
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE add_role @db varchar(50)  AS

declare @role varchar(5000), @owner varchar(50)
set @owner='sd'

set @role='
use '+@db+'
exec sp_addrole ''' +@owner+  '''
'
exec (@role)
select @role

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE create_sdb @db varchar(50),@type varchar(50) AS

declare @query varchar(5000),@local varchar(50),@table varchar(5000),@insert varchar(5000),@primary varchar(50),@script varchar(5000),@exec varchar(50), @query_exec varchar(5000)

set @local=@@servername

set @primary=(select node from metabase)
set @query='
create database '+@db+'
'
exec (@query)
exec add_role @db

set @table='
CREATE TABLE ' +@db+'.[SD].[NDB] (Node  [varchar] (50),[NDBType] [varchar] (50) ) 
CREATE TABLE  '+@db+ '.[SD].[MDBNode] ([Node] [varchar] (50) )
'
exec (@table)
----------------------------

if (@type='client')
begin
 set @exec='master..xp_cmdshell' 
  set @script='osql /U sa /P /d' +@db+ '< c:\script\client.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)
end
else if (@type='server')
begin
 set @exec='master..xp_cmdshell' 
  set @script='osql /U sa /P /d' +@db+ '< c:\script\server.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)
end
else if (@type='peer')
begin
 set @exec='master..xp_cmdshell' 
  set @script='osql /U sa /P /d' +@db+ '< c:\script\server.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)
  set @script='osql /U sa /P /d' +@db+ '< c:\script\client.sql'
  set @query_exec ='execute '+@exec+ ' '''+@script+''' '
  exec(@query_exec)
end
-----------------------------
set @insert='
insert into ' +@db+'.sd.ndb values ('''+@local+ ''', '''+@type+''')
insert into ' +@db+'.sd.MDBNode values ('''+@primary+''')'
exec (@insert)

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE PROCEDURE drop_node @node varchar (50) AS

declare @query varchar (5000)

set @query ='
drop procedure create_ndb
drop procedure create_sdb
drop procedure drop_ndb
drop procedure add_role
drop procedure drop_node
'
exec (@query)
GO
