Atitit. design differences between Database-catalog and schema and obtaining all database tables in actual use of java jdbc p.

Atitit. database-the difference between catalog and schema design and the actual use of obtaining all database tables in the database java jdbc p. atitit. database ----- differences between catalog and schema design and how to obtain all database tables in the database using java jdbc php c #. net

1.-differences between catalog and schema design 1

2. methods for obtaining database metadata 2

2. 1. directly read the information_schema of mysql, SysDatabases 3 of mssql

2. use common interfaces such as jdbc 3

. Use a separate driver interface provided by the database, such as mysql_list_dbs 3 of mysql.

3. get the list of all databases in mysql. getCatalogs 3

3. 1. traverse all databases in the database 3

3. 2. Php version mysql_list_dbs () 4

3. 3 .. Net version SysDatabases 4

3. 4 .. Net version // use OleDbConnection's GetOleDbSchemaTable to obtain the database structure 4

4. get all tables of a database getTables 4

4. 1. Php implementation mysql_list_tables 5

4. 2 .. Read SysObjects table 5 from version N et

5. refer to 5

1.-differences between catalog and schema designs

According to the SQL standard, both Catalog and Schema are abstract concepts in the SQL environment. they can be understood as a layer in a container or database object namespace, it is mainly used to solve naming conflicts. Conceptually, a database system contains multiple Catalog S, each of which contains 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 belong to a Catalog, so that we can get the fully qualified name of the database object to solve the naming conflict problem; for example, the fully qualified name of the database object table can be expressed as the Catalog name. schema name. table name. Note that the SQL standard does not require that the fully qualified names of each database object be unique, just like domain names, each IP address can have multiple domain names.

From the implementation point of view, the support and implementation methods for Catalog and Schema vary widely in various database systems. for specific problems, refer to the specific product manual, A simple and common implementation method is to use the database name as the Catalog name and the user name as the Schema name. for details, see the following table:

Table 1 common databases

Supplier	Catalog support	Schema support
Oracle	Not supported	Oracle User ID
MySQL	Not supported	Database name
Ms SQL Server	Database name	The object owner name, which has changed since version 2005.
DB2	The Catalog part is omitted when the database object is specified.	Catalog owner name
Sybase	Database name	Database owner name
Informix	Not supported	No
PointBase	Not supported	Database name

Author: Nickname: old Wow's Paw (full name: Attilax akbar al rapanui Attila AKBA Arla Panui) Name: Ai long, EMAIL: 1466519819@qq.com

Reprinted please indicate Source: http://www.cnblogs.com/attilax/

The following describes how to read table information from the database.

It depends on a DatabaseMetaData class. this object can be obtained from a database connection. With it, everything is fine. you just need to know what to ask:

DatabaseMetaData databaseMetaData = conn. getMetaData ();

// Obtain all the ResultSet tableSet = databaseMetaData. getTables (null, "%", "%", new String [] {"TABLE "});

// Obtain the tableName table column information ResultSet columnSet = databaseMetaData. getColumns (null, "%", tableName, "% ");

2. methods for obtaining database metadata: 2.1. directly read the information_schema of the meta-table mysql, SysDatabases 2.2 of mssql. use common interfaces such as jdbc 2.3. use a separate driver interface provided by the database, such as mysql's mysql_list_dbs 3. obtains the list of all databases in mysql. getCatalogs 3.1. traverse all databases in the database

[

{"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 "}

]

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. obtain all database names: (1) Select Name FROM Master. dbo. SysDatabases orDER BY Name

3.4 .. Net version // use OleDbConnection's GetOleDbSchemaTable to obtain the database structure 4. get all the tables of 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 tables in the MySQL database

4.2 .. Read the SysObjects table in version N et.

2. obtain all table names: (1) Select Name FROM SysObjects Where XType = 'U' orDER BY Name

5. Reference

Asp.net: obtain all the database names of SQL, all table names, all field names, column descriptions-XMM_1030 columns-blog channel-CSDN.NET.htm

. NET method for obtaining the names of all tables in the database (reprinted)-guoxuefeng-blog. htm

Brief History of ODBC, OLEDB, ADO, and ADO. Net evolution-BobLiu-bo .htm