Basic command for Sybase 12.5

Play with those commands:
========================================
set rowcount 10
select * from table -- show 10 rows of a table

select * from table where 1=2 -- show column names (and widths separators)
========================================
correct way to check if something is (not) null:
is null is not null

remember:
- you can not insert a null value into a column defined NOT NULL unless a column has a default value bound to it
- null values are not equal to each other (don't expect them to match in where clause)
- a row containing a null value is ignored in the calculation of aggregate values (AVG, SUM, MAX)

comparing fields one of which may be NULL:
- change to "where exists" or "where not exists", for example:
select t1.col11
from mytab t1
where
not exists (select * from mytab t2 where t2.parent_id = t1.id)
and t1.id not in (select id from some_other_table)
========================================
sp_helpdb -- show short help on all databases
sp_helpdb some_database -- show help on all databases

use somedatabase
select db_name()
select @@servername
select user_name()

select name from sysobjects where type='U' -- show list of user tables ('V' - list of views, 'S' - system tables)
sp_help some_table -- show definitions for a table
select name from sysobjects where type='P' -- show list of stored procedures
sp_helptext some_stored_procedure --show text of a stored procedure
select * from sysusers -- shows id, group and name
sp_helplogin -- shows list of logins and related databases

on a server level there are some useful tables, for example syslogins table in the master database:

use master
go
sp_help syslogins
select name, fullname, dbname from syslogins

get a list of all user tables and stored procedures in a database:

select 'mydb', type, name from mydb..sysobjects where type='U' order by name
select 'mydb', type, name from mydb..sysobjects where type='P' order by name

Use showplan to find out why the query is slow - and force indexes when necessary:

showplan options - - session-level set showplan on set noexec on -- only command it will execute after that is set noexec off real session: set showplan on go set noexec on go select ... go exec some_store_proc go set noexec off go set showplan off go forcing a select to use an index on the table. Let's say the table has 3 indexes listed in sp_help in order 1,2,3. If you want to use index #2, then in the 'from' part of the select statement put the index number in the parenthesis: select * from mytab t1 (2) where ... Alternatively you can refer the index by name (starting v.11.5): select * from mytab (index mytab_idx) where ...

Temp-tables with hashes in reality get names tagged with session: select name from tempdb..sysobjects where type = 'U' and name like '#temp3%' name 1 #temp3_______00000810011065957

Catching new line characters: select myname from mytable where charindex(char(10), myname) !=0

Conversion between binary(16) and varchar(32):
============================================
drop table #test

create table #test (cc varchar(32) null, bb binary(16) null)

-----------------------------
-- string to binary
-----------------------------
insert #test values ("873b7706de0011d59be0f05e055bbe70", null)

update #test
set bb = convert(binary(4), hextoint(substring(cc, 1, 8)))
+ convert(binary(4), hextoint(substring(cc, 9, 8)))
+ convert(binary(4), hextoint(substring(cc, 17, 8)))
+ convert(binary(4), hextoint(substring(cc, 25, 8)))
from #test
where cc="873b7706de0011d59be0f05e055bbe70"

-----------------------------
-- binary to string
-----------------------------
insert #test values (null,0x1234567890abcdef1234567890abcdef)

update #test
set cc = lower(
inttohex(convert(binary, substring(bb, 1, 4)))
+ inttohex(convert(binary, substring(bb, 5, 4)))
+ inttohex(convert(binary, substring(bb, 9, 4)))
+ inttohex(convert(binary, substring(bb, 13, 4)))
)
from #test
where bb=0x1234567890abcdef1234567890abcdef
============================================

select * from #test

============================================