Understand horrible database

I never got fresh database.it's my bad luck so I always deal with old database which are created by someone who already left the company.Due to this situation,I have wasted so much time while developing or finding bug.Today I got some tips on Google. Hopefully It will help me in future.

1) Create E-R diagram

2) Examine each table and column make sure the meaning of what it stores.

3) Examine each relationship and make sure how the tables relate to one another
or

1) Understand the project flow from front end code or any document

2) find master table and it's transaction table whose having same column ID name

First I look up for the "Master Table", then, with pen and paper, I start mapping the relations with other tables, after that, if there's some app code to look at I start making some raw sketches on how the data flows.See if the option of a Knowledge Transfer session is available to you, and if so, take full advantage of it.


--Find multiple table by using column name

SELECT * FROM ALL_TAB_COLUMNS 

 WHERE COLUMN_NAME LIKE '%TASK%' 

 AND owner = 'database_name';


--Find Column named like 'blah' in a specific table 

SELECT O.NAME, O.ID, C.NAME, O.XTYPE 

FROM SYSOBJECTS O LEFT JOIN SYSCOLUMNS C ON O.ID=C.ID 

WHERE C.NAME LIKE '%SearchFor%' 

AND O.XTYPE IN ('U','V') 

AND O.Name like '%TableName%' ORDER by O.Name


--Find all Columns in DB with name like 'blah' 

 SELECT O.NAME, O.ID, C.NAME, O.XTYPE 

FROM SYSOBJECTS O LEFT JOIN SYSCOLUMNS C ON O.ID=C.ID 

WHERE C.NAME LIKE '%SearchFor%' 

AND O.XTYPE IN ('U','V') ORDER by O.Name

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH 

from all_tab_columns a,USER_ALL_TABLES u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like ‘empid%’  order by DATA_LENGTH desc;

yes we can do a similar search in all views. Here is your query. 

Hope this helps you.. Let me know.


select a.table_name, column_name,DATA_TYPE,DATA_LENGTH 

from all_tab_columns a,ALL_CATALOG u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like upper(‘latitude%’)
and u.table_type=’VIEW’
and u.owner=’YOUR_OWNER’
order by DATA_LENGTH desc;


Find child Table

SELECT table_name FROM ALL_CONSTRAINTS WHERE constraint_type = 'R' -- "Referential integrity" AND r_constraint_name IN ( SELECT constraint_name FROM ALL_CONSTRAINTS WHERE table_name = '[TableName]' AND constraint_type IN ('U', 'P') -- "Unique" or "Primary key" )


select table_name, constraint_name, status, owner from all_constraints where r_owner = :r_owner and constraint_type = 'R' and r_constraint_name in ( select constraint_name from all_constraints where constraint_type in ('P', 'U') and table_name = :r_table_name and owner = :r_owner ) order by table_name, constraint_name