Sybase: Find all tables in a database with a unique index

The following statement will return a list of all user tables in the current database which have a unique index:

SELECT o.name FROM sysobjects o, sysindexes i WHERE o.id = i.id AND o.type='U' AND i.status & 2 = 2 GROUP BY o.name ORDER BY o.name
 

If you're also interested in the index name, use the following:
 

SELECT o.name, i.name FROM sysobjects o, sysindexes i WHERE o.id = i.id AND o.type='U' AND i.status & 2 = 2 ORDER BY o.name

Sybase: create a proxy for a remote database

Note that all strings in bold+italic should be changed to something which is relevant for your setup.

First you have to make the remote server known locally:

sp_addserver "logical_name" , ASEnterprise, "xxx.xxx.xxx.xxx:2055"
 

You can choose anything as a logical name. This is how the remote server will be called in master..sysservers and the name used by the other commands we'll use.
If you use another port than 2055, you of course have to change it in the statement above.
"ASEntreprise" is the remote server type and means it is also an ASE server.

You can check whether the remote server has been added properly:

SELECT * FROM master.dbo.sysservers WHERE srvname = "logical_name"

Now you need to create an entry in the sysremotelogins table for the remote server. This is done with the stored procedure sp_addremotelogin.

If you have the same users on both server, you can just execute the following to map remote names to local names:

sp_addremotelogin logical_name

If you want to map all logins from the remote server to a local user name:

sp_addremotelogin logical_name, local_user_name

If you only want to map a single remote login from a remote user on the remote server to a local use:

sp_addremotelogin logical_name, local_user_name, remote_user_name

Now we can create the proxy database:

create database proxy_db_name on default = 200M with default_location = 'logical_name.remote_db_name..' for proxy_update
 

If you do not want to create it on default but on a new device, you'll need to first create the device.

disk init name="proxy_dev", physname="/var/sybase/ASE/proxy_dev.dat", size="200M"

Of course the parent directory of the file which path is set in physname should exist and the appropriate rights should be set.
(you can also added the parameter directio=true to this command if required)

And then create the database:

create database proxy_db_name on proxy_dev = 200M with default_location = 'logical_name.remote_db_name..' for proxy_update

Now you can use the proxy database to access data of all tables in the remote database:

select name from proxy_db_name..sysobjects

The data are still residing on the remote server so you do not need to do anything when data change. But if the structure changes (i.e. if you add or remove tables or update the structure of a table), you need to update the proxy:

alter database proxy_db_name for proxy_update

Sybase: check whether a temporary table exists

You can check for the existence of non-temporary tables (even in in tempdb) like this:

IF EXISTS (SELECT 1 FROM <databasemase>..sysobjects where type='U' and name='<tablename>' )

Unfortunately it doesn't work for temporary tables since temporary tables (e.g. #mytemptable) are created for the current connection and another connection might create another temporary table with the same name. So Sybase will create an entry in tempdb..sysobjects with a name containing some other info (like the spid):

create table #1234567890 (i1 int)

will be mapped to:
ASE pre-15: #1234567890__00000306012214068
ASE 15: #123456789000003060012214068

So you might do something like this:

IF EXISTS (SELECT 1 FROM tempdb..sysobjects where type='U' and name LIKE '<tablename>%'+convert(varchar(30),@@spid)+'%' )

Unfortunately, this will not work since you might have a connection with spid 30 and another with spid 306 and these '%' will mess everything up.

Also note that ASE 12.5 only considers the 13 first bytes of the table name. ASE 15 supports 238 bytes.

Please also note that the way these names are generated is not documented and might thus be changed without notice.

So finding out whether another session has created a temporary table with a given name is possible, but not easy and might be broken with any future version or patch.

But finding out whether such a table has been created in the same session is much easier:
 

IF object_id('<tablename>') IS NOT NULL

Note that this also works with global temporary tables (##xxxxx).