SQL Server Drop a Table If Exists


Sometimes when you are looking for sql code online you find that people give example code that includes code that will drop (delete) a table or other object if it exists in the database. This is because they want to later create a table (or some other object).

Here is some sample code that drops a table if it exists. The name of the table in this example is called “DropMe”. All you need to do is change the table name to suit your code. You may also need to change the “dbo”.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DropMe]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[DropMe]

Here is another example below.

IF OBJECT_ID(N'[dbo].[DropMe]', N'U') IS NOT NULL
DROP TABLE [dbo].[DropMe];

Drop a schema collection if it exists.

IF EXISTS (SELECT * FROM sys.xml_schema_collections WHERE name = 'MySchema')
DROP XML SCHEMA COLLECTION [dbo].[MySchema]

Leave a comment

Your email address will not be published. Required fields are marked *