Data dictionary queries

Data dictionary queries

1.      Check if a table exists in the current database schema

A simple query that can be used to check if a table exists before you create it. This way you can make your create table script rerunnable. Just replace table_name with actual table you want to check. This query will check if table exists for current user (from where the query is executed).

SELECT table_name   FROM user_tables  WHERE table_name = 'TABLE_NAME';

2.      Check if a column exists in a table

Simple query to check if a particular column exists in table. Useful when you tries to add new column in table using ALTER TABLE statement, you might wanna check if column already exists before adding one.

SELECT column_name AS FOUND   FROM user_tab_cols  WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';

3.      Showing the table structure

This query gives you the DDL statement for any table. Notice we have pass ‘TABLE’ as first parameter. This query can be generalized to get DDL statement of any database object. For example to get DDL for a view just replace first argument with ‘VIEW’ and second with your view name and so.

SELECT DBMS_METADATA.get_ddl ('TABLE', 'TABLE_NAME', 'USER_NAME') FROM DUAL;

4.      Getting current schema

Yet another query to get current schema name.

SELECT SYS_CONTEXT ('userenv', 'current_schema') FROM DUAL;

5.      Changing current schema

Yet another query to change the current schema. Useful when your script is expected to run under certain user but is actually executed by other user. It is always safe to set the current user to what your script expects.

ALTER SESSION SET CURRENT_SCHEMA = new_schema;