使用SQL查询所有数据库名、表名和表字段名(Use SQL to query all database names, table names, and table field names)

使用SQL查询所有数据库名、表名和表字段名

MySQL中查询所有数据库名和表名

1.查询所有数据库

show databases;

2.查询指定数据库中所有表名

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

3.查询指定表中的所有字段名(ORDER BY ordinal_position,加入此排序可以按表设计字段返回顺序,不加则按字母排序)

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

4.查询指定表中的所有字段名和字段类型

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

SQLServer中查询所有数据库名和表名

1.查询所有数据库

select * from sysdatabases;

2.查询当前数据库中所有表名

select * from sysobjects where xtype='U';

xtype=’U’:表示所有用户表,xtype=’S’:表示所有系统表。

3.查询指定表中的所有字段名

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

4.查询指定表中的所有字段名和字段类型

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');

Oracle中查询所有数据库名和表名

1.查询所有数据库
由于Oralce没有库名,只有表空间,所以Oracle没有提供数据库名称查询支持,只提供了表空间名称查询。

select * from v$tablespace;--查询表空间(需要一定权限)

2.查询当前数据库中所有表名

select * from user_tables;

3.查询指定表中的所有字段名

select column_name from user_tab_columns where table_name = 'table_name';--表名要全大写

4.查询指定表中的所有字段名和字段类型

select column_name, data_type from user_tab_columns where table_name = 'table_name';--表名要全大写
————————

Use SQL to query all database names, table names, and table field names

Query all database names and table names in MySQL

1. Query all databases

show databases;

2. Query all table names in the specified database

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

3. Query all field names in the specified table (order by ordinal_position. Add this sort to design the return order of fields according to the table, and if not, sort them alphabetically)

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

4. Query all field names and field types in the 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 SQL Server

1. Query all databases

select * from sysdatabases;

2. Query all table names in the current database

select * from sysobjects where xtype='U';

Xtype =’u ‘: indicates all user tables, and xtype =’s’: indicates all system tables.

3. Query all field names in the specified table

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

4. Query all field names and field types in the 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

1. Query all databases
Since Oracle has no database name and only tablespace, Oracle does not provide database name query support, but only tablespace name query.

select * from v$tablespace;--查询表空间(需要一定权限)

2. Query all table names in the current database

select * from user_tables;

3. Query all field names in the specified table

select column_name from user_tab_columns where table_name = 'table_name';--表名要全大写

4. Query all field names and field types in the specified table

select column_name, data_type from user_tab_columns where table_name = 'table_name';--表名要全大写