16.4.2012

describe table on sybase db

Filed under: Uncategorized — nax @ 17:31

I needed to find out types of columns in one sybase database. Usual SQL standard “describe table” didn’t work. I google a bit and found this solution.

Single command list all db objects:

kfsql-1) sp_help;
Name Owner Object_type
------------------------------ ------------------------------ ----------------------
INSTANCES dbo view
myapp_Audit dbo user table
myapp_Bindings dbo user table
myapp_CPSInstances dbo user table
...
(48 rows affected)
User_type Storage_type Length Nulls Default_name Rule_name Access_Rule_name
--------------- --------------- ----------- ----- --------------- --------------- ----------------

Class_name Jar_name
------------------------------ ------------------------------

Jar_name
------------------------------

(return status = 0)

When you add table name, it gives you lot of details for that table, including types of columns:

kfsql-1) sp_help myapp_Instances;
Name Owner Object_type
------------------------------ ------------------------------ --------------------------------
myapp_Instances dbo user table

(1 row affected)
Data_located_on_segment When_created
------------------------------ ------------------------
default Feb 2 2007 9:41PM

(1 row affected)
Column_name Type Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Identity
------------------------------ ------------------------------ ----------- ---- ----- ----- ------------------------------ ------------------------------ ------------------------------ --------
id int 4 NULL NULL 0 NULL NULL NULL 1
name varchar 40 NULL NULL 0 NULL NULL NULL 0
msgInstance int 4 NULL NULL 1 NULL NULL NULL
...
(18 rows affected)
index_name index_description ...
...
(2 rows affected)
No defined keys for this object.
Object is not partitioned.
Lock scheme Datarows

exp_row_size reservepagegap fillfactor max_rows_per_page identity_gap
------------ -------------- ---------- ----------------- ------------
0 0 0 0 0

(1 row affected)
concurrency_opt_threshold optimistic_index_lock dealloc_first_txtpg
------------------------- --------------------- -------------------
15 0 0

Comments are closed.

Powered by WordPress