Metadata Extraction Example Analysis of MySQL and Oracle

Preface

Recently I received a task to extract metadata from MySQL and Oracle, which basically means extracting the library, schema, table, field, partition, index, primary key and other information in the library, and then exporting it into Excel.

Because I just started to learn about metadata and didn't know much about it, I wanted to use the almighty Baidu. As a result, after searching around on Baidu, all I got was sql, and I could just use cv. Although I got some data, I had no idea which database or table I was looking for, and the data I got was not what I wanted. I had to go to the official website and read the documents to complete my task.

It is better to teach a man to fish than to give him a fish. I wrote down my experience to provide a reference for friends who have the same doubts as me.

What is metadata

Baidu Encyclopedia: Metadata, also known as intermediary data or relay data, is data about data, mainly information describing data properties, used to support functions such as indicating storage location, historical data, resource search, and file records. Metadata is a kind of electronic catalog. In order to achieve the purpose of cataloging, it is necessary to describe and collect the content or characteristics of the data, thereby achieving the purpose of assisting data retrieval. The Dublin Core Metadata Initiative (DCMI) is an application of metadata. It was a seminar jointly sponsored by the International Library Computer Center (OCLC) and the National Center for Supercomputing Applications (NCSA) in February 1995. 52 librarians and computer experts were invited to jointly develop specifications and create a set of characteristics that describe electronic files on the Internet.

Metadata is information about the organization of data, data domains and their relationships. In short, metadata is data about data.

Reference document address

mysql: https://docs.oracle.com/cd/E17952_01/index.html

Oracle: https://docs.oracle.com/en/database/oracle/oracle-database/index.html

Let’s talk about MySQL first

MySQL's metadata exists in the information_schema library in the form of views, which can only be viewed but not modified.

After entering the document, go directly to the introduction of information_schema , which describes each table and field in detail.

Commonly used queries mysql metadata sql

-- Database SELECT `schema_name` database name, `DEFAULT_CHARACTER_SET_NAME` default character set FROM `SCHEMATA`
 
-- Table SELECT `TABLE_NAME` table name, `TABLE_COMMENT` description, `TABLE_TYPE` table type FROM `TABLES`
 
-- Field SELECT `TABLE_SCHEMA` library name, `TABLE_NAME` table name, `COLUMN_NAME` field name, `COLUMN_COMMENT` field description, `DATA_TYPE` field type, `CHARACTER_MAXIMUM_LENGTH` length, `IS_NULLABLE` whether it is empty FROM `COLUMNS`
 
-- Partition SELECT `TABLE_SCHEMA` database name, `TABLE_NAME` table name, `PARTITION_NAME` partition name, `PARTITION_ORDINAL_POSITION` partition number, `PARTITION_EXPRESSION` partition function expression FROM `PARTITIONS` WHERE partition_name IS NOT NULL 
 
-- Views SELECT * FROM `VIEWS`
 
-- Index SELECT * FROM STATISTICS
 
-- Primary key SELECT * FROM `COLUMNS` WHERE COLUMN_KEY = 'PRI'

Let's talk about Oracle

The metadata in Oracle is in the static data dictionary views.

We cannot access the data dictionary tables directly, but we can access the information in them through data dictionary views. To list the available data dictionary views, query the view DICTIONARY.

There are three types of views in the static data dictionary view: ALL_, DBA_, USER_

• The ALL_ view displays information about all schemas accessible to the current user, including information about objects from other schemas that the current user has access to, and if the current user has permissions or roles that grant access to those objects.
• The DBA_ view displays all relevant information for the entire database. DBA_ views are for administrator use only. Only users with the SELECT ANY DICTIONARY privilege can access them. This privilege is assigned to the DBA role when the system is initially installed.
• The USER_ view displays all information from the current user's schema. No special permissions are required to query these views.

The ALL_, DBA_, and USER_ views corresponding to a single data dictionary table are usually almost identical.

Official website:

Then you can find the corresponding view through the document description.

Query common Oracle metadata sql:

-- Mode SELECT * FROM DBA_TABLESPACES;
 
--table select * 
from user_tables ut LEFT JOIN user_tab_comments utc ON ut.TABLE_NAME = utc.TABLE_NAME;
 
-- Field select t.TABLE_NAME table name, t.COLUMN_NAME description, t.DATA_TYPE type, t.DATA_LENGTH length, t.NULLABLE whether it is empty, t.DATA_DEFAULT default value, s.comments description from user_tab_columns t LEFT JOIN user_col_comments s ON t.COLUMN_NAME = s.COLUMN_NAME WHERE t.TABLE_NAME ='ASSET_BASE_TABLE';
 
-- Partition SELECT * FROM USER_PART_KEY_COLUMNS;
 
-- Index SELECT * FROM USER_INDEXES;
 
-- View SELECT * FROM USER_VIEWS;
 
-- Primary key select cu.* from USER_CONS_COLUMNS cu, USER_CONSTRAINTS au 
where cu.constraint_name = au.constraint_name and au.constraint_type = 'P';

Summarize

This is the end of this article about MySQL and Oracle metadata extraction. For more relevant MySQL Oracle metadata extraction content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!