Simplified drop statement in SQL Server 2016


Krishna KV
Team Leader, Aspire Systems
Published On :   08 Jul 2016
Visit Count
Today :  1    Total :   6759
Plan, Migrate, Secure, Report
SharePoint & Office 365 Tool. Simple & Easy to Use. 15-Day Trial!

Sharegate: Kick-Ass Tool
Think Your SharePoint & Office 365 Are Secure ? Find Out Now!


SQL Server 2016 makes the drop and if exists into a single statement. It will check for the object existence. If the object exists, it will execute the drop statement. Else it will continue with the next statement.

Before SQL Server 2016

 IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
  DROP TABLE dbo.test;	
 		 
 		      (or)
 
 IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES  WHERE TABLE_NAME = 'Test')
 DROP TABLE dbo.Test
 

This statement can be written in SQL Server 2016 as:

 CREATE TABLE test(id INT,name VARCHAR(200),rollno INT  CONSTRAINT unq UNIQUE)
 GO
 
 CREATE PROCEDURE usp_test AS 
 BEGIN
 SELECT * FROM test
 END 

To delete a column

 ALTER TABLE dbo.test DROP COLUMN  IF EXISTS rollno

To delete a constraint

 ALTER TABLE dbo.test DROP CONSTRAINT IF EXISTS unq

To delete a table

 DROP TABLE IF EXISTS  dbo.test

To delete a procedure

 DROP PROCEDURE IF EXISTS dbo.usp_test
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Categories

KWizCom Scan