• ASE-15.7 SP100 documentation index
• ownership chains article (for ASE-15.7 SP100)

> SELECT * FROM some_wide_table
> go -m vert
    

 SELECT Datediff(SECOND, '1970-01-01', dateadd(HOUR, 4, getdate()))

SELECT dateadd(hour, -5, dateadd(second, timeSSE, '19700101')) from SomeTable

sp_helprotect someTableName

This configuration is kept in the so called interfaces file which is located at

$SYBASE/interfaces

/soft/SYBASE15.7/interfaces

sp_depends some_table_name;

To see the plan for a query you need to toggle on the showplan option:

set showplan on;

For cleaner output you may also want to toggle on the noexec option as well.

Don't forget to toggle them back off when you're done:

set showplan off;
set noexec off;

Information about which options are set on a particular session is cryptically encoded in the @@options variable:

select @@options

To see the current transaction's chaining mode:

SELECT @@tranchained

0 = unchained
1 = chained

To see the current transaction "depth":

SELECT @@trancount

Currently connected login (server user name):

select suser_name()

sp_displaylogin

Alias in the current DB (or name under which user is known in the DB)

select user

select user_name()

source: DVS gmail (CfA) 2018-09-20 (edited)

SELECT CAST(loginame AS VARCHAR(20)), CAST(name AS VARCHAR(20)) FROM sysobjects WHERE type="P"

If, instead, you want the database-specific id, do:

SELECT object_owner_id(id), CAST(name AS VARCHAR(20)) FROM sysobjects WHERE type="P";

To get, in addition to the above, the local, database-specific username do:

SELECT u.name user_name, CAST(o.name AS VARCHAR(20)) FROM sysusers u INNER JOIN sysobjects o ON u.uid=o.uid AND o.type="P";

Relevant sections from the documentation:

• ASE 15.7 SP 100 - Security Administration Guide - Chapter 3: Managing Adaptive Server Logins and Database Users

