Menu
I've created a table and then realised I made a mistake. SSMS wouldn't let me update the table without recreating it, so I've deleted the table and then tried to create it again.
It won't let me do this, and I get an error dialog
Table 'dbo.Foo' already exists.
So I try to delete it again:
drop table dbo.Foo Cannot drop the table 'dbo.Foo', because it does not exist or you do not have permission.
Refreshing the IntelliSense cache does not help.
815
The DROP TABLE statement deletes the specified table, and any data associated with it, from the database. The IF EXISTS clause allows the statement to succeed even if the specified tables does not exist. If the table does not exist and you do not include the IF EXISTS clause, the statement will return an error.
Answer. When you try to create a table with an already existing table name, you will receive an error message, and no table will be modified or created.
To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA. TABLES table. You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists. One of the more common uses I find for this when I need to create a table in a script.
Closing and restarting SSMS seems to be the only way to get rid of the error. After doing so, I can again successfully create the table.
70
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
