-----------------------------------

unoho

Wed May 25, 2011 1:56 pm


dropping a table in sql

-----------------------------------

hey y'all
so im having difficulties dropping a dynamic named table although it seems very trivial imo.

[code]
while
begin
--some code utilizing the dynamic named table
end

--done w/ the table, delete it.
exec('
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N''[dbo].[' + @table_name + ']'') AND OBJECTPROPERTY(id, N''IsUserTable'') = 1)
DROP TABLE [dbo].[' + @table_name + ']')
[/code]

however it seems like it wont do the job and im stuck in my project. any clue what might be going wrong w/ this?


-----------------------------------

Tony

Wed May 25, 2011 2:11 pm


RE:dropping a table in sql

-----------------------------------

generate the string as it would be passed to the database. Execute the command manually. See what the database tells you.


-----------------------------------

unoho

Wed May 25, 2011 2:46 pm


RE:dropping a table in sql

-----------------------------------

it says "Command(s) completed successfully."

so i changed the drop table w/ print 'hello world' but it wouldn't print either. so im guessing the condition is false. although i cud clearly see the table in the database


-----------------------------------

Tony

Wed May 25, 2011 2:51 pm


RE:dropping a table in sql

-----------------------------------

So you could debug your conditionals, one element at a time...

you could also check if your DBMS supports
[code]
DROP TABLE IF EXISTS @table_name
[/code]
type of syntax.


-----------------------------------

unoho

Wed May 25, 2011 3:08 pm


RE:dropping a table in sql

-----------------------------------

ya it does...it's t-sql using sql server.. i think the problem is the path of the table. the way i coded it, it would save the whole path..but if i do a  drop table,  it only looks inside the current database. 

so i ended up using replace function and got rid off the front directory..

probly wont make sense to u..but whtever, it works..somehow..

thnks though


-----------------------------------

2goto1

Wed May 25, 2011 3:22 pm


RE:dropping a table in sql

-----------------------------------

Did you have the right database selected? I.e. in SQL Server you can select a database from SSMS with the  command 

use  MyDatabase;

You can issue a query from one database to another. I.e. 

select * from MyDatabase.dbo.MyTable

Perhaps you were querying the system catalog from a different database


-----------------------------------

Amailer

Wed May 25, 2011 5:22 pm


RE:dropping a table in sql

-----------------------------------

If you are creating tables that are only required for a specific session, take a look at temporary tables:
http://techahead.wordpress.com/2007/09/27/sql-temporary-tables/


-----------------------------------

unoho

Wed May 25, 2011 9:26 pm


RE:dropping a table in sql

-----------------------------------

ya i was at the right database at that time. i wish i cud post the whole code but against company policy. 
also, i tried temp tables before (like before) and it was good except i had few problems doing the exec commands.