loading..
Русский    English
15:30

INFORMATION_SCHEMA and Oracle

The information schema (INFORMATION_SCHEMA) is a standard representation of metadata in SQL. Historically, each developer of relational DBMS provided system tables that contained meta-information in particular names of tables, columns, data types, constraints, etc. Structure of system tables can be changed between versions of the product. However, the support of the information schema makes it possible to change the structure of system tables without changing the access to metadata. Another advantage of using INFORMATION_SCHEMA is that requests for metadata do not depend on the DBMS used.

Among the lead manufacturers, perhaps, only Oracle does not support INFORMATION_SCHEMA. To be fair, it should be said that Oracle provides the ability to use system views instead of directly accessing system tables, which also allows them to safely change the structure of system tables.

Here are some typical queries given in the standard style and for Oracle.

1. List of database tables (for current DB)

Standard

Console
Execute
  1. SELECT table_name FROM information_schema.TABLES;

Oracle

  1. SELECT table_name FROM all_tables WHERE owner='SCHEMA NAME';


2. List of columns of the specified table (for example, PC)

Standard

Console
Execute
  1. SELECT column_name FROM information_schema.COLUMNS WHERE table_name='PC';

Oracle

  1. SELECT column_name FROM all_tab_columns WHERE table_name='PC';

3. Data type and data size of the specified character column of the table (the model column of PC table)

Standard

Console
Execute
  1. SELECT column_name, data_type, CHARACTER_MAXIMUM_LENGTH
  2. FROM information_schema.COLUMNS WHERE table_name='PC' AND column_name = 'model';

Oracle

  1. SELECT column_name,data_type,data_length FROM all_tab_columns
  2. WHERE table_name='PC' AND column_name='MODEL';

4. The name of the primary key constraint of PC table

Standard

Console
Execute
  1. SELECT table_name,constraint_name  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
  2. WHERE table_name='PC' AND CONSTRAINT_TYPE='Primary key';

Oracle

  1. SELECT table_name,constraint_name FROM all_constraints
  2. WHERE table_name='PC' AND constraint_type='P';

5. List of columns that make up the primary key of the Utb table (composite key)

Standard

Console
Execute
  1. SELECT table_name, column_name 
  2. FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE table_name='utb'
  3. AND constraint_name=(SELECT constraint_name FROM INFORMATION_SCHEMA.table_constraints
  4. WHERE table_name='utb' AND CONSTRAINT_TYPE='Primary key');

Here, in the outer query, we get the columns which are mentioned in the constraints. After that we choose those of them that have constraint name matching the name of primary key constraint for the same table. In principle, condition table_name = 'utb' of the main query is redundant for getting the correct answer, because the constraint name is unique for the entire database. However, early filtering significantly speeds up the execution of the query.

Oracle

  1. SELECT table_name, column_name FROM all_ind_columns WHERE table_name='UTB'
  2. AND index_name=(SELECT constraint_name FROM all_constraints
  3. WHERE table_name='UTB' AND constraint_type='P');

Bookmark and Share
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100