May 8, 2012

sp_MScreate_dist_tables (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MScreate_dist_tables()

MetaData:

 create procedure sys.sp_MScreate_dist_tables  
AS
begin
DECLARE @Cmd varchar(4000)

-- Only sysadmin and db_owner of a distribution database are allowed
-- to create replication tables in the distribution database
if (is_srvrolemember('sysadmin') <> 1 and
is_member('db_owner') <> 1)
begin
raiserror(21050, 14, -1)
return (1)
end

--
-- create replmonitor role if it does not exist
--
if not exists (select * from sys.database_principals
where name = N'replmonitor' and type = 'R')
begin
EXEC dbo.sp_addrole 'replmonitor'
end

--
-- Important:
-- We use varbinary(16) for xact_id and xact_seqno, we don't want ending nulls
-- to be truncated by the server
--
-- Also, in MSrepl_commands, we don't want ending space to be truncated.
--
SET ANSI_PADDING ON

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSredirected_publishers' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSredirected_publishers', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSredirected_publishers
(
original_publisher sysname NOT NULL,
publisher_db sysname NOT NULL,
redirected_publisher sysname NOT NULL
)
CREATE UNIQUE CLUSTERED INDEX ucMSredirected_publishers ON dbo.MSredirected_publishers
(original_publisher, publisher_db)

exec dbo.sp_MS_marksystemobject 'MSredirected_publishers'

END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_version' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSrepl_version', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSrepl_version
(
major_version int NOT NULL,
minor_version int NOT NULL,
revision int NOT NULL,
db_existed bit NULL
)
CREATE UNIQUE CLUSTERED INDEX ucMSrepl_version ON dbo.MSrepl_version
(major_version, minor_version, revision)

exec dbo.sp_MS_marksystemobject 'MSrepl_version'

INSERT INTO MSrepl_version VALUES (7,0,0,0)
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSpublisher_databases' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSpublisher_databases', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSpublisher_databases(
publisher_id smallint NOT NULL,
publisher_db sysname NULL,
id int identity NOT NULL,
publisher_engine_edition int null
)
exec dbo.sp_MS_marksystemobject 'MSpublisher_databases'

raiserror('Creating clustered index ucMSpublisher_databases', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSpublisher_databases ON dbo.MSpublisher_databases
(publisher_id, publisher_db, id)
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'publisher_engine_edition'
and object_id=object_id('MSpublisher_databases'))
BEGIN
ALTER TABLE MSpublisher_databases ADD publisher_engine_edition int null
END
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSpublications' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSpublications', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSpublications (
publisher_id smallint NOT NULL,
publisher_db sysname NULL,
publication sysname NOT NULL,
publication_id int identity NOT NULL, -- This id IS NOT the same as the SQL Server publisher's
publication_type int NOT NULL, -- 0 = Snapshot 1 = Transactional
thirdparty_flag bit NOT NULL, -- 0 = SQL Server 1 = Third Party
independent_agent bit NOT NULL,
immediate_sync bit NOT NULL,
allow_push bit NOT NULL,
allow_pull bit NOT NULL,
allow_anonymous bit NOT NULL,
description nvarchar(255) NULL,
vendor_name nvarchar(100) NULL,
retention int NULL,
sync_method int default 0 NOT NULL,
allow_subscription_copy bit default 0 not null,
thirdparty_options int null,
allow_queued_tran bit default 0 not null,
options int default 0 not null,
retention_period_unit tinyint default 0 not null,
allow_initialize_from_backup bit not null default 0,
min_autonosync_lsn varbinary(16) NULL
)
exec dbo.sp_MS_marksystemobject 'MSpublications'

-- publication_id needs to be the first columns in the index. It
-- is used in sp_MSmaximum_cleanup_seqno.
raiserror('Creating clustered index ucMSpublications', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSpublications ON dbo.MSpublications
(publication_id, publication, publisher_db, publisher_id)
CREATE UNIQUE INDEX uc2MSpublications ON dbo.MSpublications
(publication, publisher_db, publisher_id)
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'sync_method'
and object_id=object_id('MSpublications'))
BEGIN
ALTER TABLE MSpublications ADD sync_method int default 0 not null
END

IF NOT EXISTS (select * from sys.columns
where name = 'allow_subscription_copy'
and object_id=object_id('MSpublications'))
BEGIN
ALTER TABLE MSpublications ADD allow_subscription_copy bit default 0 not null
END
IF NOT EXISTS (select * from sys.columns
where name = 'thirdparty_options'
and object_id=object_id('MSpublications'))
BEGIN
ALTER TABLE MSpublications ADD thirdparty_options int null
END

IF NOT EXISTS (select * from sys.columns
where name = 'allow_queued_tran'
and object_id=object_id('MSpublications'))
BEGIN
ALTER TABLE MSpublications ADD allow_queued_tran bit default 0 not null
END

IF NOT EXISTS (select * from sys.columns
where name = 'options'
and object_id=object_id('MSpublications'))
BEGIN
exec ('ALTER TABLE MSpublications ADD options int default 0 not null')
-- Set enabled for het sub bit in options column if sync_method = 1
exec ('update MSpublications set options = 0x4 where sync_method = 1')
END

IF NOT EXISTS (select * from sys.columns
where name = 'allow_initialize_from_backup'
and object_id=object_id('MSpublications'))
BEGIN
ALTER TABLE MSpublications ADD allow_initialize_from_backup bit default 0 not null
END

IF NOT EXISTS (select * from sys.columns
where name = 'min_autonosync_lsn'
and object_id=object_id('MSpublications'))
BEGIN
ALTER TABLE MSpublications ADD min_autonosync_lsn varbinary(16) NULL
END

END


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSarticles' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSarticles', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSarticles (
publisher_id smallint NOT NULL,
publisher_db sysname NULL,
publication_id int NOT NULL,
article sysname NOT NULL,
article_id int NOT NULL, -- This id is the same as a SQL Server Publisher's
destination_object sysname NULL,
source_owner sysname NULL,
source_object sysname NULL,
description nvarchar(255) NULL,
destination_owner sysname NULL
)

exec dbo.sp_MS_marksystemobject 'MSarticles'

raiserror('Creating clustered index ucMSarticles', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSarticles ON dbo.MSarticles (publisher_db, publisher_id, article_id, article, publication_id)
END
ELSE
BEGIN
IF NOT EXISTS(select * from sys.columns
where object_id = object_id(N'MSarticles')
and name = N'destination_owner')
BEGIN
ALTER TABLE MSarticles ADD destination_owner sysname NULL
END
END

IF NOT EXISTS( SELECT * from sys.objects WHERE name = 'MSsync_states' )
BEGIN
CREATE TABLE dbo.MSsync_states
(
publisher_id smallint not null,
publisher_db sysname not null,
publication_id int not null
)
exec dbo.sp_MS_marksystemobject 'MSsync_states'

CREATE UNIQUE CLUSTERED INDEX ucMSsyncstates on dbo.MSsync_states
( publisher_id, publisher_db, publication_id )
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsubscriptions' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSsubscriptions', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSsubscriptions
(
publisher_database_id int NOT NULL, -- Used to reference MSrepl_transactions and MSrepl_commands
publisher_id smallint NOT NULL,
publisher_db sysname NOT NULL,
publication_id int NOT NULL,
article_id int NOT NULL,
subscriber_id smallint NOT NULL,
subscriber_db sysname NOT NULL,
subscription_type int NOT NULL, -- 0 = push, 1 = pull, 2 = anonymous
sync_type tinyint NOT NULL, -- 1 = automatic 2 = no sync
status tinyint NOT NULL, -- 0 = inactive, 1 = subscribed, 2 = active
subscription_seqno varbinary(16) NOT NULL, -- publisher's database sequence number
snapshot_seqno_flag bit NOT NULL, -- 1 if subscription_seqno is the snapshot seqno
independent_agent bit NOT NULL, -- Value carried over from MSpublications
subscription_time datetime NOT NULL,
loopback_detection bit NOT NULL,
agent_id int NOT NULL,
update_mode tinyint NOT NULL, -- 0 = read only, 1 = sync tran, 2 = queued tran, 3 = failover, 4 = sqlqueued tran,
-- 5 = sqlqueued failover, 6 = sqlqueued qfailover, 7 = qfailover
publisher_seqno varbinary(16) NOT NULL,
ss_cplt_seqno varbinary(16) NOT NULL,
nosync_type tinyint DEFAULT 0 NOT NULL -- 0(none), 1(replication support only), 2(initialize with backup), 3(initialize from lsn)
)

exec dbo.sp_MS_marksystemobject 'MSsubscriptions'

raiserror('Creating clustered index ucMSsubscirptions', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSsubscriptions ON dbo.MSsubscriptions
(agent_id, article_id)

-- Index used by sp_MSdelete_publisherdb_trans
raiserror('Creating index iMSsubscriptions', 0,1)
CREATE INDEX iMSsubscriptions ON dbo.MSsubscriptions
(publisher_database_id, article_id, subscriber_id, subscriber_db, publication_id, publisher_db, publisher_id)
END
ELSE
BEGIN
if not exists ( select * from sys.columns
where object_id = object_id( N'MSsubscriptions' )
and name = N'ss_cplt_seqno' )
begin
exec ('alter table MSsubscriptions add ss_cplt_seqno varbinary(16) NULL')
exec ('update MSsubscriptions set ss_cplt_seqno = publisher_seqno')
exec ('alter table MSsubscriptions alter column ss_cplt_seqno varbinary(16) NOT NULL')
end
END

-- For beta 3 upgrade, we need to create new index here.
if not exists (select * from sysindexes where name = 'iMSsubscriptions2')
begin
-- Index used by sp_MSdelete_publisherdb_trans
raiserror('Creating index iMSsubscriptions2', 0,1)
CREATE INDEX iMSsubscriptions2 ON dbo.MSsubscriptions
(publisher_database_id, subscription_seqno)
end


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_subscriptions' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSmerge_subscriptions', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSmerge_subscriptions
(
publisher_id smallint NOT NULL,
publisher_db sysname NULL,
publication_id int NOT NULL,
subscriber_id smallint NULL,
subscriber_db sysname NULL,
subscription_type int NULL, -- 0 = push, 1 = pull, 2 = anonymous
sync_type tinyint NOT NULL, -- 1 = automatic 2 = no sync
status tinyint NOT NULL, -- 0 = inactive, 1 = subscribed, 2 = active
subscription_time datetime NOT NULL,
description nvarchar(255) NULL,
publisher sysname NULL,
subscriber sysname NULL,
subid uniqueidentifier NOT NULL,
subscriber_version int NULL
)
exec dbo.sp_MS_marksystemobject 'MSmerge_subscriptions'

raiserror('Creating clustered index ucMSmerge_subscriptions', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSmerge_subscriptions ON dbo.MSmerge_subscriptions(subid)

CREATE UNIQUE INDEX unc1MSmerge_subscriptions ON dbo.MSmerge_subscriptions
(subscriber, subscriber_db, publisher_id, publisher_db, publication_id)

END
else
begin
--
* Call proc to upgrade MSmerge_subscriptions
--
exec sp_MSmerge_subscriptions_upgrade
if @@error <> 0
return(1)

END


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_transactions' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSrepl_transactions', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSrepl_transactions
(
publisher_database_id int NOT NULL,
xact_id varbinary(16) NULL,
xact_seqno varbinary (16 ) NOT NULL,
entry_time datetime NOT NULL
)

exec dbo.sp_MS_marksystemobject 'MSrepl_transactions'

raiserror('Creating clustered index usMSrepl_transactions', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSrepl_transactions ON dbo.MSrepl_transactions
(publisher_database_id, xact_seqno)
WITH STATISTICS_NORECOMPUTE

CREATE STATISTICS stat_publisher_database_id
ON MSrepl_transactions (publisher_database_id)
WITH NORECOMPUTE

CREATE STATISTICS stat_xact_id
ON MSrepl_transactions (xact_id)
WITH NORECOMPUTE

CREATE STATISTICS stat_xact_seqno
ON MSrepl_transactions (xact_seqno)
WITH NORECOMPUTE

CREATE STATISTICS stat_entry_time
ON MSrepl_transactions (entry_time)
WITH NORECOMPUTE

END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_commands' and type = 'U')
BEGIN

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSrepl_commands', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --


CREATE TABLE dbo.MSrepl_commands (
publisher_database_id int not null,
xact_seqno varbinary(16) not null,
type int not null,
article_id int not null,
originator_id int not null,
command_id int not null,
partial_command bit not null,
command varbinary(1024) NULL,
hashkey int default 0,
originator_lsn varbinary(16) NULL
)


exec dbo.sp_MS_marksystemobject 'MSrepl_commands'

raiserror('Creating clusterd index ucMSrepl_commands', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSrepl_commands ON dbo.MSrepl_commands
(publisher_database_id, xact_seqno, command_id)
WITH STATISTICS_NORECOMPUTE

-- CREATE INDEX uncMSrepl_commands_originator ON dbo.MSrepl_commands
-- (originator_id, originator_lsn)

CREATE STATISTICS stat_xact_seqno
ON MSrepl_commands (xact_seqno)
WITH NORECOMPUTE

CREATE STATISTICS stat_type
ON MSrepl_commands (type)
WITH NORECOMPUTE

CREATE STATISTICS stat_article_id
ON MSrepl_commands (article_id)
WITH NORECOMPUTE

CREATE STATISTICS stat_originator_id
ON MSrepl_commands (originator_id)
WITH NORECOMPUTE

CREATE STATISTICS stat_command_id
ON MSrepl_commands (command_id)
WITH NORECOMPUTE

CREATE STATISTICS stat_partial_command
ON MSrepl_commands (partial_command)
WITH NORECOMPUTE

END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'hashkey'
and object_id=object_id('MSrepl_commands'))
BEGIN
ALTER TABLE MSrepl_commands ADD hashkey int default 0
END

IF NOT EXISTS (select * from sys.columns
where name = 'originator_lsn'
and object_id=object_id('MSrepl_commands'))
BEGIN
ALTER TABLE MSrepl_commands ADD originator_lsn varbinary(16) NULL
END
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_originators' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSrepl_orginators', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSrepl_originators
(
id int identity not null,
publisher_database_id int not null,
srvname sysname not null,
dbname sysname not null,
publication_id int NULL,
dbversion int NULL
)

exec dbo.sp_MS_marksystemobject 'MSrepl_originators'

raiserror('Creating clustered index usMSrepl_originators', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSrepl_originators ON dbo.MSrepl_originators
(id, srvname, dbname, publication_id, dbversion)
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'publication_id'
and object_id=object_id('MSrepl_originators'))
BEGIN
ALTER TABLE MSrepl_originators ADD publication_id int NULL
END

IF NOT EXISTS (select * from sys.columns
where name = 'dbversion'
and object_id=object_id('MSrepl_originators'))
BEGIN
ALTER TABLE MSrepl_originators ADD dbversion int NULL
END

raiserror('Creating clustered index usMSrepl_originators', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSrepl_originators ON dbo.MSrepl_originators
(id, srvname, dbname, publication_id, dbversion)
WITH DROP_EXISTING
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsubscriber_info' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSsubscriber_info', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSsubscriber_info
(
publisher sysname NOT NULL,
subscriber sysname NOT NULL,
type tinyint NOT NULL, -- 0: MS SQL Server 1: ODBC Data Source --
login sysname NULL,
password nvarchar(524) NULL,
description nvarchar(510) NULL,
security_mode int NOT NULL
)

exec dbo.sp_MS_marksystemobject 'MSsubscriber_info'

raiserror('Creating clustered index ucMSsubscriber_info', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_info ON dbo.MSsubscriber_info
(publisher, subscriber)
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'password'
and object_id=object_id('MSsubscriber_info'))
BEGIN
ALTER TABLE MSsubscriber_info ADD password nvarchar (524) NULL
END
ELSE
BEGIN
ALTER TABLE MSsubscriber_info ALTER COLUMN password nvarchar (524) NULL
END

IF NOT EXISTS (select * from sys.columns
where name = 'description'
and object_id=object_id('MSsubscriber_info'))
BEGIN
ALTER TABLE MSsubscriber_info ADD description nvarchar (510) NULL
UPDATE MSsubscriber_info SET description = 'SQL Server 6.0'
END
ELSE
BEGIN
ALTER TABLE MSsubscriber_info ALTER COLUMN description nvarchar (510) NULL
END
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsubscriber_schedule' and type = 'U')
BEGIN
raiserror('Creating table MSsubscriber_schedule', 0,1)

CREATE TABLE dbo.MSsubscriber_schedule
(
publisher sysname NOT NULL,
subscriber sysname NOT NULL,
agent_type smallint NOT NULL, -- 0 for distribution agent, 1 for merge agent
frequency_type int NOT NULL,
frequency_interval int NOT NULL,
frequency_relative_interval int NOT NULL,
frequency_recurrence_factor int NOT NULL,
frequency_subday int NOT NULL,
frequency_subday_interval int NOT NULL,
active_start_time_of_day int NOT NULL,
active_end_time_of_day int NOT NULL,
active_start_date int NOT NULL,
active_end_date int NOT NULL
)
exec dbo.sp_MS_marksystemobject 'MSsubscriber_schedule'

CREATE UNIQUE CLUSTERED INDEX ucMSsubscriber_schedule ON dbo.MSsubscriber_schedule
(publisher, subscriber, agent_type)

END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsnapshot_history' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSsnapshot_history', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSsnapshot_history
(
agent_id int NOT NULL,
runstatus int NOT NULL,
start_time datetime NOT NULL,
time datetime NOT NULL,
duration int NOT NULL,
comments nvarchar(1000) NOT NULL,

-- Session summary statistics
delivered_transactions int NOT NULL,
delivered_commands int NOT NULL,
delivery_rate float NOT NULL,

error_id int NOT NULL,
timestamp NOT NULL
)

exec dbo.sp_MS_marksystemobject 'MSsnapshot_history'

raiserror('Creating clustered index ucMSsnapshot_history', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSsnapshot_history ON dbo.MSsnapshot_history
(agent_id, timestamp, start_time, time)

END
ELSE
BEGIN
if exists (select * from sysindexes where name = 'nc1MSsnapshot_history' and id=object_id('MSsnapshot_history'))
begin
drop index MSsnapshot_history.nc1MSsnapshot_history
end
if exists (select * from sys.columns where name = 'comments' and object_id=object_id('MSsnapshot_history'))
begin
alter table MSsnapshot_history alter column comments nvarchar(1000) NOT NULL
end
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSlogreader_history' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSlogreader_history', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MSlogreader_history
(
agent_id int NOT NULL,
runstatus int NOT NULL,
start_time datetime NOT NULL,
time datetime NOT NULL,
duration int NOT NULL,
comments nvarchar(4000) NOT NULL,
xact_seqno varbinary(16) NULL,

-- Session summary statistics
delivery_time int NOT NULL,
delivered_transactions int NOT NULL,
delivered_commands int NOT NULL,
average_commands int NOT NULL,
delivery_rate float NOT NULL,
delivery_latency int NOT NULL,

error_id int NOT NULL,
timestamp NOT NULL,
updateable_row bit NOT NULL default 0
)

exec dbo.sp_MS_marksystemobject 'MSlogreader_history'

raiserror('Creating clustered index ucMSlogreader_history', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSlogreader_history ON dbo.MSlogreader_history
(agent_id, timestamp, runstatus, start_time, time)

END
ELSE
BEGIN
if exists (select * from sysindexes where name = 'nc1MSlogreader_history' and id=object_id('MSlogreader_history'))
begin
drop index MSlogreader_history.nc1MSlogreader_history
end
if exists (select * from sys.columns where name = 'comments' and object_id=object_id('MSlogreader_history'))
begin
alter table MSlogreader_history alter column comments nvarchar(4000) NOT NULL
end
if not exists (select * from sys.columns where name = 'updateable_row' and object_id=object_id('MSlogreader_history'))
begin
alter table MSlogreader_history add updateable_row bit NOT NULL default 0
end
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSdistribution_history' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSdistribution_history', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSdistribution_history
(
agent_id int NOT NULL,
runstatus int NOT NULL,
start_time datetime NOT NULL,
time datetime NOT NULL,
duration int NOT NULL,
comments nvarchar(max) NOT NULL,
xact_seqno varbinary(16) NULL,

-- Current statistics
current_delivery_rate float NOT NULL,
current_delivery_latency int NOT NULL,

-- Session summary statistics
delivered_transactions int NOT NULL,
delivered_commands int NOT NULL,
average_commands int NOT NULL,
delivery_rate float NOT NULL,
delivery_latency int NOT NULL,

-- Summary statistics across all sessions
total_delivered_commands bigint NOT NULL,

error_id int NOT NULL,
updateable_row bit NOT NULL,
timestamp NOT NULL
)

exec dbo.sp_MS_marksystemobject 'MSdistribution_history'

raiserror('Creating clustered index ucMSdistribution_history', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSdistribution_history ON dbo.MSdistribution_history
(agent_id, timestamp, runstatus, start_time, time)

END
ELSE
BEGIN
IF EXISTS (select * from sys.columns
where name = 'comments'
and object_id=object_id('MSdistribution_history'))
BEGIN
ALTER TABLE MSdistribution_history ALTER COLUMN comments nvarchar(max) NOT NULL
END
if exists (select * from sysindexes where name = 'nc1MSdistribution_history' and id=object_id('MSdistribution_history'))
begin
drop index MSdistribution_history.nc1MSdistribution_history
end
END

exec sys.sp_MScreate_common_dist_tables @subside = 0

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSsnapshot_agents' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSsnapshot_agents', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSsnapshot_agents
(
id int IDENTITY NOT NULL,
name nvarchar(100) NOT NULL,
publisher_id smallint NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
publication_type int NOT NULL, -- 0 transactional 1 snapshot 2 merge
local_job bit NOT NULL,
job_id binary(16) NULL,
profile_id int NOT NULL,
dynamic_filter_login sysname NULL, -- used only for merge dynamic snapshot. Should be null otherwise
dynamic_filter_hostname sysname NULL, -- used only for merge dynamic snapshot. Should be null otherwise
publisher_security_mode int NULL,
publisher_login sysname NULL,
publisher_password nvarchar(524) NULL,
job_step_uid uniqueidentifier NULL
)

exec dbo.sp_MS_marksystemobject 'MSsnapshot_agents'

raiserror('Creating clustered index ucMSsnapshot_agents', 0,1)
CREATE CLUSTERED INDEX ucMSsnapshot_agents ON dbo.MSsnapshot_agents
(publication, publisher_db, publisher_id)

raiserror('Creatingindex iMSsnapshot_agents', 0,1)
CREATE UNIQUE INDEX iMSsnapshot_agents ON dbo.MSsnapshot_agents
(id)
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'dynamic_filter_login'
and object_id=object_id('MSsnapshot_agents'))
BEGIN
ALTER TABLE MSsnapshot_agents ADD dynamic_filter_login sysname NULL
END

IF NOT EXISTS (select * from sys.columns
where name = 'dynamic_filter_hostname'
and object_id=object_id('MSsnapshot_agents'))
BEGIN
ALTER TABLE MSsnapshot_agents ADD dynamic_filter_hostname sysname NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'publisher_security_mode')
BEGIN
ALTER TABLE MSsnapshot_agents ADD publisher_security_mode int NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'publisher_login')
BEGIN
ALTER TABLE MSsnapshot_agents ADD publisher_login sysname NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'publisher_password')
BEGIN
ALTER TABLE MSsnapshot_agents ADD publisher_password nvarchar(524) NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSsnapshot_agents') and name = 'job_step_uid')
BEGIN
ALTER TABLE MSsnapshot_agents ADD job_step_uid uniqueidentifier NULL
END
END


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSlogreader_agents' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSlogreader_agents', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSlogreader_agents
(
id int IDENTITY NOT NULL,
name nvarchar(100) NOT NULL,
publisher_id smallint NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,-- Not used for SQL Server publisher
local_job bit NOT NULL,
job_id binary(16) NULL,
profile_id int NOT NULL,
publisher_security_mode smallint NULL,
publisher_login sysname NULL,
publisher_password nvarchar(524) NULL,
job_step_uid uniqueidentifier NULL
)

exec dbo.sp_MS_marksystemobject 'MSlogreader_agents'

raiserror('Creating clustered index ucMSlogreader_agents', 0,1)
CREATE CLUSTERED INDEX ucMSlogreader_agents ON dbo.MSlogreader_agents
(publisher_db, publisher_id)

raiserror('Creatingindex iMSlogreader_agents', 0,1)
CREATE UNIQUE INDEX iMSlogreader_agents ON dbo.MSlogreader_agents
(id)
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'publisher_security_mode')
BEGIN
ALTER TABLE MSlogreader_agents ADD publisher_security_mode smallint NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'publisher_login')
BEGIN
ALTER TABLE MSlogreader_agents ADD publisher_login sysname NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'publisher_password')
BEGIN
ALTER TABLE MSlogreader_agents ADD publisher_password nvarchar(524) NULL
END

IF NOT EXISTS (select * from sys.columns where object_id=object_id('MSlogreader_agents') and name = 'job_step_uid')
BEGIN
ALTER TABLE MSlogreader_agents ADD job_step_uid uniqueidentifier NULL
END
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSdistribution_agents' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSdistribution_agents', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSdistribution_agents
(
id int IDENTITY NOT NULL,
name nvarchar(100) NOT NULL,
publisher_database_id int NOT NULL,
publisher_id smallint NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
subscriber_id smallint NULL,
subscriber_db sysname NULL,
subscription_type int NOT NULL,
local_job bit NULL,
job_id binary(16) NULL,
subscription_guid binary(16) NOT NULL,
profile_id int NOT NULL,
anonymous_subid uniqueidentifier NULL,
subscriber_name sysname NULL,
virtual_agent_id int NULL,
anonymous_agent_id int NULL,
creation_date datetime default (getdate()) not NULL,
queue_id sysname null,
queue_status int default 0 not null,
offload_enabled bit default 0 NOT NULL,
offload_server sysname NULL,
dts_package_name sysname NULL,
dts_package_password nvarchar(524) NULL,
dts_package_location int default 0 not null,
sid varbinary(85) default suser_sid() not null,
queue_server sysname NULL,
-- used for subscription based security
subscriber_security_mode smallint NULL,
subscriber_login sysname NULL,
subscriber_password nvarchar(524) NULL,
reset_partial_snapshot_progress bit default 0 not null,
job_step_uid uniqueidentifier NULL
,subscriptionstreams tinyint NULL
,subscriber_type tinyint NULL
,subscriber_provider sysname NULL
,subscriber_datasrc nvarchar(4000) NULL
,subscriber_location nvarchar(4000) NULL
,subscriber_provider_string nvarchar(4000) NULL
,subscriber_catalog sysname NULL
)

exec dbo.sp_MS_marksystemobject 'MSdistribution_agents'

raiserror('Creating clustered index ucMSdistribution_agents', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSdistribution_agents ON dbo.MSdistribution_agents
(id)

raiserror('Creatingindex iMSdistribution_agents', 0,1)
CREATE INDEX iMSdistribution_agents ON dbo.MSdistribution_agents
(publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)
END

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'creation_date')
begin
alter table MSdistribution_agents add creation_date datetime default (getdate()) not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'offload_enabled')
begin
alter table MSdistribution_agents add offload_enabled bit default 0 not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'offload_server')
begin
alter table MSdistribution_agents add offload_server sysname null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'queue_id')
begin
alter table MSdistribution_agents add queue_id sysname null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'queue_status')
begin
alter table MSdistribution_agents add queue_status int default 0 not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'dts_package_name')
begin
alter table MSdistribution_agents add dts_package_name sysname null
end
if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'dts_package_password')
begin
alter table MSdistribution_agents add dts_package_password nvarchar(524) null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'dts_package_location')
begin
alter table MSdistribution_agents add dts_package_location int default 0 not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'sid')
begin
-- set sid to be the upgrade user. db_owner or sysadmin
-- can drop the agent entry
alter table MSdistribution_agents add sid varbinary(85) default suser_sid() not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'queue_server')
begin
alter table MSdistribution_agents add queue_server sysname null
EXEC sys.sp_MSupdate_mqserver_distdb
end

-- used for subscription based security
if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_security_mode')
begin
alter table MSdistribution_agents add subscriber_security_mode smallint null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_login')
begin
alter table MSdistribution_agents add subscriber_login sysname null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_password')
begin
alter table MSdistribution_agents add subscriber_password nvarchar(524) null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'reset_partial_snapshot_progress')
begin
ALTER TABLE MSdistribution_agents ADD reset_partial_snapshot_progress bit default 0 not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'job_step_uid')
begin
ALTER TABLE MSdistribution_agents ADD job_step_uid uniqueidentifier NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriptionstreams')
begin
ALTER TABLE MSdistribution_agents ADD subscriptionstreams tinyint NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_type')
begin
ALTER TABLE MSdistribution_agents ADD subscriber_type tinyint NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_provider')
begin
ALTER TABLE MSdistribution_agents ADD subscriber_provider sysname NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_datasrc')
begin
ALTER TABLE MSdistribution_agents ADD subscriber_datasrc nvarchar(4000) NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_location')
begin
ALTER TABLE MSdistribution_agents ADD subscriber_location nvarchar(4000) NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_provider_string')
begin
ALTER TABLE MSdistribution_agents ADD subscriber_provider_string nvarchar(4000) NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSdistribution_agents') and
name = 'subscriber_catalog')
begin
ALTER TABLE MSdistribution_agents ADD subscriber_catalog sysname NULL
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_agents' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSmerge_agents', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSmerge_agents
(
id int IDENTITY NOT NULL,
name nvarchar(100) NOT NULL,
publisher_id smallint NOT NULL,
publisher_db sysname NOT NULL,
publication sysname NOT NULL,
subscriber_id smallint NULL,
subscriber_db sysname NULL,
local_job bit NULL,
job_id binary(16) NULL,
profile_id int NULL,
anonymous_subid uniqueidentifier NULL,
subscriber_name sysname NULL,
creation_date datetime default (getdate()) not NULL,
offload_enabled bit default 0 NOT NULL,
offload_server sysname NULL,
sid varbinary(85) default suser_sid() not null,
-- used for subscription based security
subscriber_security_mode smallint NULL,
subscriber_login sysname NULL,
subscriber_password nvarchar(524) NULL,
publisher_security_mode smallint NULL,
publisher_login sysname NULL,
publisher_password nvarchar(524) NULL,
job_step_uid uniqueidentifier NULL
)

