关系型数据库,查看表列表,及字段列表

查询数据库存在哪些表的SQL

// mysql
select table_name as tableName from INFORMATION_SCHEMA.TABLES where table_schema = '" + schema + "'"

//oracle
select table_name as tableName from user_tables

//sqlserver
select name as tableName from sys.tables

//pgsql
select tablename as tableName from pg_tables where schemaname='public'

查询表中有哪些字段

//msql
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,COLUMN_COMMENT  FROM information_schema.COLUMNS WHERE table_schema = '" + schema + "' AND TABLE_NAME = '" + tableName + "'"

//oracle
select  column_name AS COLUMN_NAME, data_type AS DATA_TYPE ,schema_name AS TABLE_SCHEMA, table_name  AS TABLE_NAME ,'' AS COLUMN_COMMENT from user_tab_columns where table_name = '" + tableName + "'"

//sqlserver
select TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,DATA_TYPE,'' AS COLUMN_COMMENT from information_schema.COLUMNS where table_name = '%s' AND  TABLE_CATALOG='%s'"

//pgsql
select '%s' AS TABLE_NAME,'%s' AS TABLE_SCHEMA , a.attname AS COLUMN_NAME ,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as DATA_TYPE,d.description AS COLUMN_COMMENT from pg_class c, pg_attribute a , pg_type t, pg_description d 
"
                            + "where  c.relname = '%s' and a.attnum>0 and a.attrelid = c.oid and a.atttypid = t.oid and  d.objoid=a.attrelid and d.objsubid=a.attnum
"

代码下载