Finding out the metadata and joins

database-designoracle

Suppose I have a database with a large number of tables.

Is there any command that I can type in PL/SQL which display only the field names of a given table?
Given any table is there any command that I can type that would show all the relations that it has with other tables?

For example , I have a table "TAB_1" , can I type a command that would show all the tables related to and for any table "TAB_2" that it is related to it would show which field in "TAB_1" corresponds to which field in "TAB_2 " .

Best Answer

You can query the system views and look at metadata.

SYS.ALL_OBJECTS gives you a list of all the objects that are accessible by the user
SYS.DBA_OBJECTS is similar and list all the object in the database
SYS.USER_OBJECTS lists all the object owned by the user

You will get a list of tables using this view:

SYS.ALL_TABLES = tables accessible by the user
SYS.DBA_TABLES = all tables in the database
SYS.USER_TABLES = tables owned by the user.

For a list of columns, you can join the TABLES list with the column views:

Table Columns SYS.ALL_TAB_COLUMNS, SYS.DBA_TAB_COLUMNS and SYS.USER_TAB_COLUMNS

There are similar views for:

Constraints = SYS.ALL_CONSTRAINTS, SYS.DBA_CONSTRAINTS and SYS.USER_CONSTRAINTS
Columns used by constraints = SYS.ALL_CONS_COLUMNS, SYS.DBA_CONS_COLUMNS and SYS.USER_CONS_COLUMNS

You can then join some of them and write such a query for a list of columns involved in referential integrity contraints:

SELECT ...
FROM SYS.ALL_CONS_COLUMNS col
JOIN SYS.ALL_CONSTRAINTS con con ON col.owner = con.owner AND col.constraint_name = con.constraint_name
JOIN SYS.ALL_CONS_COLUMNS fk on fk.owner = con.owner and fk.r_constraint_name = con.constraint_name
WHERE con.constraint_type = 'R' AND col.table_name = '...'

With R being (referential integrity) for constraint_type.

Related Solutions

Mysql – Multiple left joins or subquery

This is my best shot without seeing any table structures

SELECT
    user_runs.userID,
    user_runs.runID,
    `createdBy`,
    `username`,
    DATE_FORMAT(`dateCreated`,'%d-%m-%Y') `dateCreated`, 
    COUNT(DISTINCT runFavouriteID) AS favourite,
    DATE_FORMAT(MAX(`runDate`),'%d-%m-%Y') AS `lastRan`,
    MIN(`runTime`) AS `fastestTime`
FROM
    (SELECT * FROM `user_runs` WHERE userID=4) user_runs
    INNER JOIN `runs` ON user_runs.runID = runs.runID
    INNER JOIN `users` ON users.userID = runs.createdBy
    LEFT OUTER JOIN `user_run_times` ON user_run_times.runID = user_runs.runID 
    LEFT OUTER JOIN `run_favourites` ON run_favourites.runID = user_runs.runID
GROUP BY
    user_runs.runID,`createdBy`,`username`,`dateCreated`;

You will need these indexes

ALTER TABLE user_runs ADD INDEX userID_runID_ndx (userID,runID);
ALTER TABLE runs      ADD INDEX runID_createdBy_ndx (runID,createdBy);
ALTER TABLE runs      ADD INDEX createdBy_runID_ndx (createdBy,runID);

Mysql – Finding violations of the symmetry constraint

To find the rows, use a left outer join:

select 
    a.Friend1ID, a.Friend2ID, b.Friend1ID, b.Friend2ID 
from
    Friends a left join Friends b 
        on (a.Friend1ID=b.Friend2ID and a.Friend2ID=b.Friend1ID)
where 
    b.friend1ID IS NULL ;

Best Answer

Related Solutions

Mysql – Multiple left joins or subquery

Mysql – Finding violations of the symmetry constraint

Related Question