Using the create existing table Command

The create existing table command allows the definition of existing tables (proxy tables).

The syntax for this option is similar to the create table command:

create existing table table_name (column_list) 
[[external {table | procedure | file}] at pathname]

The action taken by the server when it receives this command is quite different from the action it takes when it receives the create table command, however. A new table is not created at the remote location; instead, the table mapping is checked, and the existence of the underlying object is verified. If the object does not exist (either host data file or remote server object), the command is rejected with an error message.

If the object does exist, its attributes are obtained and used to update system tables sysobjects, syscolumns, and sysindexes.

The nature of the existing object is determined.
For remote server objects (other than RPCs), column attributes found for the table or view are compared with those defined in the column_list. Column names must match (case sensitive), column types and lengths must match, or at least be convertible, and the NULL attributes of the columns must match.
Index information from the host data file or remote server table is extracted and used to create rows for the system table sysindexes. This defines indexes and keys in server terms and enables the query optimizer to consider any indexes that may exist on this table.

After successfully defining an existing table, issue an update statistics command for the table.This allows the query optimizer to make intelligent choices regarding index selection and join order.

Define a server named big_server. Its server class is ASEnterprise, and its name in the interfaces file is big_server:
```
exec sp_addserver big_server, ASEnterprise, big_server
```
Define a remote login alias. This step is optional if the username and password are the same on both servers. User “sa” is known to remote server big_server as user “sa,” password “timothy”:
```
exec sp_addexternlogin big_server, sa, sa, timothy
```

Define the remote authors table:

create existing table authors 
( 
au_id        varchar(11)      not null,
au_lname     varchar(40)      not null,
au_fname     varchar(20)      not null, 
phone        char(12)         not null,
address      varchar(40)      null,
city         varchar(20)      null,
state        char(2)          null,
country      varchar(12)      null,
postalcode   char(10)         null
)
EXTERNAL TABLE at "big_server.pubs2.dbo.authors"

Update statistics on tables to ensure reasonable choices by the query optimizer:
```
update statistics authors
```

Execute a query to test the configuration:

select * from authors where au_lname = 'Carson'