Update: Added separate versions for SQL Server 2000 and SQL Server 2005, due to the differences in the system objects tables.It may just be me, but when writing migration/create scripts for use with SQL Server I get quite agitated at having to write an ugly, long-winded, drop statement at the start of every object definition.

The support for dropping objects is one of the few things I would say MySQL has SQL Server over the barrel for.Baring in mind that if you try to drop an object that doesn’t exist, you’ll get an execution error; here’s how to drop a table in MySQL:

DROP TABLE IF EXISTS customers

Here’s how to drop the same table, if you’re using SQL Server:

IF EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'customers') AND type = (N'U'))    DROP TABLE customers

As always, when something annoys you enough and you’re in the middle of something else, it’s about time you wrote that solution. So I’ve created a simple user-defined function that checks if an object exists then returns a BIT 0 or 1 depending.

To use the function, all you have to do is call dbo.ObjectExists with two parameters, the first being the name of the object you want to check on, the second being the type of object.

IF dbo.ObjectExists('customers', 'U') = 1
    DROP TABLE customers
Common Object Types:
P – Stored Procedure
U – User Table
FN – User-Defined Function

Thanks to this little function, you can now almost match the simplicity of MySQL.

…and now the code

SQL Server 2000

CREATE FUNCTION dbo.ObjectExists(@Object VARCHAR(100), @Type VARCHAR(2)) RETURNS BIT
AS
BEGIN
    DECLARE @Exists BIT
    IF EXISTS(SELECT 1 FROM dbo.sysobjects WHERE [ID] = OBJECT_ID(@Object) AND type = (@Type))
        SET @Exists = 1
    ELSE
        SET @Exists = 0
    RETURN @Exists
END

SQL Server 2005

CREATE FUNCTION dbo.ObjectExists(@Object VARCHAR(100), @Type VARCHAR(2)) RETURNS BIT
AS
BEGIN
    DECLARE @Exists BIT
    IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(@Object) AND type = (@Type)) 
       SET @Exists = 1
    ELSE
        SET @Exists = 0
    RETURN @Exists
END
Tags:

Comments...

  1. When you say one of the few things MySQL has SQL Server over the barrel, you might add support for spatial data types. It is an enduring mystery why in these times of ubiquitous spatial data and location based services MS hasn’t seen fit to offer what Postgres, Oracle and MySQL have for years. There is, of course, also the not insignificant issue of cost and open source code.

    By John — 9 Sep, 2007 @ 8:14 am

  2. Amen. Perhaps I should have said “one of the many things…”.

    Microsoft always seem to be behind the curve with most things. I’d say that mainstream use of spatial data has been minimal in the past few years, enough so that it hasn’t been a priority for Microsoft. However, with the boom of Google Maps style applications they’re really going to need to play catch-up.

    By James Gregory — 9 Sep, 2007 @ 8:26 am

  3. IF OBJECT_ID(N’TR_Campaigns_BEFORE_UPDATE’, N’TR’) IS NOT NULL

    This one seems to work for me.

    By Peter — 10 Oct, 2007 @ 2:49 am

  4. I found that the last bit of code:
    ‘IF OBJECT_ID(N’TR_Campaigns_BEFORE_UPDATE’, N’TR’) IS NOT NULL’
    doesn’t work…it says the command has completed successfully but it doesn’t actually drop the table, as when I try to re-create the table it already exists.

    Thank you all very much for your help on this topic – was a real life saver, I’ve been getting really annoyed with having to manually drop all my temp tables!

    By Rose — 9 Sep, 2008 @ 9:46 am

  5. Great Posting James! – this is exactly what I was looking for!

    Keep up the great work!
    kr

    By Karl Rove — 3 Mar, 2009 @ 9:19 pm

  6. I Realize this is an older post, but this might be helpful, and you don’t need a custom function

    To check the existance of a table, I use the following

    IF OBJECT_ID(‘dbo.[TABLE_NAME','U') IS NOT NULL
    DROP TABLE dbo.[TABLE_NAME]
    GO

    It takes a lot less effort and code, and works just as well as using the IF EXISTS(SELECT * FROM SYSOBJECTS…..) method.

    Try it sometime!

    By Chris — 3 Mar, 2009 @ 2:03 pm

  7. Thanks Chris, that’s actually what I do nowadays :)

    By James Gregory — 3 Mar, 2009 @ 2:08 pm

  8. One advantage of Chris’s method is that it finds temporary tables (if directed to the tempdb, as in IF OBJECT_ID(‘tempdb..#temp’) IS NOT NULL, where the original code does not. Whether you should be using temp tables or not…well…

    By Brendan Hemens — 4 Apr, 2009 @ 2:54 pm

Post a comment...