In SQL Server 2016 many T-SQL enhancements were introduced. In this article, I am going to share one among them.
In SQL Server 2016 there is a new way to drop objects with simplified syntax.
In previous versions syntax will be as shown below: (this will also work in SQL Server 2016)
Below are some examples:
--Drop Constraint

This syntax also applies to dropping Function & Stored Procedure:

Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2
In SQL Server 2016 there is a new way to drop objects with simplified syntax.
In previous versions syntax will be as shown below: (this will also work in SQL Server 2016)
IF OBJECT_ID (N'dbo.Test_tbl', N'U') IS NOT NULL DROP TABLE dbo.Test_tbl; GO
Below are some examples:
--Drop Constraint
ALTER TABLE [dbo].[Test] DROP CONSTRAINT IF EXISTS ChckConstrnt ALTER TABLE [dbo].[Test] DROP COLUMN IF EXISTS Name --Drop Column DROP INDEX IF EXISTS CIX ON [dbo].[Test] --Drop Index DROP USER IF EXISTS Dev --Drop User DROP TABLE IF EXISTS [dbo].[Test] --Drop Table DROP VIEW IF EXISTS Vw_Test --Drop View DROP DATABASE IF EXISTS SathyaDB --Drop Database

This syntax also applies to dropping Function & Stored Procedure:
DROP FUNCTION IF EXISTS fn_test --Drop Function DROP PROCEDURE IF EXISTS Usp_Get_test --Drop Stored Procedure USE tempdb GO CREATE TABLE #temptbl(Id INT) DROP TABLE IF EXISTS #temptbl --Drop temp table

Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2
No comments:
Post a Comment