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

太过爱你忘了你带给我的痛 2023-10-12 14:23 111阅读 0赞

### 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;