exec dbo.sp_MS_marksystemobject 'MSmerge_agents'

raiserror('Creating clustered index ucMSmerge_agents', 0,1)

CREATE UNIQUE clustered INDEX ucMSmerge_agents ON dbo.MSmerge_agents(id)

CREATE INDEX iMSmerge_agents ON dbo.MSmerge_agents
(publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)

CREATE INDEX i2MSmerge_agents ON dbo.MSmerge_agents
(subscriber_id, subscriber_db)

END
else
begin
if EXISTS (select * from sys.indexes where name='ucMSmerge_agents' and object_id=object_id('MSmerge_agents'))
begin
drop index MSmerge_agents.ucMSmerge_agents
end

if EXISTS (select * from sys.indexes where name='iMSmerge_agents' and object_id=object_id('MSmerge_agents'))
begin
drop index MSmerge_agents.iMSmerge_agents
end

if EXISTS (select * from sys.indexes where name='i2MSmerge_agents' and object_id=object_id('MSmerge_agents'))
begin
drop index MSmerge_agents.i2MSmerge_agents
end

CREATE UNIQUE clustered INDEX ucMSmerge_agents ON dbo.MSmerge_agents(id)

CREATE INDEX iMSmerge_agents ON dbo.MSmerge_agents
(publication, publisher_db, publisher_id, subscriber_id, subscriber_db, anonymous_subid)

CREATE INDEX i2MSmerge_agents ON dbo.MSmerge_agents
(subscriber_id, subscriber_db)
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'creation_date')
begin
alter table MSmerge_agents add creation_date datetime default (getdate()) not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'offload_enabled')
begin
alter table MSmerge_agents add offload_enabled bit default 0 not null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'offload_server')
begin
alter table MSmerge_agents add offload_server sysname null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'sid')
begin
-- set sid to be the upgrade user. db_owner or sysadmin
-- can drop the agent entry
alter table MSmerge_agents add sid varbinary(85) default suser_sid() not null
end

-- used for subscription based security
if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'subscriber_security_mode')
begin
alter table MSmerge_agents add subscriber_security_mode smallint null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'subscriber_login')
begin
alter table MSmerge_agents add subscriber_login sysname null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'subscriber_password')
begin
alter table MSmerge_agents add subscriber_password nvarchar(524) null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'publisher_security_mode')
begin
alter table MSmerge_agents add publisher_security_mode smallint null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'publisher_login')
begin
alter table MSmerge_agents add publisher_login sysname null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'publisher_password')
begin
alter table MSmerge_agents add publisher_password nvarchar(524) null
end

if not exists (select * from sys.columns where
object_id = object_id('MSmerge_agents') and
name = 'job_step_uid')
begin
alter table MSmerge_agents add job_step_uid uniqueidentifier NULL
end

-- Need to re_visit the indexing of this table
if not exists (select * from sys.objects where name = 'MSrepl_identity_range')
begin
raiserror('Creating table MSrepl_identity_range',0,1)

create table dbo.MSrepl_identity_range (
publisher sysname not NULL,
publisher_db sysname not NULL,
tablename sysname not NULL,
identity_support int NULL,
next_seed bigint NULL, -- resource control
pub_range bigint NULL, -- publisher range
range bigint NULL, -- set by sp_addmergearticle
max_identity bigint NULL, -- resource control
threshold int NULL, -- in percentage, set by sp_addmergearticle
current_max bigint NULL, -- max value for current check constraint,set by sp_addmergearticle
constraint pkMSrepl_identity_range
primary key(publisher,publisher_db,tablename)
)
exec dbo.sp_MS_marksystemobject MSrepl_identity_range
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSpublication_access' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSpublication_access', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSpublication_access
(
publication_id int NULL, -- Publication_id is unique in distribution database.
login sysname NOT NULL, -- Logins in the publication access list, they nust
-- exist at both publisher and distributor side.
sid varbinary(85) NULL
)

exec dbo.sp_MS_marksystemobject 'MSpublication_access'

