Joining system tables on two different ASA servers

[Former Member]

In order to compare dev and prod databases, I'd like to join SYSOBJECTS and SYSCOLUMNS tables of two servers. However the documentation says 'you cannot create proxies of system tables'. Is there a way to do what I want without creating proxy tables? I'm relatively new to Sybase ASA, so all suggestions/directions are welcome.

Note: a related but not duplicate question

Edit: The server version is 9. I thought I added that, I feel like there's a problem with revisions but I'll post that as another thread.

[Former Member]

The documentation is incorrect or unclear. The migration scripts will not migrate system tables (for obvious reasons) but as Volker points out, you can definitely create proxy tables to system tables. Can you please give a link as to where in the documentation you read about not being able to create proxy tables to system tables. I will then have the documentation team update that particular section.

[Former Member]

Thanks for the answer. Here's the link:

http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/pdf/dbugen9.pdf

And the content (p.615):

Creating proxy tables (Sybase Central) You can create a proxy table using either Sybase Central or a CREATE EXISTING TABLE statement. You cannot create proxy tables for system tables.

[VolkerBarth]

Ah, and it's still documented that way with the current 12.0.1 release - cf. the DCX page...

Though this may be documented correctly:

"CREATE TABLE AT ... " won't work with system tables, as they will already exist and cannot be created on the remote side. Only "CREATE EXISTING TABLE" does work. But still misunderstandable, methinks:)

[Former Member]

Okay, this documentation is actually correct. You can create proxy tables to system tables in general using SQL similar to the one that Volker provided below, but, Sybase Central explicitly weeds out system tables when it presents you with a list of remote tables. So yes, you can create proxy tables to system tables by explicitly using the CREATE EXISTING TABLE statement, but you cannot create proxy tables to system tables using Sybase Central. This section of the documentation deals with creating proxy tables using Sybase Central. I will ask the documentation team to clarify that the "no system tables" restriction applies to Sybase Central only and not to the CREATE EXISTING TABLE statement.

[Breck_Carter]

ha ha... saying 'you cannot create proxies of system tables' is the same as saying 'Foxhound does not exist' 🙂

[VolkerBarth]

So you've not used Sybase Central to "click'n'build" Foxhound:)

Breck a "script kid"?

Me, too.

[VolkerBarth · Accepted solution]

It surely is possible to access to system catalog of a different SA database through proxy tables. Note, however, as you are relating to already existing tables/views, you need to use the CREATE EXISTING TABLE syntax.

The following shows how to access the systable system view of the SA12 demo database:

create server SVR_SA_DEMO class 'saodbc' using 'SQL Anywhere 12 Demo';
create externlogin "DBA" to SVR_SA_DEMO remote login "DBA" identified by 'sql';
create existing table DEMO_SysTable at 'SVR_SA_DEMO..sys.systable';
select * from DEMO_SysTable order by table_name;

For obvious reasons, the following will fail with missing permissions:

update DEMO_SysTable set table_name = 'test';

The following will show all tables/view contained in the current, but not in the demo database:

select table_name from sys.systable
except
select table_name from DEMO_SysTable
order by 1

[Former Member · Accepted solution]

Thanks, it works!