Drop Table if Exists
-
Posted on August 30, 2012 by Derek Dieter
-
1
To determine if a table exists, it’s best to go against the sys.objects view by querying the object_id using the fully qualified name of the table. The additional ‘type’ field in the where clause ensures that the table that is about to be dropped is a User table and not a system table.
Use caution when dropping tables. Once you drop a table, you will not be able to get it back unless you restore a backup.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DumpMe]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[DumpMe]
DROP TABLE [dbo].[DumpMe]
To drop a temp table you need to look in the tempdb database for it’s existence.
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
BEGIN
DROP TABLE #Temp
END
BEGIN
DROP TABLE #Temp
END
Once again, I would highly recommend double checking your code prior to issuing these statements.
Post a comment
- Comments (RSS)
- Trackback
- Permalink