raiserror('Creating clustered index ucMSpublication_access', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSpublication_access ON dbo.MSpublication_access
(publication_id, sid)
END

-- For b3 upgrade
if NOT EXISTS (select * from sys.columns where name='retention' and object_id=object_id('MSpublications'))
begin
alter table MSpublications add retention int NULL
UPDATE msdb..MSdistributiondbs set max_distretention=72 where
name = db_name() collate database_default
end

if NOT EXISTS (select * from sys.columns where name='retention_period_unit' and object_id=object_id('MSpublications'))
begin
alter table MSpublications add retention_period_unit tinyint default 0 not null
end

-- drop default_access column
if exists (select * from sys.columns where object_id = object_id('MSpublications') and
name = 'default_access')
begin
alter table MSpublications drop column default_access
end

IF NOT EXISTS (SELECT * FROM sys.columns WHERE name='sid' and object_id=object_id('MSpublication_access'))
BEGIN
ALTER TABLE dbo.MSpublication_access ADD sid varbinary(85) NULL

EXEC('UPDATE MSpublication_access SET sid = SUSER_SID(login,0)')
END

-- Drop publisher_id column
if EXISTS (select * from sys.columns where name='publisher_id' and object_id=object_id('MSpublication_access'))
begin
drop index MSpublication_access.ucMSpublication_access

alter table MSpublication_access drop column publisher_id

raiserror('Creating clustered index ucMSpublication_access', 0,1)
CREATE CLUSTERED INDEX ucMSpublication_access ON dbo.MSpublication_access
(publication_id, sid)
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSqreader_agents' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSqreader_agents', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSqreader_agents
(
id int IDENTITY NOT NULL,
name nvarchar(100) NULL,
job_id binary(16) NULL,
profile_id int NULL,
job_step_uid uniqueidentifier NULL
)

exec dbo.sp_MS_marksystemobject 'MSqreader_agents'

raiserror('Creating unique index ucMSqreader_agents', 0,1)
CREATE UNIQUE INDEX ucMSqreader_agents ON dbo.MSqreader_agents
(id)
END

-- add columns for existing table
if not exists (select * from sys.columns where
object_id = object_id('MSqreader_agents') and
name = 'profile_id')
begin
alter table dbo.MSqreader_agents add profile_id int NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSqreader_agents') and
name = 'job_step_uid')
begin
alter table dbo.MSqreader_agents add job_step_uid uniqueidentifier NULL
end


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSqreader_history' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSqreader_history', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSqreader_history
(
agent_id int NOT NULL,
publication_id int NULL,
runstatus int NOT NULL,
start_time datetime NOT NULL,
time datetime NOT NULL,
duration int NOT NULL,
comments nvarchar(1000) NOT NULL,
transaction_id nvarchar(40) NULL,
transaction_status int NULL,
transactions_processed int NULL DEFAULT 0,
commands_processed int NULL DEFAULT 0,
delivery_rate float NOT NULL DEFAULT 0.0,
transaction_rate float NOT NULL DEFAULT 0.0,
subscriber sysname NULL,
subscriberdb sysname NULL,
error_id int NULL,
timestamp NOT NULL
)

exec dbo.sp_MS_marksystemobject 'MSqreader_history'

raiserror('Creating clustered index ucMSqreader_history', 0,1)
CREATE CLUSTERED INDEX ucMSqreader_history ON dbo.MSqreader_history
(agent_id, timestamp, runstatus, start_time, time)
END
ELSE
BEGIN
if exists (select * from sys.columns where name = 'comments' and object_id=object_id('MSqreader_history'))
begin
alter table MSqreader_history alter column comments nvarchar(1000) NOT NULL
end
END

-- alter column publication_id
if not exists (select * from sys.columns where
object_id = object_id('MSqreader_history') and
name = 'publication_id')
begin
alter table dbo.MSqreader_history alter column publication_id int NULL
end

-- add columns for existing table
if not exists (select * from sys.columns where
object_id = object_id('MSqreader_history') and
name = 'error_id')
begin
alter table dbo.MSqreader_history add error_id int NULL
end

if not exists (select * from sys.columns where
object_id = object_id('MSqreader_history') and
name = 'transactions_processed')
begin
alter table dbo.MSqreader_history add transactions_processed int NULL DEFAULT 0
end

if not exists (select * from sys.columns where
object_id = object_id('MSqreader_history') and
name = 'delivery_rate')
begin
alter table dbo.MSqreader_history add delivery_rate float NOT NULL DEFAULT 0.0
end

if not exists (select * from sys.columns where
object_id = object_id('MSqreader_history') and
name = 'transaction_rate')
begin
alter table dbo.MSqreader_history add transaction_rate float NOT NULL DEFAULT 0.0
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSrepl_backup_lsns' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSrepl_backup_lsns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

CREATE TABLE dbo.MSrepl_backup_lsns
(
publisher_database_id int NOT NULL,
valid_xact_id varbinary(16) NULL,
valid_xact_seqno varbinary (16 ) NULL,
next_xact_id varbinary(16) NULL,
next_xact_seqno varbinary (16 ) NULL
)

exec dbo.sp_MS_marksystemobject 'MSrepl_backup_lsns'

raiserror('Creating clustered index ucMSrepl_backup_lsns', 0,1)
CREATE UNIQUE CLUSTERED INDEX ucMSrepl_backup_lsns ON dbo.MSrepl_backup_lsns
(publisher_database_id)

if exists (select * from MSpublisher_databases)
begin
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Upgrading MSrepl_backup_lsns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
insert into MSrepl_backup_lsns select d.id,
NULL, NULL, NULL, NULL from MSpublisher_databases d
-- No need to set the lsns in the table since the 'sync with backup' option is
-- new in 8.0
end

END

--
-- Table for replication monitor thresholds for publications
--
if (object_id('dbo.MSpublicationthresholds') is null)
begin
raiserror('Creating table MSpublicationthresholds', 0,1)
create table dbo.MSpublicationthresholds
(
publication_id int not null
,metric_id int not null
,value sql_variant null
,shouldalert bit not null default 0
,isenabled bit not null default 0
)
exec dbo.sp_MS_marksystemobject 'MSpublicationthresholds'
raiserror('Creating clustered index ucmspublicationthresholds', 0,1)
create unique clustered index ucmspublicationthresholds
on dbo.MSpublicationthresholds (publication_id, metric_id)
create nonclustered index nc1mspublicationthresholds
on dbo.MSpublicationthresholds (publication_id)
create nonclustered index nc2mspublicationthresholds
on dbo.MSpublicationthresholds (metric_id)
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishers' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublishers', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublishers
(
publisher_id smallint not null, -- use same ID as dbo.sysservers.srvid
vendor sysname not null,
publisher_guid uniqueidentifier not null,
flush_request_time datetime null,
version sysname null
-- ,CONSTRAINT pk_IHpublishers PRIMARY KEY (publisher_id)
)

exec dbo.sp_MS_marksystemobject 'IHpublishers'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishertables' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublishertables', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublishertables
(
table_id int IDENTITY NOT NULL
,publisher_id smallint NOT NULL
,name sysname NOT NULL
,owner sysname NOT NULL
-- ,CONSTRAINT pk_IHpublishertables PRIMARY KEY (table_id, publisher_id)
-- ,CONSTRAINT fk_IHpublishertables_publisher FOREIGN KEY (publisher_id) REFERENCES IHpublishers (publisher_id)
-- ,CONSTRAINT uk_IHpublishertables_name UNIQUE NONCLUSTERED (publisher_id, name, owner)
)

create unique clustered index idx_IHpublishertables_tableid ON IHpublishertables (table_id)
create index idx_IHpublishertables_name ON IHpublishertables (name)

exec dbo.sp_MS_marksystemobject 'IHpublishertables'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHarticles' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHarticles', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHarticles
(
article_id int NOT NULL IDENTITY,
name sysname NOT NULL,
publication_id smallint NOT NULL,
table_id int NOT NULL,
publisher_id smallint NOT NULL,
creation_script nvarchar(255) NULL,
del_cmd nvarchar(255) NULL,
filter int NOT NULL,
filter_clause ntext NULL,
ins_cmd nvarchar(255) NULL,
pre_creation_cmd tinyint NOT NULL,
status tinyint NOT NULL,
type tinyint NOT NULL,
upd_cmd nvarchar(255) NULL,
schema_option binary(8) NULL,
dest_owner sysname NULL,
dest_table sysname NOT NULL,
tablespace_name nvarchar(255) NULL,
objid int NULL,
sync_objid int NULL,
description nvarchar(255) NULL,
publisher_status int NULL,
article_view_owner nvarchar(255) NULL,
article_view nvarchar(255) NULL,
ins_scripting_proc int NULL,
del_scripting_proc int NULL,
upd_scripting_proc int NULL,
custom_script nvarchar(2048) NULL,
fire_triggers_on_snapshot bit NOT NULL DEFAULT 0,
instance_id int NOT NULL DEFAULT 0,
use_default_datatypes bit NOT NULL DEFAULT 0
-- ,CONSTRAINT pk_IHarticles PRIMARY KEY (article_id)
-- ,CONSTRAINT uk_IHarticles_article UNIQUE (name, publication_id)
-- ,CONSTRAINT fk_IHarticles_tableid FOREIGN KEY (table_id, publisher_id) REFERENCES IHpublishertables (table_id, publisher_id)
)

