SQL笔记-查询表+字段+字段注释

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;