sp_changearticle Replication Stored Procedure

Changes an article's properties.

Syntax

sp_changearticle publication, article [, property, value]

where

publication

Is the name of the publication that contains the article.

article

Is the name of the article whose property is to be changed.

property

States an article property to change. Can be:

name
description
sync_object
type
ins_cmd
del_cmd
upd_cmd
filter
dest_table
creation_script
pre_creation_cmd

value

Specifies the article value. Can be used with:

name

Is the new name for the article.

description

Is a descriptive entry for the publication job.

sync_object

Is the name of the table or view used to produce a synchronization output file. The default is NULL.

type

Is the article type:
1Log-based article (the default)
3Log-based article with manual filter
5Log-based article with manual view
7Log-based article with manual filter and manual view

ins_cmd

Is the INSERT statement to execute; otherwise, it will be constructed from the log.

del_cmd

Is the DELETE statement to execute; otherwise, it will be constructed from the log.

upd_cmd

Is the UPDATE statement to execute; otherwise, it will be constructed from the log.

filter

Specifies a stored procedure used to the filter the table (horizontal partition). The default is NULL.

dest_table

Is the destination table, if different from the source table (source_table).

creation_script

Is the path and name of an article schema script used to create target tables. The default is NULL.

pre_creation_cmd

Specifies a pre-creation command that can drop, delete, or truncate the destination table before synchronization is applied. Can be:

None
Drop
Delete
Truncate

Remarks

Within an existing publication, you can change a certain article (using sp_changearticle) without having to drop and re-create the entire publication. Only unsubscribed articles can be changed. Only the system administrator and the database owner can change an article.

Example

This example renames the ART_ALL article to ART_NEW.

sp_changearticle PUB1, ART_ALL, name, 'ART_NEW'

Permission

Execute permission defaults to the system administrator and the database owner.

Tables Used

sysobjects, sysarticles, syspublications, sysdatabases

See Also