DROP TABLE IF EXISTS

Hello,
Forgive me if I am being stupid, but I cannot get some syntax to work and I
need it urgently. I want to drop a table if it exists and make a new table
in its place. I though I could do,
DROP TABLE IF EXISTS tablename;
CREATE TABLE tablename ([ID] [int] NOT NULL)
but I get the first time as the table doesn't exist yet!
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'tablename' , because it does not exist in the system
catalog.
Please could someone help me with the synatax!if exists (select * from dbo.sysobjects where id =
object_id(N'[<owner>].[<tablename>]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [<owner>].[<tablename>]
GO
CREATE TABLE [<owner>].[<tablename>] (
...
) GO
"11Oppidan" wrote:

> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new tabl
e
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the syste
m
> catalog.
> Please could someone help me with the synatax!
>
>|||You can try as follows...
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tablename]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tablename]
GO
CREATE TABLE [dbo].[tablename] (
[aId] [int] IDENTITY (1, 1) NOT NULL ,
[nDayId] [int] NOT NULL
) ON [PRIMARY]
GO|||11Oppidan wrote on Wed, 23 Mar 2005 09:15:07 -0000:

> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
You can use EM to script out table creation, and if you keep the option to
create the DROP syntax ticked you'll get something like:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Tablename]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tablename]
GO
followed by the create statement.
Dan|||try this:
if object_id('tablename') is not null
drop table tablename
go
or this:
if exists(select * from sysobjects where xtype='u' and name='tablename')
drop table tablename
go
hth
dean
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
I
> need it urgently. I want to drop a table if it exists and make a new
table
> in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
system
> catalog.
> Please could someone help me with the synatax!
>|||Many thanks guys!
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:OpV06i4LFHA.3196@.TK2MSFTNGP10.phx.gbl...
> Hello,
> Forgive me if I am being stupid, but I cannot get some syntax to work and
> I need it urgently. I want to drop a table if it exists and make a new
> table in its place. I though I could do,
> DROP TABLE IF EXISTS tablename;
> CREATE TABLE tablename ([ID] [int] NOT NULL)
> but I get the first time as the table doesn't exist yet!
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'tablename' , because it does not exist in the
> system catalog.
> Please could someone help me with the synatax!
>