checking if a table exists - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

Other Topics

checking if a table exists

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-09-27 : 08:30:28

sheeraz amir writes "Hi i would be very grateful if you can help me....I have been stuck on a particular problem in sybase SQL for ages now...

if am trying to use a if loop to check if a particular table exists in a database and if it does then do nothing else create a table...

IF OBJECT_ID('palsys..dl_cdy_trd_T') IS NULL
go
Create Table palsys..dl_cdy_trd_T (
acct_nbr char(10) null
)
end

it works fine the first ime i run it...(when the table does not exist)...

its creates the table

but then when i re run it ..i get an error message saying that the table already exists in the database cannot re create...

whereas it should not even be creating it as the table already exist...

I then searching the SQLTeam.com website and came across some forums where it had the solution to the problem ..

which was

if not exists (select * from sysobjects where id = object_id('dl_cdy_trd_T') and type = 'U')
Create Table palsys..dl_cdy_trd_T (acct_nbr char(10) null)
else
select 'exists'
end

now even this seems to be given me the same error message...

can you please help me

many thanks in advance

sheeraz"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-09-27 : 09:30:50

IF OBJECT_ID('palsys..dl_cdy_trd_T') IS NULL
go
Create Table palsys..dl_cdy_trd_T (
acct_nbr char(10) null
)
end

go terminates the batch so the create will not be affected by the if statement.
Removing the go will give an error because create has to be the first statement in a batch.

Try it in dynamic sql

IF OBJECT_ID('palsys..dl_cdy_trd_T') IS NULL
exec ('Create Table palsys..dl_cdy_trd_T (
acct_nbr char(10) null
)')

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources