Oracle・PostgreSQL・MySQL・SQLServer・H2Databaseそれぞれでテーブル・カラム・プライマリキー・インデックスを取得するSQL

Oracle・PostgreSQL・MySQL・SQLServer・H2Databaseで、TABLE一覧・COLUMN一覧・PRIMARY KEY・INDEX一覧を取得するSQL(?)を最近調べたのでさらすことにします。
Oracle以外は経験浅いので、間違っていたら教えてほしいです。

JavaからJDBC経由で取りたいのでPostgreSQLでコマンド「\d」でテーブル一覧が取れるとかそういうのは書いてません。
あと、結果をほぼ一致させたかったのでAS句の使えない（使えそうにない）MySQLの「SHOW TABLES」等も無しです。

# テーブル一覧（コメント付）
## Oracle

```sql:Oracle
SELECT
	TBL.TABLE_NAME	AS TABLE_NAME
,	CMM.COMMENTS	AS COMMENTS
FROM
	USER_TABLES TBL
	LEFT OUTER JOIN USER_TAB_COMMENTS CMM ON TBL.TABLE_NAME = CMM.TABLE_NAME
ORDER BY TBL.TABLE_NAME
```
## PostgreSQL

```sql:PostgreSQL
SELECT
	TBL.TABLENAME											AS TABLE_NAME
,	OBJ_DESCRIPTION(TBL.TABLENAME::regclass, 'pg_class')	AS COMMENTS
FROM
	PG_CATALOG.PG_TABLES TBL
WHERE
	TBL.SCHEMANAME	= current_schema
ORDER BY
	TBL.TABLENAME
```
## MySQL

```sql:MySQL
SELECT
	TBL.TABLE_NAME		AS TABLE_NAME
,	TBL.TABLE_COMMENT	AS COMMENTS
  FROM INFORMATION_SCHEMA.TABLES TBL
WHERE
	TBL.TABLE_SCHEMA	= DATABASE()
ORDER BY
	TBL.TABLE_NAME
```
## SQLServer

```sql:SQLServer
SELECT
	CONVERT(VARCHAR, TBL.NAME)	AS TABLE_NAME
,	CONVERT(VARCHAR, CMM.VALUE)	AS COMMENTS
FROM
	sys.tables				AS TBL
,	sys.extended_properties	AS CMM
WHERE
	TBL.OBJECT_ID	= CMM.MAJOR_ID
AND	CMM.MINOR_ID	= 0
ORDER BY
	TBL.NAME
```
## H2Database

```sql:H2Database
SELECT
	TBL.TABLE_NAME	AS TABLE_NAME
,	TBL.REMARKS		AS COMMENTS
FROM
	INFORMATION_SCHEMA.TABLES	AS TBL
WHERE
	TBL.TABLE_SCHEMA	=		SCHEMA()
ORDER BY
	TBL.TABLE_NAME
```

#プライマリキー一覧
## Oracle

```sql:Oracle
SELECT
	CNS.TABLE_NAME		AS TABLE_NAME
,	CLM.CONSTRAINT_NAME	AS CONSTRAINT_NAME
,	CLM.COLUMN_NAME		AS COLUMN_NAME
,	CLM.POSITION		AS POSITION
FROM
	USER_CONSTRAINTS CNS
	LEFT OUTER JOIN USER_CONS_COLUMNS CLM ON CNS.OWNER = CLM.OWNER AND CNS.CONSTRAINT_NAME = CLM.CONSTRAINT_NAME
WHERE
	CNS.OWNER			= SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
AND	CNS.CONSTRAINT_TYPE	= 'P'
ORDER BY
	CNS.TABLE_NAME
,	CLM.POSITION

```
## PostgreSQL

