Obtain information such as Oracle table fields, table names, and primary keys.

Source: Internet
Author: User
Get table name:

 Oracle's user_talbes is used to record user table information.

Select * From user_tables

 Obtain the fields of a table:

User_tab_cols records the column information of the User table. The following is what others wrote:

Select user_tab_cols.table_name as table name, user_tab_cols.column_name as column name, user_tab_cols.data_type as data type, user_tab_cols.data_length as length, blank as, user_tab_cols.column_id as column no, user_col_comments.comments as remarks from user_tab_cols inner join user_col_comments on user_col_comments.table_name = user_tab_cols.table_name and user_col_comments.column_name = user_tab_cols.column_name

 

How to Get table comments from Oracle and

User_tab_comments; Table comment

User_col_comments; Table field comment

The preceding two tables can only obtain comments of their own tables. To access tables of other users that you can access, you need:

All_tab_comments; Table comment

All_col_comments; Table field comment

Of course, you can use

Dba_tab_comments; Table comment

Dba_col_comments; Table field comment

It is best to specify the owner condition for DBA * And all. User * does not have this field

User_tab_comments; Table comment

User_col_comments; Table field comment

The preceding two tables can only obtain comments of their own tables. To access tables of other users that you can access, you need:

All_tab_comments; Table comment

All_col_comments; Table field comment

Of course, you can use

Dba_tab_comments; Table comment

Dba_col_comments; Table field comment

It is best to specify the owner condition for DBA * And all. User * does not have this field

 

 

SQL statement label for obtaining all fields, primary keys, and foreign keys in Oracle and sqlserver: primary key foreign key SQL
Recently, the prototype system for social network analysis needs to read the fields and foreign key information of tables in different databases. It takes a lot of effort to implement these functions.
ORACLE:
Queries the field name, type, precision, length, and whether the field is empty in a table.
Select column_name, data_type, data_precision, data_scale, nullable
From user_tab_columns
Where table_name = 'yourtablename'
Query the names of primary key fields in a table.
Select col. column_name
From user_constraints con, user_cons_columns col
Where con. constraint_name = col. constraint_name
And Con. constraint_type = 'P'
And col. table_name = 'yourtablename'
Queries the name of a foreign key field in a table, the name of the referenced table, and the name of the applied field.
Select distinct (Col. column_name), R. table_name, R. column_name
From
User_constraints con,
User_cons_columns Col,
(Select t2.table _ name, t2.column _ name, t1.r _ constraint_name
From user_constraints T1, user_cons_columns T2
Where t1.r _ constraint_name = t2.constraint _ name
And t1.table _ name = 'yourtablename'
) R
Where con. constraint_name = col. constraint_name
And Con. r_constraint_name = R. r_constraint_name
And Con. table_name = 'yourtablename'

Sqlserver implementation:
Field:
Select C. Name, T. Name, C. xprec, C. XScale, C. isnullable
From policypes T, syscolumns C
Where T. xtype = C. xtype
And C. ID = (select ID from sysobjects where name = 'yourtablename ')
Order by C. colid

Primary Key (refer to the SQL Server Stored Procedure sp_pkeys ):
Select column_name = convert (sysname, C. Name)
From
Sysindexes I, syscolumns C, sysobjects o
Where o. ID = object_id ('[yourtablename]')
And O. ID = C. ID
And O. ID = I. ID
And (I. Status & 0x800) = 0x800
And (C. Name = index_col ('[yourtablename]', I. indid, 1) or
C. Name = index_col ('[yourtablename]', I. indid, 2) or
C. Name = index_col ('[yourtablename]', I. indid, 3) or
C. Name = index_col ('[yourtablename]', I. indid, 4) or
C. Name = index_col ('[yourtablename]', I. indid, 5) or
C. Name = index_col ('[yourtablename]', I. indid, 6) or
C. Name = index_col ('[yourtablename]', I. indid, 7) or
C. Name = index_col ('[yourtablename]', I. indid, 8) or
C. Name = index_col ('[yourtablename]', I. indid, 9) or
C. Name = index_col ('[yourtablename]', I. indid, 10) or
C. Name = index_col ('[yourtablename]', I. indid, 11) or
C. Name = index_col ('[yourtablename]', I. indid, 12) or
C. Name = index_col ('[yourtablename]', I. indid, 13) or
C. Name = index_col ('[yourtablename]', I. indid, 14) or
C. Name = index_col ('[yourtablename]', I. indid, 15) or
C. Name = index_col ('[yourtablename]', I. indid, 16)
)

Foreign key:
Select t1.name, t2.rtablename, t2.name
From
(Select col. Name, F. constid as temp
From syscolumns Col, sysforeignkeys F
Where F. fkeyid = col. ID
And F. fkey = col. colid
And F. constid in
(Select distinct (ID)
From sysobjects
Where object_name (parent_obj) = 'yourtablename'
And xtype = 'F'
)
) As T1,
(Select object_name (F. rkeyid) as rtablename, col. Name, F. constid as temp
From syscolumns Col, sysforeignkeys F
Where F. rkeyid = col. ID
And F. rkey = col. colid
And F. constid in
(Select distinct (ID)
From sysobjects
Where object_name (parent_obj) = 'yourtablename'
And xtype = 'F'
)
) As T2
Where t1.temp = t2.temp

 

 

These are all accumulated at ordinary times. You can use them as needed.

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.