1. Sybase documentation online
  2. sqsh — display query result set vertically
  3. This is very handy for wide result sets:
    > SELECT * FROM some_wide_table
    > go -m vert
    … observe that you're not supposed to end the query with a question mark in this case (for the go to have effect)
  4. how to get Seconds Since the Epoch
  5. To get Seconds Since the Epoch, and since Sybase is ignorant of time zones, you need to know the difference between your time zone and UTC (which may vary during the year due to the summer / winter time abomination). E.g. on March 13th 2019, Boston was 4 hours behind UTC so the below query worked in that case:
     SELECT Datediff(SECOND, '1970-01-01', dateadd(HOUR, 4, getdate()))
  6. convert seconds since the Epoch to human-readable Boston (EST) time
  7. SELECT dateadd(hour, -5, dateadd(second, timeSSE, '19700101')) from SomeTable

    … where timeSSE is the name of a column in table SomeTable that's holding an integer value denoting number of seconds elapsed since the Unix Epoch. Note that hour and second are unquoted literal values that Sybase understands in this context.
  8. show permissions on a table
  9. sp_helprotect someTableName
    NB: sp_helprotect can also be used on other objects besides tables.
  10. mapping of sqsh aliases to Unix hostnames
  11. This configuration is kept in the so called interfaces file which is located at


  12. how to view objects that depend on a table (e.g. triggers, SPs, indices)
  13. sp_depends some_table_name;
  14. showplan and noexec
  15. 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
    Some documentation is available here.

  16. obtaining useful information about the current transaction (transaction depth and chained mode)
  17. source

    To see the current transaction's chaining mode:

    SELECT @@tranchained
    The result is either 0 or 1, per the following:
    0 = unchained
    1 = chained

    To see the current transaction "depth":

    SELECT @@trancount
    … where a value of 0 indicates that we are outside of any transaction.

  18. how to find the currently connected user (in squish)
  19. Currently connected login (server user name):

    select suser_name()
    Full information about the current login:

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

    select user
    select user_name()
    (I haven't yet seen any difference between the two)

  20. how to find the owner of a particular SP
  21. source: DVS gmail (CfA) 2018-09-20 (edited)

    SELECT CAST(loginame AS VARCHAR(20)), CAST(name AS VARCHAR(20)) FROM sysobjects WHERE type="P"
    … the above fetches the "server user name" a.k.a. "login (account) name" of the owner.

    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";

  22. server user names (login names) and user names
  23. Relevant sections from the documentation:

    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:

    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):

  24. find all logins in an ASE server
  25. SELECT suid, name, dbname FROM master.dbo.syslogins ORDER BY suid DESC;
  26. show the currently connected user
  27. select suser_name();
  28. show login name and alias of a user
  29. sp_helpuser johndoe
    … or
    … to see info on all users. See also sp_addalias
  30. Find the default database for a particular user
  31. SELECT name, dbname FROM master.dbo.syslogins WHERE name='username';
  32. list stored procedures in a database (shows owner too)
  33. 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;

  34. how to view dependencies to / from a view in a database
  35. sp_depends viewname;
  36. how to list all views in a database
  37. sp_tables @table_type="'VIEW'";
  38. how to find all constraints in a database
  39. 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;

  40. how to execute a Sybase stored procedure with output variables
  41. 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
    … and execute the entire block together as a single batch.

  42. on login names and user names
  43. 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.

  44. list usernames and user ids in a database
  45. select suid, name from sysusers;
  46. list tables, triggers, stored procedures and views in a database
  47. list tables
    list triggers
    sp_ls t;
    list SPs
    sp_ls p; — see also the caveat here
    list views
    sp_ls v;
  48. show max number of user connections on a database
  49. 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"
    … I am not clear on what's the difference between the two.

  50. ANSI treatment of NULL values in comparisons
  51. 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;
    (1 row affected)
    [3] sqldev> select count(*) from some_table WHERE some_column IS null;
    (1 row affected)
    [4] sqldev> set ansinull off;
    [5] sqldev> select count(*) from some_table WHERE some_column = null;
  52. Schemas, users and the dbname.username.tablename notation
  53. 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.
    This SO answer is also pertinent, where we read:
    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:

    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:

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

  54. Find a connection's user and all currently logged-in users and connections
  55. 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

  56. Find character set and sort order of Sybase server
  57. 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 

  58. Sybase attrocious handling of time
  59. 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!

  60. show information about a rule, and its text
  61. sp_help <rule-name>
    sp_helptext <rule-name>
  62. database where the system procedures are installed
  63. source

    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.

  64. show chained mode of stored procedure
  65. sp_procxmode <stored procedure name>
    With an extra argument can also be used to change the mode to either 'chained' or 'unchained'.
  66. show transaction depth
    A value of `0` signifies that we are not inside a transaction. In case of nested transactions Sybase actually commits a transaction if a `commit` is given at level `1` only. Commits in levels greater than `1` simply reduce the transaction depth by one. A commit on level `1` reduces the depth to `0` and it is only at that point that Sybase commits the transaction.
  68. show connected users
  69. The below (in sqsh) will show all users connected to database somedatabase together with the header row for additional information:
    sp_who; | grep 'somedatabase\|threadpool'
  70. show a table's constraints (e.g. foreign keys)
  71. In sqsh:
    sp_helpconstraint <table-name>;
    … the above will show all foreign keys both originating from this table and pointing to this table.
  72. Sybase Datetime to Unix epoch
  73. 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' ))
    … given the caveats noted above, the conversion functions above (except the invariant) will return correct values only if the server is in UTC (which is where the Unix Epoch is defined). The invariant however works in all cases as the effects of the two transformations cancel each other out (so the invariant always returns 42).

    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.

  74. on the precision of the datetime datatype in Sybase (and SQL Server)
  75. 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 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.


  76. how to convert datetime to char
  77. SELECT convert(char(32), submission_date, 139) AS submissionDate FROM proposal..proposal
  78. tips on how to use sqsh effectively in interactive mode
  79. For some weird reason the output of many sqsh commands (either SELECT statements or stored procedures) is very wide and folds or generates line breaks that interfere with piping operations and stuff like cut or awk (that are possible in sqsh). To combat that:
  80. tips on how to use sqsh effectively when running scripts in batch mode
  81. The interesting table sysobjects
  82. 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'
    … this is the same set returned with sp_ls

    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

  83. common commands available inside the sqsh promprt
  84. For the invocation command line arguments to the sqsh tool themselves, see this note.
    NB: most of the below are specific to the CfA environment and you can see their code as shown in this note.
    show information on databases
    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
    show tables and views
    sp_ls a
    show tables sorted
    show views
    sp_ls v
    show procedures
    sp_ls p
    show triggers
    sp_ls t
  85. how to show source code of stored procedures
  86. NB: CfA-specific

    You need to connect to the sybsystemprocs database:
    sqsh -Ssqldev -Dsybsystemprocs -Umperdike
    … and then do, e.g.:
    sp_helptext sp_cols;

  87. common incantations of the sqsh tool
  88. For commands available once inside the sqsh prompt, see this note.
  89. login names, usernames and default databases
  90. 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

  91. Sybase ASE concepts
  92. 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:

    this is the fully qualified form, e.g. SELECT * FROM cxcaccounts.dbo.a where:
    is the database name
    is the user—not the login ('dbo' stands for Database Owner)
    is the table name
    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)
    …where the omitted database name is taken to be the default database (I think)

  93. Sybase ASE server structure
  94. 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:

    The database server may also be known by a database name (alias) that's used by tools like sqsh to connect to it. In the CfA environment the configurations are kept in file: /soft/SYBASE15.7/interfaces