Post reply

drop object if exists

  • September 16, 2008 at 10:47 am

    #382673

    Hi how do you determine if a table exists before creating it? If it exists I want to drop it. I tried the following but it didn't work.

    if exists(Drop table myTable) :ermm:

  • September 16, 2008 at 11:01 am

    #872555

    You should use something like:

    if object_id('TABLE_NAME') is not null drop table TABLE_NAME.

    Jorge.

  • September 16, 2008 at 11:33 am

    #872581

    Thank you! 🙂

  • September 16, 2008 at 1:28 pm

    #872653

    scan table sysobjects for xtype = 'U' and table_name to avoid dropping other objects that may have the same name (accidently)

  • September 16, 2008 at 1:39 pm

    #872661

    As safer version would be this :

    if object_id('dbo.Clients', 'U') > 0

    PRINT 'drop it'

    if object_id('dbo.usp_DeleteType_appareilBypkType', 'P') > 0

    PRINT 'drop it'

    note that the schema and object types are mentionned... there can be no accidental drops there!

  • September 16, 2008 at 1:50 pm

    #872669

    I always start my object creation scripts with the rather long-winded but clearly explainable:

    IF EXISTS (SELECT * FROM sysobjects WHERE name = 'myTableName' AND xtype = 'U')

    DROP TABLE myTableName

    I think this is a neat and tidy way of doing it and you can substitute in and xtype of P and DROP PROC or xtype IN ('IF','FN','TF') and DROP FUNCTION for procedures and functions alike.


    Ade

    A Freudian Slip is when you say one thing and mean your mother.
    For detail-enriched answers, ask detail-enriched questions...[/url]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply