Use SQL statements to query MySQL, SQLServer, all Oracle Database names and table names, field names, and mysqlsqlserver

Source: Internet
Author: User

Use SQL statements to query MySQL, SQLServer, all Oracle Database names and table names, field names, and mysqlsqlserver

Query all database names and table names in MySQL

Query all databases

show databases;


Queries all table names in a specified database.

select table_name from information_schema.tables where table_schema='database_name' and table_type='base table';

Query all field names in a specified table

select column_name from information_schema.columns where table_schema='database_name' and table_name='table_name';

Queries all field names and field types in a specified table.

select column_name,data_type from information_schema.columns where table_schema='database_name' and table_name='table_name';

Query all database names and table names in SQLServer

Query all databases

select * from sysdatabases;

Query all table names in the current database

Select * from sysobjects where xtype = 'U'; xtype = 'U': indicates all user tables, xtype = 's': indicates all system tables.

Query all field names in a specified table

select name from syscolumns where id=Object_Id('table_name');

Queries all field names and field types in a specified table.

select sc.name,st.name from syscolumns sc,systypes st where sc.xtype=st.xtype and sc.id in(select id from sysobjects where xtype='U' and name='table_name');

Query all database names and table names in Oracle

Query all databases

Because Oralce does not have a database name and only a tablespace, Oracle does not provide database name query support, but only provides a tablespace name query.

Select * from v $ tablespace; -- query the table space (requires certain permissions)

Query all table names in the current database

select * from user_tables;

Query all field names in a specified table

Select column_name from user_tab_columns where table_name = 'table _ name'; -- the table name must be in uppercase.

Queries all field names and field types in a specified table.

select column_name, data_type from user_tab_columns where table_name = 'table_name';-

Use SQL statements to query SQL statements of MySQL, SQLServer, and all Oracle Database names and table names and field names, which are simple and clear.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.