Oracle/MySQL/PostgreSQL数据库获取表的主键及字段信息(整理)

一、获取表的主键字段

1、Oracle数据库

SELECT
	COLUMN_NAME AS column_name 
FROM
	user_cons_columns cu,
	user_constraints au 
WHERE
	cu.constraint_name = au.constraint_name 
	AND au.OWNER = 'TANG'                         -- schema名称
	AND cu.TABLE_NAME = 't_oracle_single_VARCHAR' -- table名称

2、MySQL数据库

SELECT
	column_name 
FROM
	INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` 
WHERE
	TABLE_SCHEMA = 'test'          --schema名称
	AND table_name = 't_users'     --table名称
	AND constraint_name = 'PRIMARY'

3、PostgreSQL(Greenplum)数据库

SELECT 
	A.attname AS COLUMN_NAME,
	( i.keys ).n AS key_seq,
	ci.relname AS pk_name 
FROM
	pg_catalog.pg_class ct
	JOIN pg_catalog.pg_attribute A ON ( ct.oid = A.attrelid )
	JOIN pg_catalog.pg_namespace n ON ( ct.relnamespace = n.oid )
	JOIN (
	SELECT
		i.indexrelid,
		i.indrelid,
		i.indisprimary,
		information_schema._pg_expandarray ( i.indkey ) AS keys 
	FROM
		pg_catalog.pg_index i 
	) i ON ( A.attnum = ( i.keys ).x AND A.attrelid = i.indrelid )
	JOIN pg_catalog.pg_class ci ON ( ci.oid = i.indexrelid ) 
WHERE
TRUE 
	AND n.nspname = 'tang'       --schema名称
	AND ct.relname = 'new_user'  --table名称
	AND i.indisprimary 
ORDER BY
	pk_name,
	key_seq

二、获取表的字段信息

1、Oracle数据库

SELECT
	COLUMN_NAME AS field_name,
	DATA_TYPE AS field_type,
	DATA_LENGTH AS display_size,
	DATA_PRECISION AS precision,
	DATA_SCALE AS scale,
CASE
		NULLABLE 
		WHEN 'Y' THEN
		1 ELSE 0 
	END AS nullable,
	DATA_TYPE || '(' || DATA_LENGTH || ')' AS field_type_ex 
FROM
	all_tab_columns 
WHERE
	OWNER = 'ZFXFZB'                           --schema名称
	
	AND TABLE_NAME = 't_oracle_single_VARCHAR' --table名称

2、MySQL数据库

SELECT
	COLUMN_NAME AS field_name,
	DATA_TYPE AS field_type,
	CHARACTER_MAXIMUM_LENGTH AS display_size,
	NUMERIC_PRECISION AS `precision`,
	NUMERIC_SCALE AS scale,
	CASE
		IS_NULLABLE 
		WHEN 'NO' THEN
		1 ELSE 0 
	END AS nullable,
	COLUMN_TYPE AS field_type_ex 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_SCHEMA = 'test'       --schema名称
	AND TABLE_NAME = 't_user';  --table名称

3、PostgreSQL(Greenplum)数据库

SELECT 
	A.attname AS field_name,
	T.typname AS field_type,
	(
	CASE
			
			WHEN ( A.atttypmod ) :: NUMERIC > ( 10000 ) :: NUMERIC THEN
			0 
			WHEN ( A.atttypmod ) :: NUMERIC > ( 4 ) :: NUMERIC THEN
			A.atttypmod - 4 ELSE 0 
		END 
		) AS display_size,
		0 AS PRECISION,
		0 AS SCALE,
		( CASE A.attnotnull WHEN 't' THEN 0 ELSE 1 END ) AS NULLABLE,
		format_type ( A.atttypid, A.atttypmod ) AS field_type_ex 
	FROM
		pg_catalog.pg_namespace n
		JOIN pg_catalog.pg_class C ON ( C.relnamespace = n.oid )
		JOIN pg_catalog.pg_attribute A ON ( A.attrelid = C.oid )
		JOIN pg_catalog.pg_type T ON ( A.atttypid = T.oid )
		LEFT JOIN pg_catalog.pg_attrdef def ON ( A.attrelid = def.adrelid AND A.attnum = def.adnum )
		LEFT JOIN pg_catalog.pg_description dsc ON ( C.oid = dsc.objoid AND A.attnum = dsc.objsubid )
		LEFT JOIN pg_catalog.pg_class dc ON ( dc.oid = dsc.classoid AND dc.relname = 'pg_class' )
		LEFT JOIN pg_catalog.pg_namespace dn ON ( dc.relnamespace = dn.oid AND dn.nspname = 'pg_catalog' ) 
	WHERE
		A.attnum > 0 
		AND n.nspname = 'tang'     --schema名称
		AND C.relname = 'new_user' --table名称
ORDER BY
	A.attnum