From a Sybase Database how I can get table description field names and types

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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

For Sybase ASE  sp columns table name will return all the table metadata you are looking for

User · Answer

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

User · Answer

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

User · Answer

Sybase IQ   describe table name

User · Answer

If you want to use a command line program  but are not restricted to using SQL  you can use SchemaCrawler  SchemaCrawler is open source  and can produce files in plain text  CSV  or  X HTML formats

User · Answer

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

User · Answer

When finding user table  in case if want the table owner name also  you can use the following   select su name         so name from   sysobjects so         sysusers   su where  so type    U  and        so uid    su uid order  by su name            so name

User · Answer

You can search for column in all tables in database using   SELECT so name  FROM sysobjects so INNER JOIN syscolumns sc ON so id   sc id  WHERE sc name    YOUR COLUMN NAME

User · Answer

In the Sybase version I use  the following gives list of columns for selected table  select   FROM sys syscolumns sc where tname    YOUR TABLE NAME  --and creator  YOUR USER NAME  --if you want to further restrict tables --according to the user name that created it

[sql] From a Sybase Database, how I can get table description ( field names and types)?

Examples related to sql

Examples related to metadata

Examples related to sybase

Examples related to sap-ase

Examples related to isql