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

sp_addarticle sp_droppublication
sp_addpublication sp_enumfullsubscribers
sp_articlecolumn sp_helparticle
sp_changepublication sp_helparticlecolumns
sp_droparticle sp_helppublication