Tag Archives: sybase

Sybase: list all tables with row counts

— sybase: list all existing tables with their rowcount select ob.name,st.rowcnt from sysobjects ob, systabstats st where ob.type=’U’and st.id=ob.id order by ob.name Pretty useful when filtering out empty tables, sorting by rowcount number and co sources: http://stackoverflow.com/questions/18195355/how-to-list-all-the-user-tables-in-sybase-along-with-their-rowcount

sybase: multi-line comment generates following error: Missing end comment mark ”.

In sybsase transact-sql script, my multiline comment /* … */ generated the following error: Missing end comment mark ”. The reason is that: my script is in an external file loaded via sqsh sqsh relies on isql in isql, according to official doc, ‘go’ instruction should not be at the beginning of a line inside a comment => I change my code from /* … go */ to /* … **go */ and this solved my issue. sources  http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.utility/html/utility/utility210.htm

sybase: fetch table size and indexes size

Pretty easy, use the sp_spaceused stored procedure as follow: sp_spaceused my_table_name go To focus on indexes for a given table, you can call sp_helpindex <index_name> query as follow: exec sp_helpindex  ‘my_table_name’ go sources http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc20020_1251/html/databases/X30242.htm

sybase: left pad with whitespace

Below is a quick & dirty function to left-pad an integer (or anything you want if you adapt the function) with a blank space (or any other character). Solution is based on Sybase native ‘replicate’ and ‘len’ functions. declare @id int, @prefix char(2), @padding_width int set @padding_width = 10. set @id = 66. set @prefix = ‘PR’ select @prefix || replicate(‘ ‘, @padding_width – len(convert(varchar(10), @id))) || convert(varchar(10), @id) go ———— PR 66 1 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms] sources http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks101.htm    

sybase: how to quickly concatenate two columns

To quickly concatenate two columns in sybase, you can use the ‘||’ (OR) operator in your select statement as illustrated below: select column_1 || column_2 from my_table It would return a single column with content: ‘value_1 value2’  

sybase: how to retrieve current date in a specific format

To retrieve current date in sybase, simply call “getdate()” as in: select getdate() Thinks far more interesting when you make usage of the “convert()” function as well. For instance, to display current date in the following format: yyyymmdd (with leading zero for both month and day), simply proceed as follow: select convert(char(8), getdate(), 112) //output: 20111208 if today is Dec. 8th, 2011 sources http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks125.htm http://forums.databasejournal.com/showthread.php?t=9511 http://www.mombu.com/programming/sybase/t-get-two-digit-month-708552.html

sybase: insert multiple lines at once

The syntax to insert multiple lines at one in sybase, using an ‘insert’ query, is as follow: insert into <table_name> (column1, column2, …) (select col1, col2, … from <src_table> where <where_clause>) Regular single-line insert syntax is as follow: insert into <table_name> (column1, column2, …) values (value1, value2, …)

sybase: how to find if a table exists

To find if a table exists in sybase, run the following query: SELECT 1 FROM sysobjects WHERE name = ‘my_table_name’ AND type = ‘U’ You can also wrap it into an IF EXISTS() test as follow: IF EXISTS (SELECT 1 FROM sysobjects WHERE name = ‘my_table_name’ AND type = ‘U’) BEGIN //DO SOMETHING END GO sources http://stackoverflow.com/questions/307942/how-do-i-conditionally-create-a-table-in-sybase-tsql

sybase: randomly retrieve rows

To retrieve random rows within sybase, you must first set rowcount to the number of rows you want to retrieve then use ‘rand()’ function in the order by clause, as follows: set rowcount 10 select * from myTable order by rand() go sources http://www.sybaseteam.com/get-random-records-from-a-table-t-784.html

sybase: what version am I running?

It’s pretty easy to find out which version of sybase you’re running thanks to @@version variable. Simply execute the following query: select @@version go