From a Sybase Database, how I can get table descri

Question

I have access to command line isql and I like to get Meta-Data of all the tables of a given database, possibly in a formatted file. How I can achieve that?

Thanks.

Answer 1

sp_help is what you're looking for.

From Sybase online documentation on the sp_help system procedure:

Description

Reports information about a database object (any object listed in sysobjects) and about system or user-defined datatypes, as well as computed columns and function-based indexes. Column displays optimistic_index_lock.

Syntax

sp_help [objname]

[...]

Here is the (partial) output for the publishers table (pasted from Using sp_help on database objects):

Name               Owner        Object_type     Create_date 
----------------   -----------  -------------   ------------------------------
publishers         dbo          user table      Nov 9 2004 9:57AM

(1 row affected)
Column_name Type     Length   Prec  Scale   Nulls   Default_name   Rule_name
----------- -------  ------   ----- ------- ------- -------------- ---------- 
pub_id      char          4    NULL  NULL        0  NULL           pub_idrule
pub_name    varchar      40    NULL  NULL        1  NULL           NULL
city        varchar      20    NULL  NULL        1  NULL           NULL
state       char          2    NULL  NULL        1  NULL           NULL
Access_Rule_name    Computed_Column_object     Identity
------------------- -------------------------  ------------
NULL                NULL                                  0
NULL                NULL                                  0
NULL                NULL                                  0
NULL                NULL                                  0

Still quoting Using sp_help on database objects:

If you execute sp_help without supplying an object name, the resulting report shows each object in sysobjects, along with its name, owner, and object type. Also shown is each user-defined datatype in systypes and its name, storage type, length, whether null values are allowed, and any defaults or rules bound to it. The report also notes if any primary or foreign key columns have been defined for a table or view.

Answer 2

Check sysobjects and syscolumns tables.

Here is a diagram of Sybase system tables.

List of all user tables:

SELECT * FROM sysobjects WHERE type = 'U'

You can change 'U' to other objects:

• C – computed column
• D – default
• F – SQLJ function
• L – log
• N – partition condition
• P – Transact-SQL or SQLJ procedure
• PR – prepare objects (created by Dynamic SQL)
• R – rule
• RI – referential constraint
• S – system table
• TR – trigger
• U – user table
• V – view
• XP – extended stored procedure

List of columns in a table:

SELECT sc.* 
FROM syscolumns sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE so.name = 'my_table_name'

Answer 3

If Sybase is SQL-92 compliant then this information is stored within the INFORMATION_SCHEMA tables.

So the following will give you a list of tables and views in any SQL-92 compliant database

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES

Answer 4

     SELECT
DB_NAME() TABLE_CATALOG,
NULL TABLE_SCHEMA,
so.name TABLE_NAME,
sc.name COLUMN_NAME,
sc.colid ORDINAL_POSITION,
NULL COLUMN_DEFAULT,
CASE WHEN st.allownulls=1 THEN 'YES'
 ELSE 'NO'
END IS_NULLABLE,
st.name DATA_TYPE,
CASE WHEN st.name like '%char%' THEN st.length
END CHARACTER_MAXIMUM_LENGTH,
CASE WHEN st.name like '%char%' THEN st.length
END*2 CHARACTER_OCTET_LENGTH,
CASE WHEN st.name in ('numeric','int') THEN st.length
END NUMERIC_MAXIMUM_LENGTH,
CASE WHEN st.name in ('numeric','int') THEN st.prec
END NUMERIC_PRECISION,
NULL NUMERIC_PRECISION_RADIX,
CASE WHEN st.name in ('numeric','int') THEN st.scale
END NUMERIC_SCALE,
CASE WHEN st.name in ('datetime') THEN st.prec
END DATETIME_PRECISION,
NULL CHARACTER_SET_CATALOG,
NULL CHARACTER_SET_SCHEMA,
NULL COLLATION_CATALOG,
NULL COLLATION_SCHEMA,
NULL DOMAIN_CATALOG,
NULL DOMAIN_SCHEMA,
NULL DOMAIN_NAME
FROM 
sysobjects so
INNER JOIN 
syscolumns sc
ON sc.id = so.id
inner join systypes st on st.usertype = sc.usertype 
WHERE so.name = 'TableName'

Answer 5

Here a different approach to get meta data. This very helpful SQL command returns you the table / view definition as text:

SELECT text FROM syscomments WHERE id = OBJECT_ID('MySchema.MyTable') ORDER BY number, colid2, colid

Enjoy Patrick

Answer 6

sp_tables will also work in isql. It gives you the list of tables in the current database.