When a new user needs to connect to a SQL Server, the first thing that needs to be done is for the Security Officer (SO) to create a new "login account". The login is at the level of the server as a whole, and is not tied to a particular database (even though some default database may optionally be chosen so that the user doesn't have to type 'use <dbname>'once he's in.

It follows therefore, that the login name chosen by the SO must be unique. This name is also known as "Server User Name" and is not to be confused with the DB-specific usernames. Once the SO executes the create login command, a new row is added in the master.dbo.syslogins table. This row includes:

• the login name (a.k.a. server user name — not to be confused with the database-specific usernames)
• the unique Server User ID (suid) for the new user

Subsequently, when a DataBase Owner (DBO) or System Administrator (SA) can use sp_adduser to add a login account to a specific database (source). At that point, the DBO or SA can optionally assign a "name_in_db" which maybe different than the login name and by which the user will be known inside that particular database. Otherwise the username in the database will be the user's login name. The sp_adduser SP adds a row in the sysusers system table in that database. When a user has an entry in the sysusers table of a database he can issue use database_name to access that database.

The following diagram shows the correspondances between the most important columns of tables syslogins and sysusers as discussed above (and also includes sysobjects):

SELECT suid, name, dbname FROM master.dbo.syslogins ORDER BY suid DESC;

select suser_name();

sp_helpuser johndoe

sp_helpuser

SELECT name, dbname FROM master.dbo.syslogins WHERE name='username';

sp_stored_procedures;

caveat: the above apparently only shows the SPs that the current user has EXECUTE permissions on. To see all SPs (regardless of EXECUTE privilleges) the below is more reliable:

sp_ls p;

sp_depends viewname;

sp_tables @table_type="'VIEW'";

The below query shows all constraints in a database, the tables they are defined on and the constraints' type. In the case of Foreign Key constraints, the query show the tables the FK constraints originate from (the source tables, not the target tables):

SELECT CONVERT(VARCHAR(30),so1.name) table_name, so2.type, CONVERT(VARCHAR(30),so2.name) constraint_name
FROM sysconstraints sc, sysobjects so1, sysobjects so2
WHERE sc.tableid=so1.id AND sc.constrid=so2.id
ORDER BY table_name;

If you want to explicitly search for Referential Integrity (foreign key) constraints only, do:

SELECT CONVERT(VARCHAR(30),so1.name) table_name, CONVERT(VARCHAR(30),so2.name) constraint_name
FROM sysconstraints sc, sysobjects so1, sysobjects so2
WHERE sc.tableid=so1.id AND sc.constrid=so2.id AND so2.type='RI'
ORDER BY table_name;

In sqsh do the following:

[97] sqldev.proposal.1> DECLARE @outputParam1 VARCHAR(50)
[97] sqldev.proposal.2> DECLARE @outputParam2 INT
[97] sqldev.proposal.3> EXECUTE some_stored_procedure 'foo', 42, NULL, @outputParam1 OUTPUT, @outputParam2 OUTPUT
[97] sqldev.proposal.4> go
(return status = 0)


 ----------- --
        bar zar

(1 row affected)
[98] sqldev.proposal.1> 

In DbVisualizer do the same followed by:

SELECT @outputParam1, @outputParam2

Login names are what have passwords associated with them. Once logged-in, the login names are mapped to user names. The correspondence from login names to user names is many-to-one although in practice I've seen it's often one-to-one

The sp_helpuser stored procedure displays the username associated with a particular login name as well as that username's user id.

select suid, name from sysusers;

list tables

sp_ls;

list triggers

sp_ls t;

list SPs

sp_ls p; — see also the caveat here

list views

sp_ls v;

Use the sp_configure stored procedure and grep for connections. I.e. something like:

 sp_configure; | grep -i connections 

I've also used this:

sp_configure "number of user connections"
go 

Sybase as well as the equally abominable SQL Server don't treat NULLs according to the ANSI SQL standard by default. In Sybase, you'd have to explicitly set the ansinull setting to make sure you have a sound environment. This can be set on a connection basis but I suppose the default can also be configured globally somehow.

[1] sqldev> set ansinull on;
[2] sqldev> select count(*) from some_table WHERE some_column = null;

 -----------
           0

(1 row affected)
[3] sqldev> select count(*) from some_table WHERE some_column IS null;

 -----------
        2990

(1 row affected)
[4] sqldev> set ansinull off;
[5] sqldev> select count(*) from some_table WHERE some_column = null;
 -----------
        2990
    

Sybase ASE does not have a proper schema concept. Instead it conflates the concept of a user with that of the schema. Tables are thought to be owned by the user who created them. See e.g. this SO answer on the topic where we read:

Sybase ASE does not use the schema concept that SQL Server and Oracle use. Objects are located inside a database, and owned by a user - no other logical separations are there.

Oracle (and Microsoft) use a schema logical construct that Sybase ASE does not. In SAP/Sybase ASE, all tables and columns in a database are in the same schema. It is possible to users to have their own objects in the database though, so there is the possibility of imitating some of the schema behavior using user ownership, but it would require an extra level of effort.

As such the notation dbname.username.tablename is used with the meaning "table tablename owned by user username in the database dbname"

This being said, since it is possible for different user to have different tables with the exact same name in the same database, this concept does allow one to user usernames as namespaces for tables much as the schemas are used in PostgreSQL to provide a namespace for table names.

However, this is not the established convention. Instead, the established convention is the following:

• all objects (tables, views, stored procedures) are owned by the database owner (that's the user dbo)
• users do not own any objects at all, …
• …instead the dbo user owns everything and grants to the users permissions to read or update the various tables, call the various stored procedures etc.

This is ostensibly done so that when a user is fired, you don't have to rename their tables. IMO this doesn' make much sense as there's nothing that says that the users of a database have to correspond to physical persons.

OTOH and in defence of Sybase it has to be said that since it is possible to have Foreign Key constraints across databases (in the same server obviously), then the concept of the database itself functions like the namespace for tables. Instead, in PostgreSQL, last time I used it (2015), it wasn't possible to have FK constraints across databases so the schema concept was indispensable. Still that's not quite the same thing as you have to create the same user on multiple databases if your application needs to access a bunch of different databases (which is very often the case).

At any rate and coming back to the dbname.username.tablename notation, either or both of the dbname and username components may be missing:

• If the dbname component is missing then it is assumed to be the database currently used by the connection
• If the username component is missing then it is assumed to be the user that's logged-in on the current connection. However, if that user owns no tables (which is what the established convention is), then Sybase falls back to the dbo user and looks there for a table named tablename.

See also this SO question of mine and this older note of mine.

To find which Server User (i.e. login account) is connected on the particualr connection we are in, use the suser_name() function. E.g. from sqsh:

select suser_name();

To show all currently logged-in users and information about their connections (e.g. client host name, database name, command) use the system stored procedure sp_who. E.g. from sqsh:

 sp_who; | grep something

Use: sp_helpsort

Sample output:

(lot's of output omitted)
...
Character Set = 1, iso_1
     ISO 8859-1 (Latin-1) - Western European 8-bit character set.
 Sort Order = 50, bin_iso_1
     Binary ordering, for the ISO 8859/1 or Latin-1 character set (iso_1).
Characters, in Order 

source

Sybase Servers support the use of date and time data through the datetime and smalldatetime datatypes (and, with newer server, the date and time datatypes), as well as the getdate(), dateadd(), datediff(), and datepart() functions. The getutcdate() function was also added to some servers to provide the current datetime value in Coordinated Universal Time regardless of the time zone the server is otherwise running under.

The datetime and smalldatetime datatypes, however, do not store time zone information and the products are entirely ignorant of the concepts of time zones and daylight saving time. Sybase Servers only recognize and store the date and time portions of the values provided by the operating system, which are based on the time zone configured at the operating system level (typically though the TZ environment variable setting in Unix or the Date/Time function of the Windows Control Panel) for the user who started the product. The calculations behind the dateadd and datediff functions are aware of leap years (using the rule of every 4th year, except for every 100th year, except for every 400th year), but do not include any adjustments for leap seconds or transitions from daylight saving time to regular time.

Therefore changing the underlying OS timezone will not have any appreciable effect on the data stored in the datetime fields in one's database.

Also, the implication of the above is that looking at the DATETIME values in a Sybase database you don't know the exact time unless you also know the timezone of the server!

sp_help <rule-name>
sp_helptext <rule-name>

System procedures such as sp_procxmode are stored in database sybsystemprocs.

The documentation says (the link does not appear very trustworthy):

System procedures are created by installmaster at installation. They are located in the sybsystemprocs database, and owned by the system administrator. Use sp_version to determine which version of installmaster was most recently run.

Some system procedures can be run only in a specific database, but many of them can be run in any database. You can create your own system procedures that can be executed from any database. For more information, see the System Administration Guide.

All system procedures execute at isolation level 1.

All system procedures report a return status. The following example means that the procedure executed successfully: return status = 0

Beginning with Adaptive Server® version 12.5.1, you can declare up to 10,000 variables in a stored procedure. In earlier versions of Adaptive Server, the limit was 2,000.

sp_procxmode <stored procedure name>

SELECT @@TRANCOUNT

sp_who; | grep 'somedatabase\|threadpool'

sp_helpconstraint <table-name>;

Note first of all that Sybase is totally oblivious to time zones. It stores no time zone iformation at all. The server simply stores whatever Datetime values the client provides without applying any kind of transformation. It also reports the stored values without any kind of transformation. So the onus is on the client application to not mess up. Also, in case different application access the same server, they should all be aware of each other's conventions.

The only case I am aware of, of the server reporting a Datetime value that was not previously provided by the client, is with the getDate function:

select getDate()

… in this case I've verified that the server's behavior is identical to simply returning the date value provided by Linux (e.g. using date) totally ignoring the timestamp information.

I am not aware of a method to inquire a Sybase server as to the time zone it is located (e.g. what you get in Unix with date +%z or date +%Z). This is equivalent as saying that it is impossible to ask a Sybase server to return the time in seconds since the epoch (what you get with date +%s in Bash).

With the above caveats in mind, below are the functions to convert between Unix epoch values and Datetime values in Sybase:

Unix Epoch to Datetime

SELECT dateadd( SECOND, 1489426961, '1970-1-1' )

Datetime to Unix Epoch

SELECT datediff( SECOND, '1970-1-1', getDate())

Invariant (sanity check)

SELECT datediff (SECOND, '1970-1-1', dateadd( SECOND, 42, '1970-1-1' ))

Finally, just to note that some useful Sybase Datetime to String conversion formats are defined under path Transact-SQL Users Guide > Using Transact-SQL Functions in Queries > Built-in functions. I am not providing a link as the Sybase shitty company can't even implement bookmarkable documentation.

The datetime data type in Sybase (this applies to SQL Server as well), is able to only distinguish time in (approximately) 1/300ths of a second. No greater resolution is possible. Contrast that with glorious PostgreSQL which has microsecond resolution for a huge range of dates.

This is corroborated by the fact that the following query, when executed on a table with more than 1000 datetime values, yields only 237 distinct millisecond values:

 SELECT COUNT(*) FROM
 (
 SELECT DISTINCT y.millis FROM
 (
 SELECT RIGHT(RTRIM(LTRIM(x.submissionDate)), 6) AS millis FROM (
 SELECT convert(char(32), submission_date, 139) AS submissionDate FROM proposal..proposal
 ) x
 ) y
 ) z

So, the bottom line is that when using a client-side API such as JDBC, the java.sql.Timestamp (which can hold an exact date up to nanosecond precision) is way more than enough to accurately convey to the client side any value stored in a datetime column.

Conversely, it also means that the datetime data type is inadequate to hold all possible values of a java.sql.Timestamp or indeed, even of the much humbler java.util.Date which has millisecond resolution.

The wretched Sybase documentation imprecisely states that the datetime data type can hold values accurate to 1/300 second. This is misleading. What they actually mean is that the resolution / granularity / precision (pick your favorite term) of the datetime data type is approximately 3 ms.

Sources:

• my question on SO on datetime fractional seconds (prompted by the misleading and unclear Sybase documentation). See especially, the update on my own answer.
• Slightly irrelevant (but provides nice links) answer on the fact that Sybase time data types do not store time zone information: How does Sybase store datetime information?

SELECT convert(char(32), submission_date, 139) AS submissionDate FROM proposal..proposal

• Sybase CONVERT function format style codes

• use the -J switch to select the iso_1 character set. For some reason, that results in denser output. E.g.:

  sqsh -Jiso_1 -Umperdike -Ssqldev

• Append, after a SQL statement or the call to a stored procedure -mbcp. This will result in pipe-separated output which is denser. E.g.:

  select * from sysobjects; -mbcp

  … or:

  sp_ls; -mbcp

• the -h option turns off column headers and trailers. E.g. stuff like "(#rows affected)" or "(return status=0)" — but sadly not "Type added" messages.

  Sample invocation:

  sqsh -h -Uusername -Sdbname -i script-dml.sql

• the -a option supposedly is the maximum number of errors before aborting but I 've tried that with 0 and 1 and it didn't seem to work (I got the impression that the script carried on)
• the -e option allows you to echo each line that sqsh executes. In this way when something breaks you'll be able to see the approximate point where the breakage happened. Example invocation:

  sqsh -e -Uusername -SdatabaseName -i script-ddl.sql 

Table sysobjects is an interesting table (do a sp_cols on it to see more).

To verify that table sysobjects is among the system tables in a database do:

select name from sysobjects where type='S'

To view all user tables in a database do:

select name from sysobjects where type='U'

To view all stored procedures in some other database 'foo' that have the string 'person' in their name do:

select type, name from foo..sysobjects where type='P'; | grep person

show information on databases

sp_helpdb

show a lot of information on a table

sp_help sometablename

show the columns of a table or a SP

sp_cols sometable. Also sp_colsn is similar except in that it prints the columns in alphabetical order (and in that sense can be simulated with a sp_cols sometable; | sort

show privilleges of user

sp_helprotect <username>

show permissions on object

sp_helprotect <object> (same SP as above, it can polymorphically accept as an argument either the name of an object or the name of a user)

show tables

sp_ls

show tables and views

sp_ls a

show tables sorted

sp_lsn

show views

sp_ls v

show procedures

sp_ls p

show triggers

sp_ls t

NB: CfA-specific

sqsh -Ssqldev -Dsybsystemprocs -Umperdike

sp_helptext sp_cols;

• connect to the default database of a particular user on a particular server

sqsh -Uloginname -Sservername

What is provided in the above incantation is the loginname, which is mapped to a username. Once inside (at the prompt) you can obtain the username with:

select user_name();

… which may very well not be the same as the loginame. The loginname itself is shown with:

select suser_name();

In general, the dbadmin will map login names to usernames. The mappings are shown by the command:

sp_helpuser;

To see the database we were connected to, do a:

select db_name();

… if a database was not provided as a command line argument on the sqsh tool invocation (using -D), then that database is the default database associated with a given login.

Tools such as sqsh use the so called "login names". Once inside the prompt these are mapped to the DB user names (I've also seen those referred to as "name-in-db"). Every login name has a default database.

The default database for a particular login can be obtained by the following query:

select name, dbname from master.dbo.syslogins; | grep part-of-the-username

NB: See also this more recent note of mine.

In ASE, there is no concept of a "schema" (like you have in PostgreSQL or Oracle), instead tables are owned by users. So, hierarchically, the concepts of database server, database, user and table are arranged as follows:

Referential integrity constraints can cross databases.

Once connected to a Sybase server (e.g. by means of the sqsh tool) you can use either of the below forms to access a table:

database.owner.table

this is the fully qualified form, e.g. SELECT * FROM cxcaccounts.dbo.a where:

cxcaccounts

is the database name

dbo

is the user—not the login ('dbo' stands for Database Owner)

a

is the table name

database..table

E.g. SELECT * FROM cxcaccounts..a where the two dots .. stand for the owner of that table (or the default owner / usename—this is not very clear at the moment)

..table

…where the omitted database name is taken to be the default database (I think)

See also this dba.stackexchange question of mine.

An ASE database server (single OS process with a single listening port) contains multiple databases, some of which are "system" databases and the rest are "user" databases (e.g. in the CfA environment these would be cxcaccounts, axafusers and about 40 others). Two system databases that are always present in an ASE system are:

• master
• sybsystemprocs