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
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.
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
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
|In SQL Server 2016 you can use DROP IF EXISTS. See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016.aspx|