```sql:PostgreSQL
SELECT
	TC.TABLE_NAME			AS TABLE_NAME
,	TC.CONSTRAINT_NAME		AS CONSTRAINT_NAME
,	COL.COLUMN_NAME			AS COLUMN_NAME
,	COL.ORDINAL_POSITION	AS POSITION
FROM
	INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON
		TC.TABLE_NAME		= CCU.TABLE_NAME
	AND	TC.CONSTRAINT_NAME	= CCU.CONSTRAINT_NAME
	INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE COL ON
		TC.TABLE_CATALOG	= COL.TABLE_CATALOG
	AND	TC.TABLE_SCHEMA		= COL.TABLE_SCHEMA
	AND	TC.TABLE_NAME		= COL.TABLE_NAME
	AND	CCU.COLUMN_NAME		= COL.COLUMN_NAME
WHERE
	TC.CONSTRAINT_TYPE	= 'PRIMARY KEY'
ORDER BY
	TC.TABLE_NAME
,	COL.ORDINAL_POSITION

```
## MySQL

```sql:MySQL
SELECT
	KCOL.TABLE_NAME			AS TABLE_NAME
,	KCOL.CONSTRAINT_NAME	AS CONSTRAINT_NAME
,	KCOL.COLUMN_NAME		AS COLUMN_NAME
,	KCOL.ORDINAL_POSITION	AS POSITION
FROM
	INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCOL
WHERE
	KCOL.TABLE_SCHEMA	= DATABASE()
ORDER BY
	KCOL.TABLE_NAME
,	KCOL.ORDINAL_POSITION

```
## SQLServer

```sql:SQLServer
SELECT
	CONVERT(VARCHAR, TBL.NAME)		AS TABLE_NAME
,	CONVERT(VARCHAR, KCNST.NAME)	AS CONSTRAINT_NAME
,	CONVERT(VARCHAR, COLS.NAME)		AS COLUMN_NAME
,	IDXCOL.KEY_ORDINAL				AS POSITION
FROM
	sys.tables AS TBL
		INNER JOIN sys.key_constraints AS KCNST
		ON
			TBL.OBJECT_ID	= KCNST.PARENT_OBJECT_ID
		AND	KCNST.TYPE	= 'PK'
		INNER JOIN sys.index_columns AS IDXCOL
		ON
			KCNST.PARENT_OBJECT_ID	= IDXCOL.OBJECT_ID
		AND	KCNST.UNIQUE_INDEX_ID	= IDXCOL.INDEX_ID
		INNER JOIN sys.columns AS COLS
		ON
			IDXCOL.OBJECT_ID	= COLS.OBJECT_ID
		AND	IDXCOL.COLUMN_ID	= COLS.COLUMN_ID
ORDER BY
	TBL.NAME
,	IDXCOL.KEY_ORDINAL

```
## H2Database

```sql:H2Database
SELECT
	CON.TABLE_NAME			AS	TABLE_NAME
,	CON.CONSTRAINT_NAME		AS	CONSTRAINT_NAME
,	IDX.COLUMN_NAME			AS	COLUMN_NAME
,	IDX.ORDINAL_POSITION	AS	POSITION
FROM
	INFORMATION_SCHEMA.CONSTRAINTS	CON
,	INFORMATION_SCHEMA.INDEXES		IDX
WHERE
	CON.CONSTRAINT_SCHEMA	=	SCHEMA()
AND	CON.TABLE_NAME			=	IDX.TABLE_NAME
AND	CON.UNIQUE_INDEX_NAME	=	IDX.INDEX_NAME
AND	IDX.TABLE_SCHEMA		=	CON.TABLE_SCHEMA
ORDER BY
	CON.TABLE_NAME
,	IDX.ORDINAL_POSITION
```


# テーブル別カラム情報一覧（コメント付）
## Oracle