create unique clustered index idx_IHarticles_articleid ON IHarticles (article_id)
create index idx_IHarticles_tableid ON IHarticles (table_id)
create index idx_IHarticles_name ON IHarticles (name)

exec dbo.sp_MS_marksystemobject 'IHarticles'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishercolumns' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublishercolumns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublishercolumns
(
publishercolumn_id int IDENTITY
,table_id int not null
,publisher_id smallint not null
,name sysname not null
,column_ordinal int not null
,type varchar(255) not null
,length bigint not null
,prec int null
,scale int null
,isnullable bit not null
,iscaptured bit not null -- Column is begin captured by tracking trigger but might not be in any articles
-- ,CONSTRAINT pk_IHpublishercolumns PRIMARY KEY (publishercolumn_id)
-- ,CONSTRAINT fk_IHpublishercolumns_tableid FOREIGN KEY (table_id, publisher_id) REFERENCES IHpublishertables (table_id, publisher_id)
)

create index idx_IHpublishercolumns_tableid on IHpublishercolumns (table_id)
create index idx_IHpublishercolumns_name on IHpublishercolumns (name)
create index idx_IHpublishercolumns_type on IHpublishercolumns (type)
create index idx_IHpublishercolumns_pubcolumnid on IHpublishercolumns (publishercolumn_id)

exec dbo.sp_MS_marksystemobject 'IHpublishercolumns'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHcolumns' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHcolumns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHcolumns
(
column_id int IDENTITY
,publishercolumn_id int not null
,name sysname not null
,article_id int not null
,column_ordinal int not null
,mapped_type tinyint not null
,mapped_length bigint null
,mapped_prec int null
,mapped_scale int null
,mapped_nullable bit null default 1
-- ,CONSTRAINT pk_IHcolumns PRIMARY KEY (column_id)
-- ,CONSTRAINT uk_IHcolumns_pubcolid UNIQUE (publishercolumn_id, article_id)
-- ,CONSTRAINT uk_IHcolumns_name UNIQUE (name, article_id)
-- ,CONSTRAINT fk_IHcolumns_publishrecolumnid FOREIGN KEY(publishercolumn_id) REFERENCES IHpublishercolumns (publishercolumn_id)
-- ,CONSTRAINT fk_IHcolumns_articleid FOREIGN KEY (article_id) REFERENCES IHarticles (article_id)
)

create clustered index idx_IHcolumns_pubcolumnid ON IHcolumns (publishercolumn_id)
create index idx_IHcolumns_mappedtype ON IHcolumns (mapped_type)
create index idx_IHcolumns_articleid ON IHcolumns (article_id)
create index idx_IHcolumns_columnid ON IHcolumns (column_id)
create index idx_IHcolumns_name ON IHcolumns (name)

exec dbo.sp_MS_marksystemobject 'IHcolumns'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHindextypes' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHindextypes', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHindextypes
(
type NVARCHAR(255) NOT NULL
-- ,CONSTRAINT pk_IHindextypes PRIMARY KEY (type)
)

insert into IHindextypes (type) values ('UNIQUE');
insert into IHindextypes (type) values ('NONUNIQUE');

exec dbo.sp_MS_marksystemobject 'IHindextypes'
END


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublisherindexes' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublisherindexes', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublisherindexes
(
publisherindex_id INT IDENTITY NOT NULL
,table_id INT NOT NULL
,publisher_id SMALLINT NOT NULL
,name NVARCHAR(255) NOT NULL
,type NVARCHAR(255) NOT NULL
-- ,CONSTRAINT pk_IHindexes PRIMARY KEY (publisherindex_id)
-- ,CONSTRAINT fk_IHindexes_indextypeid FOREIGN KEY (type) REFERENCES IHindextypes (type)
-- ,CONSTRAINT fk_IHindexes_tableid FOREIGN KEY (table_id, publisher_id) REFERENCES IHpublishertables (table_id, publisher_id)
)

create unique clustered index idx_IHpublisherindexes_pubindexid ON IHpublisherindexes (publisherindex_id)
create index idx_IHpublisherindexes_tableid ON IHpublisherindexes (table_id)
create index idx_IHpublisherindexes_name ON IHpublisherindexes (name)

exec dbo.sp_MS_marksystemobject 'IHpublisherindexes'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishercolumnindexes' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublishercolumnindexes', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublishercolumnindexes
(
publishercolumn_id int NOT NULL,
publisherindex_id int NOT NULL,
indid int NOT NULL
-- ,CONSTRAINT pk_IHpublishercolumnindexes PRIMARY KEY (publishercolumn_id, publisherindex_id)
-- ,CONSTRAINT fk_IHpublishercolumnindexes_columnid FOREIGN KEY (publishercolumn_id) REFERENCES IHpublishercolumns (publishercolumn_id)
-- ,CONSTRAINT fk_IHpublishercolumnindexes_constraintid FOREIGN KEY (publisherindex_id) REFERENCES IHpublisherindexes (publisherindex_id)
)

create clustered index idx_IHpublishercolumnindexes_pubcolumnid ON IHpublishercolumnindexes (publishercolumn_id)
create index idx_IHpublishercolumnindexes_pubindexid ON IHpublishercolumnindexes (publisherindex_id)

exec dbo.sp_MS_marksystemobject 'IHpublishercolumnindexes'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublications' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublications', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublications
(
pubid int not null,
name sysname not null,
repl_freq tinyint not null,
status tinyint not null,
sync_method tinyint not null,
snapshot_jobid binary(16) null,
enabled_for_internet bit not null,
immediate_sync_ready bit not null,
allow_queued_tran bit not null default 0,
allow_sync_tran bit not null default 0,
autogen_sync_procs bit not null,
snapshot_in_defaultfolder bit not null,
alt_snapshot_folder nvarchar(510) null,
pre_snapshot_script nvarchar(510) null,
post_snapshot_script nvarchar(510) null,
compress_snapshot bit not null,
ftp_address sysname null,
ftp_port int not null,
ftp_subdirectory nvarchar(510) null,
ftp_login nvarchar(256) null,
ftp_password nvarchar(1048) null,
allow_dts bit not null default 0,
allow_anonymous bit not null default 0,
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 not NULL default 10, -- default is sphinx
description nvarchar(255) NULL,
independent_agent bit NOT NULL default 0,
immediate_sync bit NOT NULL default 0,
allow_push bit NOT NULL default 1,
allow_pull bit NOT NULL default 0,
retention int NULL,
allow_subscription_copy bit default 0 not null,
allow_initialize_from_backup bit not null default 0,
min_autonosync_lsn binary null,
replicate_ddl int null default 1,
options int null default 0,
originator_id int null
)

create index idx_IHpublications_replfreq ON IHpublications (repl_freq)
create index idx_IHpublications_name ON IHpublications (name)
create index idx_IHpublications_pubid ON IHpublications (pubid)

exec dbo.sp_MS_marksystemobject 'IHpublications'
END



IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHextendedArticleView' and type = 'V')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHextendedArticleView', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd =
' create view IHextendedArticleView as '
+ ' SELECT msa.publisher_id, '
+ ' msa.publication_id, '
+ ' msa.article, '
+ ' msa.destination_object, '
+ ' msa.source_owner, '
+ ' msa.source_object, '
+ ' msa.description, '
+ ' iha.creation_script, '
+ ' iha.del_cmd, '
+ ' iha.filter, '
+ ' iha.filter_clause, '
+ ' iha.ins_cmd, '
+ ' iha.pre_creation_cmd, '
+ ' iha.status, '
+ ' iha.type, '
+ ' iha.upd_cmd, '
+ ' iha.schema_option, '
+ ' iha.dest_owner '
+ ' FROM MSarticles msa '
+ ' JOIN IHarticles iha ON msa.article_id = iha.article_id '

EXEC (@Cmd)

exec dbo.sp_MS_marksystemobject 'IHextendedArticleView'
END


IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHconstrainttypes' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHconstrainttypes', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHconstrainttypes
(
type NVARCHAR(255) NOT NULL
-- ,CONSTRAINT pk_IHcontrainttypes PRIMARY KEY (type)
)

insert into IHconstrainttypes (type) values ('PRIMARYKEY')

exec dbo.sp_MS_marksystemobject 'IHconstrainttypes'
END



IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublisherconstraints' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublisherconstraints', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublisherconstraints
(
publisherconstraint_id int IDENTITY NOT NULL
,table_id int NOT NULL -- denormalized
,publisher_id smallint NOT NULL -- denormalized
,name sysname NOT NULL
,type NVARCHAR(255) NOT NULL
-- ,CONSTRAINT pk_IHpublisherconstraints PRIMARY KEY (publisherconstraint_id)
-- ,CONSTRAINT uk_IHpublisherconstriaints_name UNIQUE (name, table_id)
-- ,CONSTRAINT fk_IHpublisherconstraints_constraint FOREIGN KEY (type) REFERENCES IHconstrainttypes (type)
-- ,CONSTRAINT fk_IHpublisherconstraints_tableid FOREIGN KEY (table_id, publisher_id) REFERENCES IHpublishertables (table_id, publisher_id)
)
create unique clustered index idx_IHpublisherconstraints_pubconstraintid ON IHpublisherconstraints (publisherconstraint_id)
create index idx_IHpublisherconstraints_tableid ON IHpublisherconstraints (table_id)
create index idx_IHpublisherconstraints_name ON IHpublisherconstraints (name)

exec dbo.sp_MS_marksystemobject 'IHpublisherconstraints'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHpublishercolumnconstraints' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHpublishercolumnconstraints', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHpublishercolumnconstraints
(
publishercolumn_id int NOT NULL,
publisherconstraint_id int NOT NULL,
indid int NOT NULL
)

create clustered index idx_IHpublishercolumnconstraints_pubcolumnid ON IHpublishercolumnconstraints (publishercolumn_id)
create index idx_IHpublishercolumnconstraints_pubconstraintid ON IHpublishercolumnconstraints (publisherconstraint_id)

exec dbo.sp_MS_marksystemobject 'IHpublishercolumnconstraints'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHsubscriptions' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table IHsubscriptions', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table IHsubscriptions
(
article_id int NOT NULL,
srvid smallint NOT NULL,
dest_db sysname NOT NULL,
login_name sysname NOT NULL,
distribution_jobid binary(16) NOT NULL,
timestamp timestamp NULL,
queued_reinit bit NOT NULL DEFAULT 0,
status tinyint NOT NULL DEFAULT 0,
sync_type tinyint NOT NULL DEFAULT 0,
subscription_type int NOT NULL DEFAULT 0,
update_mode tinyint NOT NULL DEFAULT 0, -- 0 (read only),
loopback_detection bit NOT NULL DEFAULT 0,
nosync_type tinyint NOT NULL DEFAULT 0
,srvname sysname NOT NULL DEFAULT ''
)
create unique clustered index [idx_IHsubscriptions_article_id] ON [dbo].[IHsubscriptions]
(
[article_id] ASC,
[srvid] ASC,
[dest_db] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF ) ON [PRIMARY]

