常见数据库中判断表、字段是否存在的SQL语句

判断一个表是否存在

MySQL

SELECT table_name FROM information_schema.TABLES WHERE table_name='表名';
# 返回表名或NULL，直接判断
# 或者：
select count(*) from information_schema.tables where table_name='表名';
# 返回1 or 0, 需要再次判断

MSSQL Server

select count(*) from dbo.sysobjects where name='表名';

PostgreSQL

select count(*) from information_schema.tables where table_schema='table_schema' and table_name='表名';

Oracle

select count(*) from user_objects where object_name='表名';

Sqlite

select count(*) from sqlite_master where type='table' and name='表名';

判断表中一个字段是否存在

MySQL

select count(*) from information_schema.columns where table_name = '表名' and column_name='字段名';

MSSQL Server

select count(*) from syscolumns where id=object_id('表名') and name= '字段名';

PostgreSQL

select count(*) from information_schema.columns where table_schema='table_schema' and table_name='表名' and column_name='字段名';

Oracle

select count(*) from user_tab_columns where table_name='表名' and column_name='字段名';

Sqlite

select count(*) from sqlite_master where name='表名' and sql like '字段名'