Delete table in MSSQL db

We can delete a table from MSSQL database by using DROP table command ( Query ) . Let us try with simple example first. While executing this command in any .asp page we must ensure that we have connected to MSSQL database with proper userid and password. We are not providing the connection string and associated code here but you can download the total code by downloading the file at the end of the page.

You can test this query in your Microsoft SQL Server Management Studio

drop table member

The above command will delete the table member from the database. What happens we run the same query again. You will get an error message like this

Error Type:
Microsoft OLE DB Provider for SQL Server (0x80040E37)
Cannot drop the table 'dbo.member', because it does not exist or you do not have permission.
signup/sql.asp, line 25

The reason is simple , we have already deleted the table so we can't delete again. Then how to check the existence of the table before deleting to avoid the error message ? We will modify our query and add the command IF EXISTS to it.

if exists (SELECT * FROM sysobjects WHERE name = 'member')drop table member

We can also search INFORMATION_SCHEMA to check if table exists or not.

if exists (select * from INFORMATION_SCHEMA.TABLES where table_name ='member') drop table member

Number of User Comments : 1


Jovan Popovic


In SQL Server 2016 you can use DROP IF EXISTS. See

Post Comment This is for short comments only. Use the forum for more discussions.

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2015 All rights reserved worldwide Privacy Policy Disclaimer