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

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.