locked
If exist drop doesn't work RRS feed

  • Question

  • Hi guys, I got these statement:

    IF EXISTS(SELECT name FROM sys.tables WHERE name = 'Checkcolumn' )
    	begin
    	DROP TABLE Checkcolum
    	end
    	IF EXISTS(SELECT name FROM sys.tables WHERE name = 'Checkcolumnfinal' )
    	begin
    	DROP TABLE CHECKCOLUMNfinal
    	end

    Now..if the table doesn't exist no problem but if the table exist it return me the error 

    Cannot delete the object Checkcolumn, because it does not exist or you do not have permission. 

    But if I drop the table outside the code above with a simple Drop Table Checkcolumn it's ok. No error. 

    Now, I know it's monday but....why?

    Monday, October 21, 2013 10:14 AM

Answers

  • Try the below:

    Create Table CheckColumn(Col1 int)
    Select * From CheckColumn
    
    
    IF object_id('Checkcolumn','U') is not null
    	begin
    		Drop table CheckColumn
    	end
    --Select * From CheckColumn
    
    	
    	

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Durval Ramos Monday, October 21, 2013 10:54 AM
    • Marked as answer by Allen Li - MSFT Thursday, October 31, 2013 1:13 AM
    Monday, October 21, 2013 10:52 AM
  • yeah, its Monday....

    IF EXISTS(SELECT name FROM sys.tables WHERE name = 'Checkcolumn' )
    	begin
    	DROP TABLE Checkcolum --Where is "n"????
    	end


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 21, 2013 10:56 AM

All replies

  • it is giving error that checkcolum doesnot exist. what is the problem in that

    make sure that you have selected the database  while executing it

    check the sample code below

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[d]') AND type in (N'U'))
    DROP TABLE [dbo].[d]
    GO

    USE [DB_Size1]
    GO

    /****** Object:  Table [dbo].[d]    Script Date: 10/21/2013 11:22:32 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[d](
        [dt] [date] NULL
    ) ON [PRIMARY]

    GO



    Ramesh Babu Vavilla MCTS,MSBI




    Monday, October 21, 2013 10:22 AM
  • Try the below:

    Create Table CheckColumn(Col1 int)
    Select * From CheckColumn
    
    
    IF object_id('Checkcolumn','U') is not null
    	begin
    		Drop table CheckColumn
    	end
    --Select * From CheckColumn
    
    	
    	

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Proposed as answer by Durval Ramos Monday, October 21, 2013 10:54 AM
    • Marked as answer by Allen Li - MSFT Thursday, October 31, 2013 1:13 AM
    Monday, October 21, 2013 10:52 AM
  • yeah, its Monday....

    IF EXISTS(SELECT name FROM sys.tables WHERE name = 'Checkcolumn' )
    	begin
    	DROP TABLE Checkcolum --Where is "n"????
    	end


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Monday, October 21, 2013 10:56 AM