sql server 2000 deleting a table 1

[pssheba]

Hi !
I delete a table using:

drop table mytable

If mytable exists then i get an error message.
What should be the right code to delete a table if it exists ?
Thanks !

 

[gmmastros]

You can check to see if the table exists before droping it like this...

If Exists(Select * From Information_Schema.Tables Where table_name='MyTable')
  Begin
    Drop Table MyTable
  End

If this code fails, then please post the error message you get.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pssheba]

Hi gmmastros !
thanks for your reply.
your code yielded the following error message:

Server: Msg 3701, Level 11, State 5, Procedure sp_aaa, Line 93
Cannot drop the table 'mytable', because it does not exist in the system catalog.
Server: Msg 2714, Level 16, State 6, Procedure sp_aaa.

Hopefully you can give me another advise but thanks all the same !

 

[gmmastros]

Can you run the following query and post the results here?

[tt][blue]Select * From Information_Schema.Tables Where table_name='MyTable'[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pssheba]

Hi !
Results are: when the table exists, it is listed in the query's result, if it doesn't exist i get an empty select result. No error message what so ever

 

[gmmastros]

When the table exists and you run the select query, what does it say for Table_Type ? If this is actually a table, then the Table_Type value will be 'BASE TABLE'. If this is actually a view, then the Table_type will be 'VIEW'.

The original code I provided should only drop the table if it exists. If the table does not exist, the DROP TABLE command would not be run. Therefore, you shouldn't get any error message. The information_schema.tables view also returns VIEWS that exist in the database. If you try to drop a table that is actually a view, you will get an error message. To delete a view, you would want to use Drop View ViewName



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[pssheba]

hi !
i found a system table:"sysobjects" where the name of my table resides under "name" column.
I implemented the code you sent on that table rather then on Information_Schema.Tables and it worked.
Do you think that this is the solution? If that is the case do you still want me to find out about what you asked?