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