DROP IF EXITS in SQL server 2016
Before SQL server 2016 released , when we want drop any objects , we should first check the object is exists or not otherwise it will return a error.
And taking one table whose name is "SQLSmartTable" for future reference.
DROP TABLE [SQLSmartTable]
If the object is not found it will give an error like :
OR
we can check the object existence in different ways like
IF EXISTS(SELECT 'Y' FROM sys.objects AS O WHERE O.name = 'SQLSmartTable AND O.[type] = 'U')
DROP TABLE [SQLSmartTable]
--OR
IF OBJECT_ID('dbo.SQLSmartTable','U') IS NOT NULL
DROP TABLE [SQLSmartTable]
But in SQL server 2016 we can handle this situation in different way , with the help of
DROP IF EXISTS .
DROP TABLE IF EXISTS [SQLSmartTable]
If we want to drop the same database
DROP TABLE IF EXISTS [SQLSMARTDB].
Example :
DROP TABLE IF EXISTS [SQLSmartTable12]
Here the above table "SQLSmartTable12" is not exits , but when we will execute this command it will not through any error . This is so nice.
This DROP IF EIXTS(DIE) functionality is not only limited for dropping a table and database , we can use it for dropping other type of objects like
.ASSEMBLY
.VIEW
.DATABASE
.DEFAULT
.FUNCTION
.SECURITY POLICY
.SEQUENCE
.SYNONYM
.TABLE
.TRIGGER
.PROCEDURE
.INDEX
.AGGREGATE
.ROLE
.RULE
.SCHEMA
.TYPE
.USER
If think it will be helpful . Let me know if you have any problem .
NOTE :
In this case we are considering a Database whose name is "SQLSMARTDB".And taking one table whose name is "SQLSmartTable" for future reference.
DROP TABLE [SQLSmartTable]
If the object is not found it will give an error like :
Msg 3701, Level 11, State 5, Line 14 Cannot drop the table 'SQLSmartTable', because it does not exist or you do not have permission.
OR
we can check the object existence in different ways like
IF EXISTS(SELECT 'Y' FROM sys.objects AS O WHERE O.name = 'SQLSmartTable AND O.[type] = 'U')
DROP TABLE [SQLSmartTable]
--OR
IF OBJECT_ID('dbo.SQLSmartTable','U') IS NOT NULL
DROP TABLE [SQLSmartTable]
But in SQL server 2016 we can handle this situation in different way , with the help of
DROP IF EXISTS .
Example:
If you want to drop the above table(SQLSmartTable) in 2016 SQL Server you can write the SQL command like this
DROP TABLE IF EXISTS [SQLSmartTable]
If we want to drop the same database
DROP TABLE IF EXISTS [SQLSMARTDB].
Note :
If suppose the object is not exits , it will not fail and execution will continue.
Example :
DROP TABLE IF EXISTS [SQLSmartTable12]
Here the above table "SQLSmartTable12" is not exits , but when we will execute this command it will not through any error . This is so nice.
This DROP IF EIXTS(DIE) functionality is not only limited for dropping a table and database , we can use it for dropping other type of objects like
.ASSEMBLY
.VIEW
.DATABASE
.DEFAULT
.FUNCTION
.SECURITY POLICY
.SEQUENCE
.SYNONYM
.TABLE
.TRIGGER
.PROCEDURE
.INDEX
.AGGREGATE
.ROLE
.RULE
.SCHEMA
.TYPE
.USER
If think it will be helpful . Let me know if you have any problem .
Comments
Post a Comment