```sql:Oracle
SELECT
	CLM.COLUMN_NAME		AS COLUMN_NAME
,	CMM.COMMENTS		AS COMMENTS
,	CLM.DATA_TYPE		AS DATA_TYPE
,	CLM.DATA_LENGTH		AS DATA_LENGTH
,	CLM.DATA_PRECISION	AS DATA_PRECISION
,	CLM.DATA_SCALE		AS DATA_SCALE
,	CLM.NULLABLE		AS NULLABLE
,	CLM.DATA_DEFAULT	AS DATA_DEFAULT
FROM
	USER_TAB_COLUMNS CLM
	LEFT OUTER JOIN USER_COL_COMMENTS CMM ON CLM.TABLE_NAME = CMM.TABLE_NAME AND CLM.COLUMN_NAME = CMM.COLUMN_NAME
WHERE
	CLM.TABLE_NAME = /* tableName */'TABLE_NAME'
ORDER BY
	CLM.TABLE_NAME
,	CLM.COLUMN_ID

```
## PostgreSQL

```sql:PostgreSQL
SELECT
	COL.COLUMN_NAME													AS COLUMN_NAME
,	COL_DESCRIPTION(COL.TABLE_NAME::regclass,COL.ORDINAL_POSITION)	AS COMMENTS
,	COL.DATA_TYPE													AS DATA_TYPE
,	COL.CHARACTER_MAXIMUM_LENGTH									AS DATA_LENGTH
,	COALESCE(COL.NUMERIC_PRECISION,COL.DATETIME_PRECISION)			AS DATA_PRECISION
,	COL.NUMERIC_SCALE												AS DATA_SCALE
,	COL.IS_NULLABLE													AS NULLABLE
,	COL.COLUMN_DEFAULT												AS DATA_DEFAULT
FROM
	INFORMATION_SCHEMA.COLUMNS COL
WHERE
	COL.TABLE_NAME	= /* tableName */'TABLE_NAME'
ORDER BY
	COL.TABLE_NAME
,	COL.ORDINAL_POSITION
```
## MySQL

```sql:MySQL
SELECT
	COL.COLUMN_NAME											AS COLUMN_NAME
,	COL.COLUMN_COMMENT										AS COMMENTS
,	COL.DATA_TYPE											AS DATA_TYPE
,	COL.CHARACTER_MAXIMUM_LENGTH							AS DATA_LENGTH
,	IFNULL(COL.NUMERIC_PRECISION,COL.DATETIME_PRECISION)	AS DATA_PRECISION
,	COL.NUMERIC_SCALE										AS DATA_SCALE
,	COL.IS_NULLABLE											AS NULLABLE
,	COL.COLUMN_DEFAULT										AS DATA_DEFAULT
FROM
	INFORMATION_SCHEMA.COLUMNS COL
WHERE
	COL.TABLE_SCHEMA	= DATABASE()
AND	COL.TABLE_NAME		= /* tableName */'TABLE_NAME'
ORDER BY
	COL.TABLE_NAME
,	COL.ORDINAL_POSITION
```
## SQLServer

```sql:SQLServer
SELECT
	CONVERT(VARCHAR, COL.NAME)	AS COLUMN_NAME
,	CONVERT(VARCHAR, EP.VALUE)	AS COMMENTS
,	ICOL.DATA_TYPE				AS DATA_TYPE
,	COL.MAX_LENGTH				AS DATA_LENGTH
,	COL.PRECISION				AS DATA_PRECISION
,	COL.SCALE					AS DATA_SCALE
,	ICOL.IS_NULLABLE			AS NULLABLE
,	ICOL.COLUMN_DEFAULT			AS DATA_DEFAULT
FROM
	sys.tables					AS TBL
,	sys.columns					AS COL
,	sys.extended_properties		AS EP
,	INFORMATION_SCHEMA.COLUMNS	AS ICOL
WHERE
	TBL.NAME		= /* tableName */'TABLE_NAME'
AND	TBL.OBJECT_ID	= COL.OBJECT_ID
AND	COL.OBJECT_ID	= EP.MAJOR_ID
AND	COL.COLUMN_ID	= EP.MINOR_ID
AND	COL.NAME		= ICOL.COLUMN_NAME
AND	TBL.NAME		= ICOL.TABLE_NAME
ORDER BY
	TBL.NAME
,	COL.COLUMN_ID
```
## H2Database