exec dbo.sp_MS_marksystemobject 'IHsubscriptions'
END
ELSE
BEGIN
IF NOT EXISTS (select * from sys.columns
where name = 'srvname'
and object_id=object_id('IHsubscriptions'))
BEGIN
ALTER TABLE IHsubscriptions ADD srvname sysname NOT NULL DEFAULT N'' with values
exec('update dbo.IHsubscriptions
set srvname = upper(ss.srvname collate database_default)
from dbo.IHsubscriptions sub
join master.dbo.sysservers ss on ss.srvid = sub.srvid'
)
END
ELSE
BEGIN
exec ('update dbo.IHsubscriptions
set srvname = upper(ss.srvname collate database_default)
from dbo.IHsubscriptions sub
join master.dbo.sysservers ss on ss.srvid = sub.srvid
where sub.srvname = N'
''' ')
END
END

IF not exists (select * from sys.objects where name = 'sysschemaarticles' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table sysschemaarticles', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create table 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
)

CREATE UNIQUE CLUSTERED INDEX [c1sysschemaarticles] ON [dbo].[sysschemaarticles]
(
[artid] ASC,
[pubid] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

exec dbo.sp_MS_marksystemobject 'sysschemaarticles'
END

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- Create the cache table for PeerToPeer replication
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MScached_peer_lsns' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MScached_peer_lsns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
CREATE TABLE dbo.MScached_peer_lsns
(
agent_id int null,
originator sysname,
originator_db sysname,
originator_publication_id int null,
originator_db_version int null,
originator_lsn varbinary(16) null
)

CREATE UNIQUE CLUSTERED INDEX ucMScached_peer_lsns
ON dbo.MScached_peer_lsns (agent_id, originator_db_version, originator_lsn, originator_db, originator, originator_publication_id)

exec dbo.sp_MS_marksystemobject 'dbo.MScached_peer_lsns'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHextendedSubscriptionView' and type = 'V')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating view IHextendedSubscriptionView', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd =
' create view IHextendedSubscriptionView as '
+ ' SELECT '
+ ' ihs.article_id, '
+ ' ihs.dest_db, '
+ ' ihs.srvid, '
+ ' ihs.login_name, '
+ ' ihs.distribution_jobid, '
+ ' mss.publisher_database_id, '
+ ' mss.subscription_type, '
+ ' mss.sync_type, '
+ ' mss.status, '
+ ' mss.snapshot_seqno_flag, '
+ ' mss.independent_agent, '
+ ' mss.subscription_time, '
+ ' mss.loopback_detection, '
+ ' mss.agent_id, '
+ ' mss.update_mode, '
+ ' mss.publisher_seqno, '
+ ' mss.ss_cplt_seqno '
+ ' FROM IHsubscriptions ihs '
+ ' JOIN MSsubscriptions mss ON ihs.article_id = mss.article_id '
+ ' and ihs.srvid = mss.subscriber_id '
+ ' and ihs.dest_db = mss.subscriber_db '

EXEC (@Cmd)

exec dbo.sp_MS_marksystemobject 'IHextendedSubscriptionView'
END

DECLARE @Cmd1 varchar(2000)
IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'syssubscriptions' and type = 'V')
or
(EXISTS (SELECT * from sys.objects WHERE name = 'syssubscriptions' and type = 'V')
and NOT EXISTS (select * from sys.columns where name = 'srvname' and object_id=object_id('syssubscriptions')))
BEGIN
IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'syssubscriptions' and type = 'V')
begin
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating view syssubscriptions', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd1 = ' create '
end
else if NOT EXISTS (select * from sys.columns where name = 'srvname' and object_id=object_id('syssubscriptions'))
begin
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Altering view syssubscriptions to include srvname column', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd1 = ' alter '
end
select @Cmd1 = @Cmd1 + 'view dbo.syssubscriptions (artid, srvid, dest_db, status, sync_type, login_name, subscription_type, '
+ ' distribution_jobid, timestamp, update_mode, loopback_detection, queued_reinit, nosync_type, srvname) AS '
+ ' SELECT ihsub.article_id, '
+ ' ihsub.srvid, '
+ ' ihsub.dest_db, '
+ ' ihsub.status, '
+ ' ihsub.sync_type, '
+ ' ihsub.login_name, '
+ ' ihsub.subscription_type, '
+ ' ihsub.distribution_jobid, '
+ ' ihsub.timestamp, '
+ ' ihsub.update_mode, '
+ ' ihsub.loopback_detection, '
+ ' ihsub.queued_reinit, '
+ ' ihsub.nosync_type '
+ ' ,ihsub.srvname '
+ ' FROM dbo.IHsubscriptions ihsub '

EXEC (@Cmd1)

exec dbo.sp_MS_marksystemobject 'syssubscriptions'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'syspublications' and type = 'V')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating view syspublications', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd1 =
' create view syspublications (description, name, pubid, repl_freq, status, sync_method, snapshot_jobid, '
+ ' independent_agent, immediate_sync, enabled_for_internet, allow_push, allow_pull, allow_anonymous, immediate_sync_ready, '
+ ' allow_sync_tran, autogen_sync_procs, retention, allow_queued_tran, snapshot_in_defaultfolder, alt_snapshot_folder, '
+ ' pre_snapshot_script, post_snapshot_script, compress_snapshot, ftp_address, ftp_port, ftp_subdirectory, ftp_login, '
+ ' ftp_password, allow_dts, allow_subscription_copy, centralized_conflicts, conflict_retention, conflict_policy, queue_type, '
+ ' ad_guidname, backward_comp_level, allow_initialize_from_backup, min_autonosync_lsn, replicate_ddl, options, originator_id) AS '
+ ' SELECT ihpub.description, '
+ ' ihpub.name, '
+ ' ihpub.pubid, '
+ ' ihpub.repl_freq, '
+ ' ihpub.status, '
+ ' ihpub.sync_method, '
+ ' ihpub.snapshot_jobid, '
+ ' ihpub.independent_agent, '
+ ' ihpub.immediate_sync, '
+ ' ihpub.enabled_for_internet, '
+ ' ihpub.allow_push, '
+ ' ihpub.allow_pull, '
+ ' ihpub.allow_anonymous, '
+ ' ihpub.immediate_sync_ready, '
+ ' ihpub.allow_sync_tran, '
+ ' ihpub.autogen_sync_procs, '
+ ' ihpub.retention, '
+ ' ihpub.allow_queued_tran, '
+ ' ihpub.snapshot_in_defaultfolder, '
+ ' ihpub.alt_snapshot_folder, '
+ ' ihpub.pre_snapshot_script, '
+ ' ihpub.post_snapshot_script, '
+ ' ihpub.compress_snapshot, '
+ ' ihpub.ftp_address, '
+ ' ihpub.ftp_port, '
+ ' ihpub.ftp_subdirectory, '
+ ' ihpub.ftp_login, '
+ ' ihpub.ftp_password, '
+ ' ihpub.allow_dts, '
+ ' ihpub.allow_subscription_copy, '
+ ' ihpub.centralized_conflicts, '
+ ' ihpub.conflict_retention, '
+ ' ihpub.conflict_policy, '
+ ' ihpub.queue_type,'
+ ' ihpub.ad_guidname, '
+ ' ihpub.backward_comp_level, '
+ ' ihpub.allow_initialize_from_backup, '
+ ' ihpub.min_autonosync_lsn, '
+ ' ihpub.replicate_ddl,'
+ ' ihpub.options,'
+ ' ihpub.originator_id'
+ ' FROM IHpublications ihpub '

EXEC (@Cmd1)

exec dbo.sp_MS_marksystemobject 'syspublications'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'sysarticles' and type = 'V')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating view sysarticles', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd1 =
' create view sysarticles (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) AS '
+ ' SELECT ihart.article_id, ihart.creation_script, ihart.del_cmd, ihart.description, '
+ 'ihart.dest_table, ihart.filter, ihart.filter_clause, ihart.ins_cmd, '
+ 'ihart.name, ihart.objid, ihart.publication_id, ihart.pre_creation_cmd, '
+ 'ihart.status, ihart.sync_objid, ihart.type, ihart.upd_cmd, ihart.schema_option, '
+ 'ihart.dest_owner, ihart.ins_scripting_proc, ihart.del_scripting_proc, ihart.upd_scripting_proc, ihart.custom_script, ihart.fire_triggers_on_snapshot '
+ 'FROM IHarticles ihart '

EXEC (@Cmd1)

exec dbo.sp_MS_marksystemobject 'sysarticles'
END

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'sysarticlecolumns' and type = 'V')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating view sysarticlecolumns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd1 = 'create view sysarticlecolumns (artid, colid, is_udt, is_xml, is_max ) AS SELECT article_id, publishercolumn_id, 0, 0, 0 FROM IHcolumns'
EXEC (@Cmd1)

exec dbo.sp_MS_marksystemobject 'sysarticlecolumns'
END

-- NOTE:
-- IHsyscolumns is a view of PUBLISHED columns in dbo.syscolumns format. It is NOT the
-- full list of base columns like it is in normal SQL Server dbo.syscolumns
IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'IHsyscolumns' and type = 'V')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating view IHsyscolumns', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

SELECT @Cmd1 =
' create view IHsyscolumns (name, id, xtype, typestat, xusertype, length, '
+ ' xprec, xscale, colid, xoffset, bitpos, reserved, colstat, cdefault, domain, number, colorder, autoval, '
+ ' offset, collationid, language, status, type, usertype, printfmt, prec, scale, iscomputed, isoutparam, isnullable, '
+ ' collation, tdscollation ) AS '
+ ' SELECT ihcol.name, '
+ ' ihcol.article_id, '
+ ' ihcol.mapped_type, '
+ ' NULL, '
+ ' ihcol.mapped_type, '
+ ' ihcol.mapped_length, '
+ ' ihcol.mapped_prec, '
+ ' ihcol.mapped_scale, '
+ ' ihcol.column_id, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' ihcol.column_ordinal, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' NULL, '
+ ' 0, '
+ ' ihcol.mapped_type, '
+ ' NULL, '
+ ' ihcol.mapped_prec, '
+ ' ihcol.mapped_scale, '
+ ' 0, '
+ ' 0, '
+ ' 0, '
+ ' NULL, '
+ ' NULL '
+ ' FROM IHcolumns ihcol '

EXEC (@Cmd1)

exec dbo.sp_MS_marksystemobject 'IHsyscolumns'
END

IF not exists (select * from sys.objects where name = 'sysextendedarticlesview')
BEGIN
exec ('create view dbo.sysextendedarticlesview
as
select * 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'
)

exec dbo.sp_MS_marksystemobject 'sysextendedarticlesview'

END

if object_id('MStracer_tokens') is null
begin
create table MStracer_tokens
(
tracer_id int identity(-2147483648, 1) primary key clustered,
publication_id int not null,
publisher_commit datetime not null,
distributor_commit datetime null
)
if @@error <> 0
begin
return 1
end

exec dbo.sp_MS_marksystemobject 'MStracer_tokens'
end

if object_id('MStracer_history') is null
begin
create table MStracer_history
(
parent_tracer_id int not null,
agent_id int not NULL,
subscriber_commit datetime null

constraint pkuc_tracer_subs primary key nonclustered
(
parent_tracer_id,
agent_id
)

constraint fk_MStracer_tokens_parent_tracer_id foreign key
(
parent_tracer_id
) references MStracer_tokens
(
tracer_id
)
)
if @@error <> 0
begin
return 1
end

exec dbo.sp_MS_marksystemobject 'MStracer_history'
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSmerge_articleresolver' and type = 'U')
BEGIN
create table dbo.MSmerge_articleresolver
(
article_resolver nvarchar(255) primary key not null ,
resolver_clsid nvarchar(50) not null,
is_dotnet_assembly bit null default 0,
dotnet_assembly_name nvarchar(255) null,
dotnet_class_name nvarchar(255) null
)

if @@error <> 0
return 1
exec dbo.sp_MS_marksystemobject 'MSmerge_articleresolver'

END

if object_id('dbo.MSreplication_monitordata') is null
begin
create table dbo.MSreplication_monitordata
(
-- internal use
lastrefresh datetime null
,computetime int null -- in seconds
,publication_id int null
-- static
,publisher sysname
,publisher_srvid int null
,publisher_db sysname
,publication sysname
,publication_type int null
,agent_type int null
,agent_id int null
,agent_name sysname
,job_id uniqueidentifier null
-- dynamic
,status int null
,isagentrunningnow bit null
,warning int null -- latest session's warning
,last_distsync datetime null -- last sync time
,agentstoptime datetime null -- agent stop time
,distdb sysname null
,retention int null
,time_stamp datetime null
-- tran specific
,worst_latency int null
,best_latency int null
,avg_latency int null
,cur_latency int null
-- <<merge specific>>
-- this is per subscription, depending on latest session type (fast/slow)
-- there is a subset of all sessions for this subscription with this type
-- get the value by session_speed/average
-- the following 3 are statistics of the above set, unit is percentage
,worst_runspeedPerf int null
,best_runspeedPerf int null
,average_runspeedPerf int null
-- this is mergerunspeed/average above
,mergePerformance int null
-- latest session's running duration
,mergelatestsessionrunduration int null
-- latest session's row/second raw number
,mergelatestsessionrunspeed float null
-- lastest session's running type (LAN or DUN)
,mergelatestsessionconnectiontype int null
,retention_period_unit tinyint null
--
,worst_runduration int
,best_runduration int
,average_runduration int
,cur_runduration int
,mergeexpirationthreshold int
,mergerundurationthreshold int
,mergestoppedcontinuousagentthreshold int
,mergerunspeedthreshold int
,mergerunspeed float
--
)
if (@@error != 0)
return 1
exec dbo.sp_MS_marksystemobject 'MSreplication_monitordata'
--
-- build indices
--

create nonclustered index nc2MSreplication_monitordata
on MSreplication_monitordata(publication, publisher_db)
if (@@error != 0)
return 1

create nonclustered index nc6MSreplication_monitordata
on MSreplication_monitordata(lastrefresh)
if (@@error != 0)
return 1

--
-- grant control access to replmonitor role for refresh computation
--
grant control on dbo.MSreplication_monitordata to replmonitor
end

IF NOT EXISTS (SELECT * from sys.objects WHERE name = 'MSnosyncsubsetup' and type = 'U')
BEGIN
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
raiserror('Creating table MSnosyncsubsetup', 0,1)
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- The parameterName corresponds to each parameter name
-- of stored proc sp_MSsetupnosyncsubwithlsnatdist.
create table dbo.MSnosyncsubsetup
(
publisher_database_id int,
publication_id int,
artid int,
next_valid_lsn varbinary(10),
parameterName sysname,
parameterValue nvarchar(max),
primary key (publisher_database_id,
publication_id,
artid,
next_valid_lsn,
parameterName)
)
if @@error <> 0
return 1
exec dbo.sp_MS_marksystemobject 'MSnosyncsubsetup'
END

end

Total Pageviews