How to get table info?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: How to get table info?

  1. #1
    Join Date
    Sep 2001
    Posts
    99

    How to get table info?

    I can use this sql get the infomation of the table at sql server2000
    select a.colorder colorder,a.name column_name,
    (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'Y'else 'N' end) N'identity',
    (case when (select count(*) from sysobjects where (name in (select name from sysindexes where (id = a.id) and (indid in (select indid from sysindexkeys where (id = a.id) and (colid in (select colid from syscolumns where (id = a.id) and (name = a.name))))))) and (xtype = 'PK'))>0 then 'Y' else 'N' end) primary_key,
    (case when (select count(*) from sysobjects where (name in (select name from sysindexes where (id = a.id) and (indid in (select indid from sysindexkeys where (id = a.id) and (colid in (select colid from syscolumns where (id = a.id) and (name = a.name))))))) and (xtype = 'UQ'))>0 then 'Y' else 'N' end) N'unique',
    b.name data_type,
    a.length data_length,
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as dotpos,
    (case when a.isnullable=1 then 'Y'else 'N' end) nullable,
    isnull(e.text,'') data_default,isnull(g.[value],'') comments
    from syscolumns a left join systypes b on a.xtype=b.xusertype
    inner join sysobjects d on a.id=d.id and d.xtype='U'
    and d.name<>'dtproperties' and d.name = 'ct_customer'
    left join syscomments e on a.cdefault=e.id
    left join sysproperties g on a.id=g.id AND a.colid = g.smallid
    order by object_name(a.id),a.colorder
    ------------------------------------------------

    I can get the info about follows
    colorder column_name identity primary_key unique data_type data_length dotpos nullable data_default comments

    but now I want use oracle,how can I get oracle table infomation ?I want to know these infomation except identity column.


    now I my sql is this
    select sys.user_tab_columns.column_id,sys.user_tab_columns.column_name,data_type,data_length,nullable,data_ default,comments from sys.user_tab_columns,sys.user_col_comments where sys.user_tab_columns.column_name = sys.user_col_comments.column_name and sys.user_tab_columns.table_name = sys.user_col_comments.table_name and sys.user_tab_columns.table_name='TRY1' order by sys.user_tab_columns.column_id

    but I can't get the data_length,dotpos,private_key,unique£¬how can I do this ?
    help me ,thanks
    ocean

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You need to familiarize yourself with the other static data dictionary views -- they are documented in the Reference

    Of particular interest will be ...

    dba_tables
    dba_indexes
    dba_tab_columns
    dba_ind_columns
    dba_constraints
    dba_cons_columns
    dba_partitions
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2002
    Posts
    70
    Query the following tables:

    USER_CONSTRAINTS - for constraints on the table
    USER_CONS_COLUMNS - constraints on the particular columns of a table
    USER_IND_COLUMNS - indexes on the particular columns of a table

    And to get description of the table just type DESC Table_name at SQL prompt
    ________________
    ShanDJ

  4. #4
    Join Date
    Sep 2001
    Posts
    99

    can you give me a sample?

    I'm not familiar with oracle pl/sql and oracle system views. can you give me the sample.
    ocean

  5. #5
    Join Date
    May 2001
    Location
    Maryland, USA
    Posts
    409

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    select * From user_Tables;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Sep 2001
    Posts
    99

    god,could you give me an intact sql example

    I know which system view includes these infomation, but I can't write a sql achieve my requires.
    I want who can give me an intact sql.
    help me,help me ,I need it urgent . I have no time to learn the system view and other.
    please,thank you very much.
    ocean

  8. #8
    Join Date
    Feb 2003
    Posts
    85

    Re: god,could you give me an intact sql example

    Originally posted by oceanju
    I know which system view includes these infomation, but I can't write a sql achieve my requires.
    I want who can give me an intact sql.
    help me,help me ,I need it urgent . I have no time to learn the system view and other.
    please,thank you very much.
    fu** off then

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: god,could you give me an intact sql example

    Originally posted by oceanju
    I need it urgent . I have no time to learn . . .
    OK, you're in a bind. Whoever got you there should be prepared to shell out for professional help - $1000 should do it.

    If you think we're unhelpful, read this http://www.catb.org/~esr/faqs/smart-questions.html
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  10. #10
    Join Date
    Mar 2003
    Location
    Dallas, TX
    Posts
    12
    Try this:

    select column_name, data_type, decode(data_precision, null,
    data_length, data_precision), nvl(nullable, 'N'), num_distinct,
    num_nulls, nvl(data_scale, 0)
    from dba_tab_columns
    where table_name = 'EMP'
    and owner = 'SCOTT'
    order by column_id;

    SELECT owner, index_name,
    uniqueness from dba_indexes
    where owner ='SCOTT'
    and table_name = 'EMP'
    order by owner, index_name;

    Also I posted an integrated set of GUI tools to manage, monitor and tune the Oracle database. One of the many functions is to display table and index information. The link is http://www.barsoft.net
    Michel Bartov
    http://www.barsoft.net/

Posting Permissions

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



Click Here to Expand Forum to Full Width