Tables are not dropped !! Help!!

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

General SQL Server Forums

New to SQL Server Programming

Tables are not dropped !! Help!!

Author

Topic

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-10 : 11:57:50

I have a small sql block which has a cursor and every time at the beginning of the cursor loop i would have to drop the tables i have created in the previous loop.For some this statement is not dropping the table and not even throwing any error .Please let me know what is wrong.

if exists (select * from sys.sysobjects where name = 'RDATAS' and xtype = 'U')
drop table dbo.RDATAS
if exists (select * from sys.sysobjects where name = 'RDATAT' and xtype = 'U')
Drop table dbo.RDATAT
if exists (select * from sys.sysobjects where name = '#COLUMNNAMES1' and xtype = 'U')
drop table dbo.#COLUMNNAMES1
if exists (select * from sys.sysobjects where name = '#COLUMNNAMESDT' and xtype = 'U')
drop table #COLUMNNAMESDT
if exists (select * from sys.sysobjects where name = '#RVAL' and xtype = 'U')
drop table dbo.#RVAL

Thanks........

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-10 : 12:02:51

The exists for the # tables aren't going to find anything, because the hash tables aren't in the user DB. Do a google search, there should be a few posts on the right way to check for existence of a temp table.

--
Gail Shaw
SQL Server MVP

sravz
Starting Member

42 Posts

Posted - 2011-08-10 : 12:12:37

Hey Kqundava,

You can give a try to this format.As Shaw said the if exists doesn't work on temp tables
If object_id('tempdb..#temp_name) is null
drop table dbo.temp_name

Thanks,
Sravz

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-10 : 12:44:20

quote:
Originally posted by sravz

Hey Kqundava,

You can give a try to this format.As Shaw said the if exists doesn't work on temp tables
If object_id('tempdb..#temp_name) not is null
drop table #temp_name

Thanks,
Sravz

Small corrction.

kgundava
Yak Posting Veteran

66 Posts

Posted - 2011-08-10 : 14:27:56

Thanks a lot....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources