Computer Science Canada

dropping a table in sql
Author:  unoho [ Wed May 25, 2011 1:56 pm ]
Post subject:  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 + ']')


however it seems like it wont do the job and im stuck in my project. any clue what might be going wrong w/ this?
Author:  Tony [ Wed May 25, 2011 2:11 pm ]
Post subject:  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.
Author:  unoho [ Wed May 25, 2011 2:46 pm ]
Post subject:  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
Author:  Tony [ Wed May 25, 2011 2:51 pm ]
Post subject:  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

type of syntax.
Author:  unoho [ Wed May 25, 2011 3:08 pm ]
Post subject:  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
Author:  2goto1 [ Wed May 25, 2011 3:22 pm ]
Post subject:  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
Author:  Amailer [ Wed May 25, 2011 5:22 pm ]
Post subject:  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/
Author:  unoho [ Wed May 25, 2011 9:26 pm ]
Post subject:  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.

:


You can syndicate this boards posts using the file backend.php or view the topic map using sitemap.php.

Terms of Use | Privacy Policy