create proxy_table

Description

(Component Integration Services only) Creates a proxy table without specifying a column list. CIS derives the column list from the metadata it obtains from the remote table.

Syntax

create proxy_table table_name
	[external [table | directory | file]]
	at pathname
	[column delimiter “<string>”]

Parameters

table_name

specifies the local proxy table name to be used by subsequent statements. table_name takes the form dbname.owner.object, where dbname and owner are optional and represent the local database and owner name. If dbname is not specified, the table is created in the current database; if owner is not specified, the table is owned by the current user. If either dbname or owner is specified, the entire table_name must be enclosed in quotes. If only dbname is present, a placeholder is required for owner.

external table

specifies that the object is a remote table or view. external table is the default, so this clause is optional.

external directory

specifies that the object is a directory with a path in the following format: “/tmp/directory_name [;R]”, where “R” indicates “recursive.”

external file

specifies that the object is a file with a path in the following format: “/tmp/filename”.

at pathname

specifies the location of the remote object. pathname takes the form server_name.dbname.owner.object, where:

  • server_name – is the name of the server that contains the remote object.

  • dbname – (optional) is the name of the database managed by the remote server that contains this object.

  • owner – (optional) is the name of the remote server user that owns the remote object.

  • object – is the name of the remote table or view.

string

The column delimiter string can be any character sequencer, but if the string is longer than 16 bytes, only the first 16 bytes are used. The use of a column delimiter for proxy tables mapped to anything but files results in a syntax error.

Examples

Example 1

This example creates a proxy table named t1 that is mapped to the remote table t1. CIS derives the column list from the remote table:

create proxy_table t1 
at "SERVER_A.db1.joe.t1"

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

create proxy_table permission defaults to the table owner and is not transferable.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

11

create

create procedure

  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

See also

Commands create existing table, create table