1. Check if a table exists in the current database schema
select table_name
from user_tables
where table_name = ‘__TABLE_NAME__’
2. Check if a column exists in a table
select column_name as found
from user_tab_cols
where table_name = ‘__TABLE_NAME__’
and column_name = ‘__COLUMN_NAME__’
3. Check if a table contains any data
select 1
from ‘__TABLE_NAME__’
where rownum = 1
4. Query to find the primary key constraint on a table.
SELECT * FROM ALL_CONS_COLUMNS A
JOIN ALL_CONSTRAINTS C
ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = ‘P’
5. Query to find the Foreign key constraint on a table.
SELECT * FROM ALL_CONS_COLUMNS A
JOIN ALL_CONSTRAINTS C
ON A.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE C.TABLE_NAME = <your table> AND C.CONSTRAINT_TYPE = ‘R’
6. Query to find all the constraints on a table.
SELECT *
FROM SYS.ALL_CONSTRAINTS A , SYS.ALL_CONS_COLUMNS B
WHERE S.OWNER=<owner_name> AND S.TABLE_NAME=<table_name> AND A.CONSTRAINT_NAME=B.CONSTRAINT_NAME