如何用SQL语句判断一个TABLE是否存在？# Database - 数据库
o*s
1 楼
各位高手：
我想实现下面的SQL块语句
begin
if TABLE是否存在
delete from TABLE;
else
create TABLE;
...
end
谢了
我想实现下面的SQL块语句
begin
if TABLE是否存在
delete from TABLE;
else
create TABLE;
...
end
谢了
g*s
2 楼
sql server is
exists(select * from sysobjects where name='table_name' and type='U')
【在 o**s 的大作中提到】
: 各位高手：
: 我想实现下面的SQL块语句
: begin
: if TABLE是否存在
: delete from TABLE;
: else
: create TABLE;
: ...
: end
: 谢了
exists(select * from sysobjects where name='table_name' and type='U')
【在 o**s 的大作中提到】
: 各位高手：
: 我想实现下面的SQL块语句
: begin
: if TABLE是否存在
: delete from TABLE;
: else
: create TABLE;
: ...
: end
: 谢了
w*g
3 楼
in Oracle, try this one.
declare
isThere varchar2(1);
begin
select 'Y' into isThere
from user_tables
where table_name = 'MYTABLE';
if isThere = 'Y' then
truncate table MYTABLE;
else
create table MYTABLE (....);
end if;
end;
【在 o**s 的大作中提到】
: 各位高手：
: 我想实现下面的SQL块语句
: begin
: if TABLE是否存在
: delete from TABLE;
: else
: create TABLE;
: ...
: end
: 谢了
declare
isThere varchar2(1);
begin
select 'Y' into isThere
from user_tables
where table_name = 'MYTABLE';
if isThere = 'Y' then
truncate table MYTABLE;
else
create table MYTABLE (....);
end if;
end;
【在 o**s 的大作中提到】
: 各位高手：
: 我想实现下面的SQL块语句
: begin
: if TABLE是否存在
: delete from TABLE;
: else
: create TABLE;
: ...
: end
: 谢了
s*t
4 楼
it won't work. An error will be generated if more than
one table have the same name under different schemas.
declare
isThere varchar2(1) := 'N';
begin
select 'Y' into isThere
from dual
where exists
(select null from all_tables
where table_name = 'ABC');
if isThere = 'Y' then
dbms_output.put_line('exists');
end if;
exception
when no_data_found then
dbms_output.put_line('not exists');
end;
【在 w****g 的大作中提到】
: in Oracle, try this one.
: declare
: isThere varchar2(1);
: begin
: select 'Y' into isThere
: from user_tables
: where table_name = 'MYTABLE';
: if isThere = 'Y' then
: truncate table MYTABLE;
: else
one table have the same name under different schemas.
declare
isThere varchar2(1) := 'N';
begin
select 'Y' into isThere
from dual
where exists
(select null from all_tables
where table_name = 'ABC');
if isThere = 'Y' then
dbms_output.put_line('exists');
end if;
exception
when no_data_found then
dbms_output.put_line('not exists');
end;
【在 w****g 的大作中提到】
: in Oracle, try this one.
: declare
: isThere varchar2(1);
: begin
: select 'Y' into isThere
: from user_tables
: where table_name = 'MYTABLE';
: if isThere = 'Y' then
: truncate table MYTABLE;
: else
m*t
5 楼
In SQL Server, this will do it:
if exists (select * from sysobjects where id = object_id(N'tableName')
and OBJECTPROPERTY(id, N'Table') = 1)
if exists (select * from sysobjects where id = object_id(N'tableName')
and OBJECTPROPERTY(id, N'Table') = 1)
s*j
6 楼
In Oracle
select * from dba_tables where table_name = 'TABLE_NAME'
【在 m******t 的大作中提到】
: In SQL Server, this will do it:
: if exists (select * from sysobjects where id = object_id(N'tableName')
: and OBJECTPROPERTY(id, N'Table') = 1)
select * from dba_tables where table_name = 'TABLE_NAME'
【在 m******t 的大作中提到】
: In SQL Server, this will do it:
: if exists (select * from sysobjects where id = object_id(N'tableName')
: and OBJECTPROPERTY(id, N'Table') = 1)
相关阅读