如何获取数据库表的信息Schema并限制查询仅返回表排除视图？

Hey there! Let's break down your two questions step by step, covering the most common databases since the approach varies a bit between them.

The exact query depends on your database system—here are the most common implementations:

MySQL/MariaDB #

Use the built-in INFORMATION_SCHEMA database, which stores all metadata for your databases:

SELECT 
  t.TABLE_SCHEMA AS database_name,
  t.TABLE_NAME AS table_name,
  c.COLUMN_NAME AS column_name,
  c.DATA_TYPE AS data_type,
  c.IS_NULLABLE AS is_nullable,
  c.COLUMN_DEFAULT AS default_value,
  c.CHARACTER_MAXIMUM_LENGTH AS max_length
FROM INFORMATION_SCHEMA.TABLES t
JOIN INFORMATION_SCHEMA.COLUMNS c 
  ON t.TABLE_SCHEMA = c.TABLE_SCHEMA 
  AND t.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_SCHEMA = 'your_database_name'; -- Replace with your target DB name

This returns comprehensive details like column data types, nullability rules, default values, and max length for every table in your specified database.

PostgreSQL #

PostgreSQL uses information_schema for standard metadata, plus you can tap into pg_catalog for deeper system-level details:

SELECT 
  table_schema AS schema_name,
  table_name,
  column_name,
  data_type,
  is_nullable,
  column_default
FROM information_schema.columns
WHERE table_schema = 'public'; -- Replace with your schema name (usually 'public' by default)

If you need constraint details (like primary keys), join this query with information_schema.table_constraints and information_schema.key_column_usage.

SQL Server #

Leverage the INFORMATION_SCHEMA views, similar to MySQL:

SELECT 
  TABLE_CATALOG AS database_name,
  TABLE_SCHEMA AS schema_name,
  TABLE_NAME AS table_name,
  COLUMN_NAME AS column_name,
  DATA_TYPE AS data_type,
  IS_NULLABLE AS is_nullable,
  COLUMN_DEFAULT AS default_value
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'your_database_name'; -- Replace with your DB name

For table-level details like storage settings, pair this with the sys.tables system view.

Oracle #

Use the USER_TABLES and USER_TAB_COLUMNS views (swap USER with ALL or DBA if you need access to tables owned by other users):

SELECT 
  t.TABLE_NAME AS table_name,
  c.COLUMN_NAME AS column_name,
  c.DATA_TYPE AS data_type,
  c.NULLABLE AS is_nullable,
  c.DATA_DEFAULT AS default_value
FROM USER_TABLES t
JOIN USER_TAB_COLUMNS c 
  ON t.TABLE_NAME = c.TABLE_NAME;

This gives you full schema details for all tables owned by your current database user.

Every database system has a metadata field that distinguishes base tables from views. Just add a WHERE clause to filter out views:

MySQL/MariaDB #

Filter using TABLE_TYPE = 'BASE TABLE'—views will have a TABLE_TYPE value of 'VIEW':

SELECT TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

PostgreSQL #

Use table_type = 'BASE TABLE' to exclude views (which show up as 'VIEW' in this field):

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_type = 'BASE TABLE';

SQL Server #

Same logic applies with TABLE_TYPE = 'BASE TABLE':

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG = 'your_database_name'
  AND TABLE_TYPE = 'BASE TABLE';

Oracle #

Filter on OBJECT_TYPE = 'TABLE'—views are labeled as 'VIEW' in this field:

SELECT OBJECT_NAME AS table_name
FROM USER_OBJECTS
WHERE OBJECT_TYPE = 'TABLE';

Pro tip: If you use USER_TABLES directly, you don't need an extra filter—this view only returns base tables, not views!

内容的提问来源于stack exchange，提问作者Spjcc