Sybase ADDICT

The following transact-sql code will help you to find all defined rules in a Sybase ASE database.

select sysobjects.name as "rule name",
object_name(syscolumns.id) as "table name",
syscolumns.name as "column name"
from sysobjects, syscolumns
where object_name(syscolumns.domain)=sysobjects.name
and sysobjects.type = 'R'
go

The output displays the table and column to which the rules are bound.

Quickly find out the database’s tables having identity columns:

select object_name(id) as "tablename",name as "column_name"
from syscolumns where status =128
go

Enjoy

Find out the dataserver’s page size without using global variables like @@pagesize or @@maxpagesize.

When you work with heterogeneous versions of Sybase Ase (12.0.x and 12.5.x or 15.x), this query can help you while coding your dba stored procedures…

declare @page_size int
select  @page_size = low /1024 from master.dbo.spt_values
where type = 'E' and number = 1
go

It will produce the result in Kb. This works with Sybase ASE from version 11.x to 15.x.

Might be a good idea to share some usefull code snippet. Let’s start with a simple query to find out text/image columns in a database. This one will build a list of columns using text/image columns for each tables:

select user_name(o.uid) as "owner",
o.name as "table",
c.name as "column"
from   sysobjects o, syscolumns c
where  o.type = "U"
and    o.id = c.id
and    c.type in (34,35)
order  by 1,2,3
go

Enjoy

You just have to run a simple query in isql to find all the reserved words for Adaptive Server Enterprise and Sybase SQL Server. To list all reserved words for Adaptive Server Enterprise, log in to isql and run this query:

select name from master..spt_values where type = "W"
go

The spt_values table is a look-up table in the master database that stores system values used by other server processes.