Friday, 26 May 2017

undefined undefined
Data dictionary queries

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



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



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;


Getting current schema
Yet another query to get current schema name.

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


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;

Related Posts:

  • Oracle date and time function related queries Date / Time related queries Get the first day of the month Quickly returns the first day of current month. Instead of current month you want to find first day of month where a date falls, replace SYSDATE with any date colu… Read More
  • Normalization In SQL Normalization In SQL,MY SQL and Oracle What is normalization ? Defination : Normalization is the process of efficiently organizing data in a database. There are two goals of the normalization process:  1. eliminat… Read More
  • Most Important Data dictionary Queries.. Data dictionary queries 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… Read More
  • SQL Functions(ddl,dml,dcl & tcl) with Examples There are 4 types of sql functions 1.DML 2.DDL 3.DCL 4.TCL Now lets see one by one 1.DML DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in databa… Read More
  • Database administration queries Database administration queries Database version informationReturns the Oracle database version. SELECT * FROM v$version; Database default information Some system default information. SELECT username,profile,default_ta… Read More

0 comments:

Post a Comment

Copyright © 2025 ORACLE-FORU - SQL, PL/SQL and ORACLE D2K(FORMS AND REPORTS) collections | Powered by Blogger
Design by N.Design Studio | Blogger Theme by NewBloggerThemes.com