CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
CM2K
CM2K

Reputation: 883

drop table #temp vs drop myTable if it's not null

So far i was using

IF OBJECT_ID(''tempdb..#tempTable'') IS NOT NULL  -- dropping the temp table
   DROP TABLE #tempTable

Is there a way in which I could use the same statement for a table which is not a temp one? Tried like this and it didn't work:

IF OBJECT_ID(''myOwnDb.dbo.myTable'') IS NOT NULL  -- dropping the table
   DROP TABLE dbo.myTable

Upvotes: 0

Views: 1201

Answers (3)

Andrey Korneyev
Andrey Korneyev

Reputation: 26876

Strings in MS SQL server should be enclosed in single quotes.

So neither OBJECT_ID(''myOwnDb.dbo.myTable'') nor OBJECT_ID("myOwnDb.dbo.myTable") will work.

But OBJECT_ID('myOwnDb.dbo.myTable') will work perfectly.

Upvotes: 2

Amnesh Goel
Amnesh Goel

Reputation: 2655

In addition to what other users have suggested wrt Object_ID which is fine, you can explore below method to detect if table exist or not using INFORMATION_SCHEMA

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_NAME = N'Your Table Name')
BEGIN
  Drop table <tablename>
END

Upvotes: 1

JBond
JBond

Reputation: 3242

The reason it did not work is because you have the extra quotes instead of single quotes.

i.e. You should be doing this:

IF OBJECT_ID('myOwnDb.dbo.myTable') IS NOT NULL  -- dropping the table
   DROP TABLE dbo.myTable

However, note that when you actually drop the table. You aren't even referencing the database. So you can just do:

IF OBJECT_ID('dbo.myTable') IS NOT NULL  -- dropping the table
   DROP TABLE dbo.myTable

Unless you are calling this command from another database. Then you will need to include the database name in the DROP TABLE command as well.

Upvotes: 0

Related Questions