Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Unanswered: List of column attributes

    Hi,

    How do I get a list of the tables, columns in the tables and the column attributes (like datatype, precision, etc..) in a SyBase database? I to be able to use sql commands to get this information like I can in Oracle.

  2. #2
    Join Date
    Nov 2002
    Posts
    207
    Either you can use select * from syscolumns or the better option would be to see how sp_help pulls the information from different system tables to give the desired output.

    sp_helptext sp_help will give you more details.

  3. #3
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Thanks. I do know about sp_help, but that wont be any help from my application. The syscolumns works perfect thanks! I assume systables would give me the tables?

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Ok I got it. You get the tables from sysobjects and indexes from sysindexes. Thanks!

  5. #5
    Join Date
    May 2004
    Posts
    35
    how bout getting infos bout the primary keys of tables, how do you do that, syscolumns doesn't give that or sysconstraints, em not sure though

  6. #6
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hey dude,

    If you are new to Sybase then easiest way is through ddlgen utility.

    It is available in your $SYABSE/$SYBASE_ASE/bin. The utility guide has infor on the syntax

  7. #7
    Join Date
    May 2004
    Posts
    35
    ok i'm new and i tried ddlgen, it's useful, but right now i don't need it, i wanted to create a program through PB that will not use any outside programs and that will contain select statements that actually do what 'ddlgen' does, in short, a custom made 'ddlgen' type of utility, and the only problem i am experiencing is how to determine.....primary keys through systables...(sigh)

  8. #8
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Hi,

    Here's an excerpt from a script for rebuilding clustered indexes to defrag tables. It gets the DRI bits for indexes.

    select
    @str_foreignkey_dri = isnull( substring('Y', 1, (1-abs(sign( (i.status2 & 1) - 1 ))) ), 'N'),
    @str_primarykey_dri = isnull( substring('Y', 1, (1-abs(sign( (i.status2 & 2) - 2 ))) ), 'N')
    from
    sysindexes
    where
    id = object_id(@table_name)
    and indid = 1

    This will give you the foreign key and primary clauses for tables that use them. If you have defined your primary key indexes using create unique index or create unique clustered index, these bits will not be set. A unique index is not necessarilly the table's primary key, though a primary key constraint would enforce unqiueness.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •