Go to Qiita Advent Calendar 2024 Top
LoginSignup
54
54

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@ota-meshi(ota)inフューチャー株式会社

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

Last updated at Posted at 2015-12-08

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

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

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

Oracle

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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を利用して実行計画を取得する

参考にしたサイト一部

ありがとうございます助かりました
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

54
54
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
54
54

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?