Shulou(Shulou.com)06/02 Report--

How to carry out MySQL and Oracle metadata extraction analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Preface

Recently received a task is to extract mysql and Oracle metadata, roughly in the library, schema, tables, fields, partitions, indexes, primary keys and other information extracted, and then led to excel.

Because I just came into contact with metadata and didn't know anything about it, I wanted to use the omnipotent Baidu. As a result, Baidu told me sql and cv directly. Although I got some data, I don't know which database and which table to check, and the data I got is not what I want. I have to go to the official website to read the documents to complete my task.

It is better to teach people to fish than to teach people to fish. Write down the experience and give a reference to the confused friends like me.

What is metadata?

Baidu encyclopedia: metadata (Metadata), also known as intermediary data and metadata, is data describing data (data about data), mainly information describing data attributes (property), which is used to support functions such as indicating storage location, historical data, resource search, file records and so on. Metadata is a kind of electronic catalog. in order to achieve the purpose of cataloging, we must describe and collect the content or characteristics of the data, and then achieve the purpose of assisting data retrieval. Dublin Core set (Dublin Core Metadata Initiative,DCMI) is an application of metadata, which was jointly sponsored by the International Library computer Center (OCLC) and the National supercomputing Tencent App Center (National Center for Supercomputing Applications,NCSA) in February 1995. 52 librarians and computer experts were invited to work together to develop specifications and create a set to describe the characteristics of electronic documents on the Internet.

Metadata is information about the organization of data, data fields 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 start with MySQL.

The metadata of mysql exists in the information_schema library in the form of views, which can only be viewed and cannot be modified.

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

Commonly used query mysql metadata sql

-- Database SELECT `SCHEMATA` 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_ comet` field description, `DATA_ TYPE` field type, `CHARACTER_MAXIMUM_ NAME` length, `IS_ NULLABLE` whether FROM `COLUMNS`-partition SELECT `TABLE_ SCHEMA` library name, `TABLE_ NAME` table name `NAME` partition name, `PARTITION_ORDINAL_ POSITION` partition number, `PARTITION_ express function expression FROM `PARTITIONS` WHERE partition_name IS NOT NULL-view SELECT * FROM `VIEWS`-index SELECT * FROM STATISTICS-primary key SELECT * FROM `COLUMNS` WHERE COLUMN_KEY = 'PRI' again Oracle

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

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

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

The ALL_ view displays all the information about the current user, including the mode of the current user from objects in other schemas, as well as access to information, if the current user has permissions or role authorization to access these objects.

The DBA_ view displays all relevant information in the entire database. The DBA_ view is for administrators only. Only users with SELECT ANY DICTIONARY privileges can access them. This permission DBA is assigned to roles when the system is initially installed.

The USER_ view displays all the information from the current user's schema. No special permissions are required to query these views.

ALL_,DBA_ and USER_ are usually almost the same for a single data dictionary table view.

Official website:

You can then find the corresponding view through the documentation.

Query commonly used Oracle metadata sql:

-- pattern 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, whether t.NULLABLE 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 = 'playing. Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.