```sql:H2Database
SELECT
	COL.COLUMN_NAME					AS COLUMN_NAME
,	COL.REMARKS						AS COMMENTS
,	COL.TYPE_NAME					AS DATA_TYPE
,	COL.CHARACTER_MAXIMUM_LENGTH	AS DATA_LENGTH
,	COL.NUMERIC_PRECISION			AS DATA_PRECISION
,	COL.NUMERIC_SCALE				AS DATA_SCALE
,	COL.IS_NULLABLE					AS NULLABLE
,	COL.COLUMN_DEFAULT				AS DATA_DEFAULT
FROM
	INFORMATION_SCHEMA.COLUMNS	COL
WHERE
	COL.TABLE_SCHEMA	= SCHEMA()
AND	COL.TABLE_NAME		= /* tableName */'TABLE_NAME'
ORDER BY
	COL.TABLE_NAME
,	COL.ORDINAL_POSITION
```


# テーブル別インデックス一覧
## Oracle

```sql:Oracle
SELECT
	IDX.TABLE_NAME			AS TABLE_NAME
,	IDX.INDEX_NAME			AS INDEX_NAME
,	IDX.UNIQUENESS			AS UNIQUENESS
,	IDXCOL.COLUMN_NAME		AS COLUMN_NAME
,	IDXCOL.COLUMN_POSITION	AS POSITION
FROM
	USER_INDEXES IDX
,	USER_IND_COLUMNS IDXCOL
WHERE
	IDX.INDEX_NAME	= IDXCOL.INDEX_NAME
AND	IDX.TABLE_NAME	= IDXCOL.TABLE_NAME
AND	IDX.TABLE_NAME	= /* tableName */'TABLE_NAME'
ORDER BY
	IDX.TABLE_NAME
,	IDX.INDEX_NAME
,	IDXCOL.COLUMN_POSITION
```
## PostgreSQL

```sql:PostgreSQL
SELECT
	IDXS.TABLENAME	AS TABLE_NAME
,	IDXS.INDEXNAME	AS INDEX_NAME
,	IDX.INDISUNIQUE	AS UNIQUENESS
,	COL.COLUMN_NAME	AS COLUMN_NAME
,	IDX.POSITION	AS POSITION
 FROM
 	PG_INDEXES IDXS,
	(SELECT
		IDX.INDEXRELID						AS INDEXRELID
	,	IDX.INDISUNIQUE						AS INDISUNIQUE
	,	UNNEST(IDX.INDKEY)					AS INDKEY
	,	GENERATE_SUBSCRIPTS(IDX.INDKEY,1)	AS POSITION
	FROM
		PG_INDEX IDX
	) IDX
	,INFORMATION_SCHEMA.COLUMNS COL
WHERE
	IDXS.SCHEMANAME			= current_schema
AND	IDXS.INDEXNAME			NOT LIKE '%pg_toast_%'
AND	IDX.INDEXRELID			= IDXS.INDEXNAME::regclass
AND	COL.TABLE_SCHEMA		= current_schema
AND	COL.TABLE_NAME			= IDXS.TABLENAME
AND	IDXS.TABLENAME			= /* tableName */'TABLE_NAME'
AND	COL.ORDINAL_POSITION	= IDX.INDKEY
ORDER BY
	IDXS.TABLENAME
,	IDXS.INDEXNAME
,	IDX.POSITION
```
## MySQL

```sql:MySQL
SELECT
	STT.TABLE_NAME					AS TABLE_NAME
,	STT.INDEX_NAME					AS INDEX_NAME
,	IF(STT.NON_UNIQUE=1,'NO','YES')	AS UNIQUENESS
,	STT.COLUMN_NAME					AS COLUMN_NAME
,	STT.SEQ_IN_INDEX				AS POSITION
FROM INFORMATION_SCHEMA.STATISTICS STT
WHERE
	STT.TABLE_SCHEMA	= DATABASE()
AND	STT.TABLE_NAME		= /* tableName */'TABLE_NAME'
ORDER BY
	STT.TABLE_NAME
,	STT.INDEX_NAME
,	STT.SEQ_IN_INDEX
```
## SQLServer

