As mentioned, we must use the three-part name to locate the table ( db_name.schema_name.tbl_name). In the above example, the SQL Server queries to locate the table. Thus, we must use the three-part name in the query: if exists (select 1 from where name='tblStudents') The temporary tables are created in tempdb. Suppose you want to drop the temporary table named #tblStudent. ĭrop Temporary Table using DROP IF EXISTS In case that the IF EXISTS condition evaluates TRUE, it will execute the DROP TABLE statement.įor the SQL Server 2016 and higher, we can drop the tblStudent table using the DROP TABLE IF EXISTS statement: DROP TABLE IF EXISTS. In the above example, our SQL Server queries sys.tables to locate the table. If you are using SQL Server 2014 and older version, you can run the following query: if exists (select 1 from sys.tables where name='tblStudents') Suppose you want to drop the tblStudent table. Now, we are going to examine the DROP IF EXISTS in different use cases with examples. Obj_name – specify the object name which you want to drop.Obj_Type – specifies the object type and applies to any of the following objects:.The syntax of the DROP TABLE IF EXISTS is the following: DROP OBJ_TYPE OBJ_NAME It drops the existing object specified in that statement. What about specific database objects?Ī new and extremely useful SQL Statement DROP OBJECT IF EXISTS has been introduced in SQL Server 2016. We see that there is an efficient way - DROP temp table if exists - to remove any temp table that we don’t need anymore. Note: To populate various database objects and their details, you can use any of the following DMVs Database ObjectsĪlternatively, you can use sys.all_objects to populate the database objects.
0 kommentar(er)
