Oracle
查詢所有表
SELECT table_name FROM user_tables;
查詢單表
SELECT table_name FROM user_tables WHERE table_name = 'TABLE_NAME';
查詢多表
SELECT table_name FROM user_tables WHERE table_name IN ('TABLE1', 'TABLE2');
查詢所有表字段
SELECT table_name, column_name, comments
FROM user_col_comments
ORDER BY table_name, column_name;
查詢單表字段
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name = 'TABLE_NAME'
ORDER BY column_name;
查詢多表字段
SELECT table_name, column_name, comments
FROM user_col_comments
WHERE table_name IN ('TABLE1', 'TABLE2')
ORDER BY table_name, column_name;
MySQL
查詢所有表
SHOW TABLES;
查詢單表
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME';
查詢多表
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME IN ('TABLE1', 'TABLE2');
查詢所有表字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DATABASE_NAME'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
查詢單表字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME = 'TABLE_NAME'
ORDER BY ORDINAL_POSITION;
查詢多表字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'DATABASE_NAME' AND TABLE_NAME IN ('TABLE1', 'TABLE2')
ORDER BY TABLE_NAME, ORDINAL_POSITION;
SQL Server
查詢所有表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY TABLE_NAME;
查詢單表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'TABLE_NAME';
查詢多表
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME IN ('TABLE1', 'TABLE2');
查詢所有表字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'DATABASE_NAME' AND TABLE_SCHEMA = 'dbo'
ORDER BY TABLE_NAME, ORDINAL_POSITION;
查詢單表字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'DATABASE_NAME' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'TABLE_NAME'
ORDER BY ORDINAL_POSITION;
查詢多表字段
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DESCRIPTION
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'DATABASE_NAME' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME IN ('TABLE1', 'TABLE2')
ORDER BY TABLE_NAME, ORDINAL_POSITION;
PostGreSQL
查詢所有表
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name;
查詢單表
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'TABLE_NAME';
查詢多表
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name IN ('TABLE1', 'TABLE2');
查詢所有表字段
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;
查詢單表字段
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name = 'TABLE_NAME'
ORDER BY ordinal_position;
查詢多表字段
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_schema = 'public' AND table_name IN ('TABLE1', 'TABLE2')
ORDER BY table_name, ordinal_position;
