> 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)
SELECT Datediff(SECOND, '1970-01-01', dateadd(HOUR, 4, getdate()))
SELECT dateadd(hour, -5, dateadd(second, timeSSE, '19700101')) from SomeTable
sp_helprotectcan also be used on other objects besides tables.
This configuration is kept in the so called interfaces file which is located at
To see the plan for a query you need to toggle on the
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
select @@optionsSome documentation is available here.
To see the current transaction's chaining mode:
SELECT @@tranchainedThe 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.
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_name()(I haven't yet seen any difference between the two)
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";
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):
SELECT suid, name, dbname FROM master.dbo.syslogins ORDER BY suid DESC;
sp_helpuser johndoe… or
sp_helpuser… to see info on all users. See also sp_addalias
SELECT name, dbname FROM master.dbo.syslogins WHERE name='username';
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:
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:
 sqldev.proposal.1> DECLARE @outputParam1 VARCHAR(50)  sqldev.proposal.2> DECLARE @outputParam2 INT  sqldev.proposal.3> EXECUTE some_stored_procedure 'foo', 42, NULL, @outputParam1 OUTPUT, @outputParam2 OUTPUT  sqldev.proposal.4> go (return status = 0) ----------- -- bar zar (1 row affected)  sqldev.proposal.1>
In DbVisualizer do the same followed by:
SELECT @outputParam1, @outputParam2… and execute the entire block together as a single batch.
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
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;
sp_ls p;— see also the caveat here
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… I am not clear on what's the difference between the two.
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.
 sqldev> set ansinull on;  sqldev> select count(*) from some_table WHERE some_column = null; ----------- 0 (1 row affected)  sqldev> select count(*) from some_table WHERE some_column IS null; ----------- 2990 (1 row affected)  sqldev> set ansinull off;  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.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.
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:
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
(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
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>With an extra argument can also be used to change the mode to either 'chained' or 'unchained'.
SELECT @@TRANCOUNTA 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.
sp_who; | grep 'somedatabase\|threadpool'
sp_helpconstraint <table-name>;… the above will show all foreign keys both originating from this table and pointing to this table.
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:
… 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:
SELECT dateadd( SECOND, 1489426961, '1970-1-1' )
SELECT datediff( SECOND, '1970-1-1', getDate())
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.
datetimedatatype in Sybase (and SQL Server)
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,
(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
Conversely, it also means that the
datetime data type is
inadequate to hold all possible values of a
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
datetime data type is approximately 3 ms.
SELECT convert(char(32), submission_date, 139) AS submissionDate FROM proposal..proposalSources:
sqsh -Jiso_1 -Umperdike -Ssqldev
select * from sysobjects; -mbcp… or:
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.
sqsh -h -Uusername -Sdbname -i script-dml.sql
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'… this is the same set returned with
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
sp_cols sometable. Also
sp_colsnis similar except in that it prints the columns in alphabetical order (and in that sense can be simulated with a
sp_cols sometable; | sort
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)
NB: CfA-specificYou need to connect to the sybsystemprocs database:
sqsh -Ssqldev -Dsybsystemprocs -Umperdike… and then do, e.g.:
sqsh -Uloginname -SservernameWhat 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
sqshtool 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(source)
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:
SELECT * FROM cxcaccounts.dbo.awhere:
SELECT * FROM cxcaccounts..awhere the two dots
..stand for the owner of that table (or the default owner / usename—this is not very clear at the moment)
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: