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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQo1p4KKf-VQlQJ1hM21T_F57hXDxvhgKQ4GF5DW1xHYxYbcCUqZzvA19YFZ5aLPxFg5SlW0FPpk9xrGcYldnDwTVZAD1dF6-xFPWWxfWcQ2AY34wzwHZNKzIRKe-pnsBN5ALdBHk6S3PB/s640/if.png)
This syntax also applies to dropping Function & Stored Procedure:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgD_EmapCuhDDnD_3qvNO29NltE6GnpAG_YNntrXmebFezfAi_ba6PnwkRRl8Q06GHZiyKvAVeKrhAsAId10LmEKBTfEf7BPr63WF1ancCCYYZqI0v5gHxJem1uUf9Oc6y3RkjeQcYOQUrY/s640/if1.png)
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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQo1p4KKf-VQlQJ1hM21T_F57hXDxvhgKQ4GF5DW1xHYxYbcCUqZzvA19YFZ5aLPxFg5SlW0FPpk9xrGcYldnDwTVZAD1dF6-xFPWWxfWcQ2AY34wzwHZNKzIRKe-pnsBN5ALdBHk6S3PB/s640/if.png)
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
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgD_EmapCuhDDnD_3qvNO29NltE6GnpAG_YNntrXmebFezfAi_ba6PnwkRRl8Q06GHZiyKvAVeKrhAsAId10LmEKBTfEf7BPr63WF1ancCCYYZqI0v5gHxJem1uUf9Oc6y3RkjeQcYOQUrY/s640/if1.png)
Scripts are tested in Microsoft Azure VM - SQL Server 2016 CTP 3.2
No comments:
Post a Comment