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
--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'
--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.
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
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
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
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