Atitit. The database-catalog differs from the schema design and is used in practice to get all the library tables of the database Java JDBC P.

Atitit. Database-----The design difference between catalog and schema and use in practice get database all library tables java jdbc PHP c#.net

1. The difference between-catalog and schema design 1

2. Several ways to get database meta-Information 2

2.1. Direct read meta table MySQL INFORMATION_SCHEMA, MSSQL's sysdatabases 3

2.2. Using common interfaces such as JDBC 3

2.3. Use a separate driver interface from the database, such as MySQL Mysql_list_dbs 3

3. Get database list of all databases in MySQL GetCatalogs 3

3.1. Traverse database all databases 3

3.2. PHP version Mysql_list_dbs () 4

3.3. 。 Net version sysdatabases 4

3.4. 。 Net Version//Use OleDbConnection's getoledbschematable to get the structure of the database 4

4. Get all tables for a database Gettables 4

4.1. PHP Implementation Mysql_list_tables 5

4.2. 。 N et version read SysObjects table 5

5. Reference 5

1. The difference between-catalog and schema design

According to the SQL standard, both the Catalog and Schema are abstract concepts in a SQL environment and can be understood as a hierarchy in a container or database object namespace, primarily to address naming conflicts. Conceptually, a database system contains multiple catalog, each containing multiple schemas, and each schema contains multiple database objects (tables, views, fields, etc.), in turn a database object must belong to a schema, and the schema must be It belongs to a catalog so that we can get the fully qualified name of the database object to resolve the naming conflict, for example, the fully qualified name of the database object table can be represented as: Catalog name. The Schema name. The table name. It is also important to note that the SQL standard does not require that the fully qualified name of each database object be unique, like a domain name, and that each IP address can have multiple domain names if you like.

From the point of view of implementation, the various database systems to the catalog and Schema support and implementation of a variety of different, specific problems need to refer to specific product specifications, relatively simple and common implementation is to use the database name as the Catalog name, using the user name as the Schema name, Refer to the following table for details:

Table 1 Common databases

/tr> tr>

Vendor	Catalog support	Schema support
Oracle	Not supported	Oracle User ID
MySQL	Does not support	Database name
MS sql Server	Database name	Object is the main name, version 2005 has been changed
DB2	Specifying a database object when the catalog section is omitted	Catalog main name
Sybase	Database name	Database owner name
Informix	Not supported	Not required
Pointbase	Not supported	Database name

Author:: Nickname: Old Wow's paw (full name:: Attilax Akbar al Rapanui Attilaksachanui) Kanji name: Ayron, email:1466519819@qq.com

Reprint please indicate source: http://www.cnblogs.com/attilax/

Here's how to read the table information from the database.

Here, depending on a class DatabaseMetaData, this object can be obtained from a database connection. With it all gone, wondering what to ask it:

DatabaseMetaData DatabaseMetaData = Conn.getmetadata ();

Get all tables ResultSet Tableset = Databasemetadata.gettables (null, "%", "%", New string[]{"Table"});

Get tableName table column information ResultSet columnset = Databasemetadata.getcolumns (null, "%", tableName, "%");

2. Ways to get database meta-information

2.1. Direct read meta-table MySQL INFORMATION_SCHEMA, MSSQL's sysdatabases

2.2. Use common interfaces such as JDBC

2.3. Use a separate driver interface from the database, such as MySQL Mysql_list_dbs

3. Get a list of all databases in MySQL database getcatalogs

3.1. Traverse database all databases

[

{"Table_cat": "Information_schema"},

{"Table_cat": "8KBL"},

{"Table_cat": "Atiposdb"},

{"Table_cat": "Cyar"},

{"Table_cat": "Ecmdb"},

{"Table_cat": "Hxtaxi"},

{"Table_cat": "Iwbm2"},

{"Table_cat": "Iwmshop"},

{"Table_cat": "Iwmshopnow"},

{"Table_cat": "LimeSurvey"},

{"Table_cat": "MySQL"},

{"Table_cat": "Performance_schema"},

{"Table_cat": "Shopedb"},

{"Table_cat": "SHOPNC"},

{"Table_cat": "Test"},

{"Table_cat": "Timerdb"},

{"Table_cat": "Vod2"},

{"Table_cat": "Wechatdb"},

{"Table_cat": "WordPress"},

{"Table_cat": "Wxb_site_new"},

{"Table_cat": "Wxmiqi"},

{"Table_cat": "Zuche5"}

]

The GetSchemas is empty.

3.2. PHP version Mysql_list_dbs ()

$dbs = Mysql_list_dbs (); Call the Mysql_list_dbs function

3.3.. Net version sysdatabases

1. Get all database names: (1), Select name from Master.dbo.SysDatabases OrDER by Name

3.4.. Net Version//Use OleDbConnection's getoledbschematable to get the structure of the database

4. Get all the tables for a database gettables

DatabaseMetaData Dbmd = dbx. getconnection (). GetMetaData ();

Databasemetadata.getcolumns (Localcatalog, Localschema,

LocalTableName, NULL);

ResultSet rs = Dbmd. Gettables ("Atiposdb", "%", "%", new string[]{"TABLE"});

[

{

"table_name": "Applications",

"REMARKS": "",

"Table_type": "TABLE",

"Table_schem": null,

"Table_cat": "Atiposdb"

},

{

"table_name": "Attribute",

"REMARKS": "",

"Table_type": "TABLE",

"Table_schem": null,

"Table_cat": "Atiposdb"

},

4.1. PHP Implementation Mysql_list_tables

(PHP 3, PHP 4, PHP 5)

Mysql_list_tables--List the tables in the MySQL database

4.2.. N et version read SysObjects table

2. Get all table names: (1), Select name from SysObjects Where xtype= ' U ' OrDER by Name

5. Reference

ASP. NET get SQL all database names, all table names, all field names, column descriptions-xmm_1030 's column-Blog channel-CSDN.NET.htm

. NET methods for obtaining all table names in a database (reprinted)-Guoxuefeng-blog Park. htm

ODBC, OLE DB, ADO, ADO. Net-A brief history of evolution-Bobliu-blog Park. htm

