-
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
-
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
-
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
-
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
-
-
select * From user_Tables;
Jeff Hunter
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|