create procedure sys.sp_MScreate_pub_tables
AS
DECLARE @retcode int,
@fError int
SELECT @fError = 0
-- enable 'create tables as pseudo system tables
/*
** Msg 226, Level 16, State 9
** CREATE TABLE system-table command not allowed within multi-statement transaction.
*/
/*
BEGIN TRAN sp_create_central_pub_tables
*/
/* Creating 'sysarticles' */
IF not exists (select * from sys.objects where name = 'sysarticles')
BEGIN
create table dbo.sysarticles
(
artid int identity NOT NULL,
creation_script nvarchar(255) NULL,
del_cmd nvarchar(255) NULL,
description nvarchar(255) NULL,
dest_table sysname NOT NULL,
filter int NOT NULL,
filter_clause ntext NULL,
ins_cmd nvarchar(255) NULL,
name sysname NOT NULL,
objid int NOT NULL,
pubid int NOT NULL,
pre_creation_cmd tinyint NOT NULL,
status tinyint NOT NULL,
sync_objid int NOT NULL,
type tinyint NOT NULL,
upd_cmd nvarchar(255) NULL,
schema_option binary(8) NULL,
dest_owner sysname NULL,
ins_scripting_proc int NULL, -- object id of custom scripting proc registered by user, to replace sp_scriptinsproc
del_scripting_proc int NULL, -- object id of custom scripting proc registered by user, to replace sp_scriptdelproc
upd_scripting_proc int NULL, -- object id of custom scripting proc registered by user, to replace sp_scriptupdproc
custom_script nvarchar(2048) NULL, -- custom script, set by sp_register_custom_script, cleared by DDL trigger
-- or by sp_unregister_custom_script
fire_triggers_on_snapshot bit NOT NULL default 0
-- Note: Please update sysextendedarticlesview whenever
-- there is a schema change in sysarticles
)
exec dbo.sp_MS_marksystemobject 'sysarticles'
IF @@error<>0
BEGIN
GOTO ERROR
END
create unique clustered index c1sysarticles
on sysarticles(artid, pubid)
IF @@error<>0
BEGIN
GOTO ERROR
END
END
IF not exists (select * from sys.objects where name = 'sysarticlecolumns')
BEGIN
create table dbo.sysarticlecolumns
(
artid int NOT NULL,
colid int NOT NULL,
is_udt bit default 0,
is_xml bit default 0,
is_max bit default 0
)
create unique clustered index idx_sysarticlecolumns on sysarticlecolumns (artid, colid)
EXEC dbo.sp_MS_marksystemobject 'sysarticlecolumns'
IF @@error<>0
BEGIN
GOTO ERROR
END
END
/* Creating 'sysschemaarticles' */
IF not exists (select * from sys.objects where name = 'sysschemaarticles')
BEGIN
create table dbo.sysschemaarticles
(
artid int NOT NULL,
creation_script nvarchar(255) NULL,
description nvarchar(255) NULL,
dest_object sysname NOT NULL,
name sysname NOT NULL,
objid int NOT NULL,
pubid int NOT NULL,
pre_creation_cmd tinyint NOT NULL,
status int NOT NULL,
type tinyint NOT NULL,
schema_option binary(8) NULL,
dest_owner sysname NULL
)
IF @@error<>0
BEGIN
GOTO ERROR
END
exec dbo.sp_MS_marksystemobject 'sysschemaarticles'
IF @@error<>0
BEGIN
GOTO ERROR
END
create unique clustered index c1sysschemaarticles
on sysschemaarticles(artid, pubid)
IF @@error<>0
BEGIN
GOTO ERROR
END
END
/* Creating 'sysextendedarticlesview' */
IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
BEGIN
exec ('create view dbo.sysextendedarticlesview
as
select artid, creation_script, del_cmd, description,
dest_table, filter, filter_clause, ins_cmd, name, objid, pubid,
pre_creation_cmd, status, sync_objid, type, upd_cmd,
schema_option, dest_owner, ins_scripting_proc, del_scripting_proc,
upd_scripting_proc, custom_script, fire_triggers_on_snapshot
from sysarticles
union all
select artid, creation_script, NULL, description,
dest_object, NULL, NULL, NULL, name, objid, pubid,
pre_creation_cmd, status, NULL, type, NULL,
schema_option, dest_owner, NULL, NULL, NULL, NULL, 0
from sysschemaarticles
go')
IF @@error<>0
BEGIN
GOTO ERROR
END
exec dbo.sp_MS_marksystemobject 'sysextendedarticlesview'
IF @@error<>0
BEGIN
GOTO ERROR
END
END
/* Creating 'syspublications' */
IF NOT EXISTS (select * from sys.objects where name = 'syspublications')
BEGIN
CREATE TABLE dbo.syspublications (
description nvarchar(255) NULL,
name sysname NOT NULL,
pubid int identity NOT NULL,
repl_freq tinyint NOT NULL,
status tinyint NOT NULL,
sync_method tinyint NOT NULL,
snapshot_jobid binary(16) NULL,
independent_agent bit NOT NULL,
immediate_sync bit NOT NULL,
enabled_for_internet bit NOT NULL,
allow_push bit NOT NULL,
allow_pull bit NOT NULL,
allow_anonymous bit NOT NULL,
immediate_sync_ready bit NOT NULL,
-- SyncTran
allow_sync_tran bit NOT NULL,
autogen_sync_procs bit NOT NULL,
retention int NULL,
-- The following are post 7.0
allow_queued_tran bit default 0 not null,
-- portable snapshot support
snapshot_in_defaultfolder bit default 1 NOT NULL,
alt_snapshot_folder nvarchar(255) NULL,
-- snapshot pre/post- command
pre_snapshot_script nvarchar(255) NULL,
post_snapshot_script nvarchar(255) NULL,
-- Snapshot compression
compress_snapshot bit default 0 NOT NULL,
-- Post 7.0 Ftp support
ftp_address sysname NULL,
ftp_port int default 21 NOT NULL,
ftp_subdirectory nvarchar(255) NULL,
ftp_login sysname NULL default N'anonymous',
ftp_password nvarchar(524) NULL,
allow_dts bit default 0 not null,
allow_subscription_copy bit default 0 not null,
centralized_conflicts bit NULL, -- 0 False, 1 True
conflict_retention int NULL, -- 60
conflict_policy int NULL, -- 1 = PubWins, 2 = SubWins, 3 = Reinit
queue_type int NULL, -- 1 = MSMQ, 2 = SQL
ad_guidname sysname NULL,
backward_comp_level int default 10 not NULL, -- default is sphinx
allow_initialize_from_backup bit default 0 NOT NULL,
min_autonosync_lsn binary(10) NULL,
replicate_ddl int default 1,
options int default 0 not null,
originator_id int NULL, -- 4 bytes are used
)
exec dbo.sp_MS_marksystemobject 'syspublications'
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create unique clustered index uc1syspublications
on syspublications (pubid)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create unique nonclustered index unc2syspublications
on syspublications (name)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create nonclustered index nc3syspublications
on syspublications (status)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
/* Creating 'syssubscriptions' */
IF not exists (select * from sys.objects where name = 'syssubscriptions')
BEGIN
CREATE TABLE dbo.syssubscriptions
(
artid int NOT NULL,
srvid smallint NOT NULL,
dest_db sysname NOT NULL,
status tinyint NOT NULL,
sync_type tinyint NOT NULL,
login_name sysname NOT NULL,
subscription_type int NOT NULL,
distribution_jobid binary(16) NULL,
timestamp NOT NULL,
-- SyncTran
update_mode tinyint NOT NULL, -- 0(read only), 1(Sync Tran), 2(Queued Tran), 3(Failover),
-- 4(sqlqueued), 5(sqlqueued failover), 6(sqlqueued qfailover), 7(qfailover)
loopback_detection bit NOT NULL,
queued_reinit bit DEFAULT 0 NOT NULL,
nosync_type tinyint DEFAULT 0 NOT NULL -- 0(none), 1(replication support only), 2(initialize with backup), 3(initialize from lsn)
,srvname sysname not null default N''
)
exec dbo.sp_MS_marksystemobject 'syssubscriptions'
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create unique nonclustered index unc1syssubscriptions
on syssubscriptions (artid, srvid, dest_db, srvname)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
-- SyncTran
/* Creating 'sysarticleupdates' */
IF not exists (select * from sys.objects where name = 'sysarticleupdates')
BEGIN
CREATE TABLE dbo.sysarticleupdates
(
artid int NOT NULL,
pubid int NOT NULL,
sync_ins_proc int NOT NULL, -- ID of sproc handling Insert Sync Transactions
sync_upd_proc int NOT NULL, -- ID of sproc handling Update Sync Transactions
sync_del_proc int NOT NULL, -- ID of sproc handling Delete Sync Transactions
autogen bit NOT NULL,
sync_upd_trig int NOT NULL, -- Note 7.0 upgrade issue
conflict_tableid int NULL, -- ID of conflict table for this article
ins_conflict_proc int NULL, -- ID of sproc to log conflicts
identity_support bit default 0 not null -- Whether or not do auto identity range
)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
-- mark the index as a system object
exec dbo.sp_MS_marksystemobject 'sysarticleupdates'
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create unique nonclustered index unc1sysarticleupdates
on sysarticleupdates (artid, pubid)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
-- end SyncTran
IF not exists (select * from sys.objects where name = 'MSpub_identity_range')
BEGIN
CREATE TABLE dbo.MSpub_identity_range
(
objid int not null,
range bigint not null,
pub_range bigint not null,
current_pub_range bigint not null,
threshold int not null,
last_seed bigint null -- It will be not when uninitialized.
)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
-- mark the index as a system object
exec dbo.sp_MS_marksystemobject 'MSpub_identity_range'
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create unique nonclustered index unc1MSpub_identity_range
on MSpub_identity_range (objid)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
IF not exists (select * from sys.objects where name = 'systranschemas' and schema_id = 1)
BEGIN
CREATE TABLE dbo.systranschemas
(
tabid int not null,
startlsn binary(10) not null,
endlsn binary(10) not null,
typeid int not null default 52
)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
-- mark the index as a system object
exec dbo.sp_MS_marksystemobject 'systranschemas'
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
create unique clustered index uncsystranschemas
on systranschemas (startlsn)
IF @@ERROR <> 0
BEGIN
GOTO ERROR
END
END
-- We will always create PeerToPeer tables at the publisher. Subscriber side
-- tables will only be created when subscribed to PeerToPeer publication
exec @retcode = sys.sp_MScreate_peer_tables
IF @retcode <> 0 or @@ERROR <> 0
BEGIN
GOTO ERROR
END
EXEC @retcode = sys.sp_MSrepl_ddl_triggers @type='tran', @mode='drop'
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO ERROR
END
EXEC @retcode = sys.sp_MSrepl_ddl_triggers @type='tran', @mode='add'
IF @@ERROR <> 0 or @retcode <> 0
BEGIN
GOTO ERROR
END
CLEANUP:
RETURN( @fError )
ERROR:
select @fError = 1
GOTO CLEANUP