```sql:SQLServer
SELECT
	CONVERT(VARCHAR, TBL.NAME)		AS TABLE_NAME
,	CONVERT(VARCHAR, IDX.NAME)	    AS INDEX_NAME
,   IDX.IS_UNIQUE					AS UNIQUENESS
,	CONVERT(VARCHAR, COLS.NAME)		AS COLUMN_NAME
,	IDXCOL.KEY_ORDINAL				AS POSITION
FROM
	sys.tables AS TBL
		INNER JOIN sys.indexes AS IDX
		ON
			TBL.OBJECT_ID		= IDX.OBJECT_ID
		INNER JOIN sys.index_columns AS IDXCOL
		ON
			TBL.OBJECT_ID		= IDXCOL.OBJECT_ID
		AND	IDX.INDEX_ID		= IDXCOL.INDEX_ID
		INNER JOIN sys.columns AS COLS
		ON
			IDXCOL.OBJECT_ID	= COLS.OBJECT_ID
		AND	IDXCOL.COLUMN_ID	= COLS.COLUMN_ID
WHERE
	TBL.NAME	= /* tableName */'TABLE_NAME'
ORDER BY
	TBL.NAME
,   IDX.NAME
,	IDXCOL.KEY_ORDINAL
```
## H2Database

```sql:H2Database
SELECT
	IDX.TABLE_NAME									AS	TABLE_NAME
,	COALESCE(CON.CONSTRAINT_NAME, IDX.INDEX_NAME)	AS	INDEX_NAME
,	CASE
		WHEN
			IDX.NON_UNIQUE
		THEN
			'no'
		ELSE
			'yes'
	END												AS	UNIQUENESS
,	IDX.COLUMN_NAME									AS	COLUMN_NAME
,	IDX.ORDINAL_POSITION							AS	POSITION
FROM
	INFORMATION_SCHEMA.INDEXES	IDX
LEFT JOIN
	INFORMATION_SCHEMA.CONSTRAINTS	CON	ON
	(
		CON.TABLE_NAME			=	IDX.TABLE_NAME
	AND	CON.UNIQUE_INDEX_NAME	=	IDX.INDEX_NAME
	AND	IDX.TABLE_SCHEMA		=	CON.TABLE_SCHEMA
	)
WHERE
	IDX.TABLE_SCHEMA	=	SCHEMA()
AND	IDX.TABLE_NAME		=	/* tableName */'TABLE_NAME'
ORDER BY
	IDX.TABLE_NAME
,	IDX.INDEX_NAME
,	IDX.ORDINAL_POSITION
```


# あとがき
JOINのやりかたがそれぞれ自由で醜いのはごめんなさい。
どこかで参考にしたSQLをなるべく崩さず書いていたりするためです。

次は、JDBC経由での実行計画の取り方について書いています。
[Oracle・PostgreSQL・MySQL・SQLServer・H2DatabseそれぞれでJDBCを利用して実行計画を取得する](http://qiita.com/ota-meshi/items/6a0dc20f2aca6746047a)

# 参考にしたサイト一部
ありがとうございます助かりました
PostgreSQL参考
https://www.postgresql.jp/document/9.4/html/functions-info.html
http://devlights.hatenablog.com/entry/20080226/p1
http://landhere.info/blog/a65.html
http://ameblo.jp/blueskyame/entry-10261155548.html
http://chopl.in/post/2013/11/07/how_to_retrieve_tables_and_columns_with_postgres/
http://d.hatena.ne.jp/ja9/20100830/1283146058
SQLServer参考
http://lightgauge.net/database/sqlserver/3010/
http://lightgauge.net/database/sqlserver/3697/
http://lightgauge.net/database/sqlserver/3682/
MySQL参考
http://d.hatena.ne.jp/tanamon/20090325/1237972922
http://hack.aipo.com/archives/3513/
H2参考
http://www.h2database.com/html/grammar.html#information_schema
http://groovyarekore.blogspot.jp/2010/09/groovyh2-database_22.html