How to get table info?

[oceanju]

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

[slimdave]

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

[shandj]

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

[oceanju]

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

[patel_dil]

Sample of what? Did you start reading the manuals?

Start here at
http://otn.oracle.com/pls/tahiti/tahiti.homepage

and

http://download-west.oracle.com/docs...a77069/toc.htm

and
http://otn.oracle.com/pls/tahiti/tahiti.catalog_views

HTH

[marist89]

select * From user_Tables;

[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.

[guru_heaven]

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

[DaPi]

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

[mbartov]

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