sysschemaarticles

Transact-SQL Reference

previous page next page
Transact-SQL Reference

sysschemaarticles

Tracks schema-only articles for transactional and snapshot publications. This table is stored in the publication database.

Column name Data type Description
artid int Article ID.
creation_script nvarchar(255) Path and name of an article schema script used to create the target table.
description nvarchar(255) Descriptive entry for the article.
dest_object sysname Name of the object in the subscription database if the article is a schema-only article, such as stored procedure, view, or UDF.
name sysname Name of the schema-only article in a publication.
objid int Object identifier of the article base object. Can be the object identifier of a procedure, view, indexed, view, or UDF.
pubid int ID for the publication.
pre_creation_cmd tinyint Specifies what the system should do if it detects an existing object of the same name at the Subscriber when applying the snapshot for this article:

0 = Nothing.
1 = Delete destination table.
2 = Drop destination table.
3 = Truncate destination table.

status int Bitmap used to indicate the status of the article.
type tinyint Value indicating the type of schema-only article:

0x20 = Stored procedure schema-only article.
0x40 = View schema-only article or. indexed view schema-only article.
schema_option binary(8) Bitmask of the schema generation option for the given article. It specifies the automatic creation of the stored procedure in the destination database for all CALL/MCALL/XCALL. It can be one of these values:

0x00 = Disables scripting by the Snapshot Agent and uses creation_script.
0x01 = Generates the object creation (CREATE TABLE, CREATE PROCEDURE, and so on). This value is the default for stored procedure articles.
0x02 = Generates custom stored procedures for the article, if defined.
0x10 = Generates a corresponding clustered index.
0x20 = Converts user-defined data types to base data types.
0x40= Generates corresponding nonclustered index(es).
0x80= Includes declared referential integrity on the primary keys.
0x73 = Generates the CREATE TABLE statement, creates clustered and nonclustered indexes, converts user-defined data types to base data types, and generates custom stored procedure scripts to be applied at the Subscriber. This value is the default for all articles except stored procedure articles.
0x100= Replicates user triggers on a table article, if defined.
0x200= Replicates foreign key constraints. If the referenced table is not part of a publication, all foreign key constraints on a published table will not be replicated.
0x400= Replicates check constraints.
0x800= Replicates defaults.
0x1000= Replicates column-level collation.
0x2000= Replicates extended properties associated with the published article source object.
0x4000= Replicates unique keys if defined on a table article.
0x8000= Replicates primary key and unique keys on a table article as constraints using ALTER TABLE statements.
dest_owner sysname Owner of the table at the destination database.

sysservers

Contains one row for each server that Microsoft® SQL Server™ can access as an OLE DB data source.

Column name Data type Description
srvid smallint ID (for local use only) of the remote server.
srvstatus smallint For internal use only.
srvname sysname Name of the server.
srvproduct nvarchar(128) Product name for the remote server.
providername nvarchar(128) OLE DB provider name for access to this server.
datasource nvarchar(4000) OLE DB data source value.
location nvarchar(4000) OLE DB location value.
providerstring nvarchar(4000) OLE DB provider string value.
schemadate datetime Date this row was last updated.
topologyx int Used by the SQL Server Enterprise Manager server topology diagram.
topologyy int Used by the SQL Server Enterprise Manager server topology diagram.
catalog sysname Catalog that is used when making a connection to an OLE DB provider.
connecttimeout int Timeout setting for server-connection.
querytimeout int Timeout setting for queries against server.
srvnetname char(30) Reserved (currently the same as the srvname).
isremote bit 1 if server is a remote server, else 0 if server is a linked server.
rpc bit 1/0 for sp_serveroption rpc set to true/false.
pub bit 1/0 for sp_serveroption pub set to true/false.
sub bit 1/0 for sp_serveroption sub set to true/false.
dist bit 1/0 for sp_serveroption dist set to true/false.
dpub bit 1/0 for sp_serveroption dpub set to true/false.
rpcout bit 1/0 for sp_serveroption rpc out set to true/false.
dataaccess bit 1/0 for sp_serveroption data access set to true/false.
collationcompatible bit 1/0 for sp_serveroption collation compatible set to true/false.
system bit 1/0 for sp_serveroption system set to true/false.
useremotecollation bit 1/0 for sp_serveroption use remote collation set to true/false.
lazyschemavalidation bit 1/0 for sp_serveroption lazy schema validation set to true/false.
collation sysname Server collation as set by sp_serveroption collation name.

previous page start next page