Should I Create Tables in Sql to Drop Them Again

This commodity gives an overview of the SQL Drib TABLE argument to remove one or more tables from a database.

In my earlier commodity, Difference between SQL Truncate and SQL Delete statements in SQL Server, nosotros explored to delete data from an existing data. We might delete whole information using both SQL Delete and SQL Truncate statements. We might also delete specific data from the SQL Server tables using SQL Delete argument. SQL Delete and Truncate do non move the object structure from the database.

Sometimes, nosotros practice want to perform database clean up by removing unnecessary tables. Let'due south say yous want to make majority changes to a tabular array. Most of the DBA'due south take table-level backup before making any alter to it. It involves creating another backup table in a similar database with a different proper name.

In the post-obit table, we want to delete multiple records.

ane 2 3 4 5 half-dozen 7 eight nine 10 xi 12 13 fourteen fifteen 16 17

SELECT [ BusinessEntityID ] , [ NationalIDNumber ] , [ LoginID ] , [ OrganizationNode ] , [ OrganizationLevel ] , [ JobTitle ] , [ BirthDate ] , [ MaritalStatus ] , [ Gender ] , [ HireDate ] , [ SalariedFlag ] , [ VacationHours ] , [ SickLeaveHours ] , [ CurrentFlag ] , [ rowguid ] , [ ModifiedDate ] FROM [ AdventureWorks2017 ] . [ HumanResources ] . [ Employee ]

Earlier removing the data, take a backup using SELECT INTO control.

ane 2 3 four 5 vi 7 8 9 ten eleven 12 13 14 xv 16 17 18

SELECT [ BusinessEntityID ] , [ NationalIDNumber ] , [ LoginID ] , [ OrganizationNode ] , [ OrganizationLevel ] , [ JobTitle ] , [ BirthDate ] , [ MaritalStatus ] , [ Gender ] , [ HireDate ] , [ SalariedFlag ] , [ VacationHours ] , [ SickLeaveHours ] , [ CurrentFlag ] , [ rowguid ] , [ ModifiedDate ] INTO [ AdventureWorks2017 ] . [ HumanResources ] . [ Employee13072019 ] FROM [ AdventureWorks2017 ] . [ HumanResources ] . [ Employee ] ;

Information technology created some other tabular array with existing cavalcade structure and copies the data into information technology. We need to perform regular clean-up of these backup tables. It takes unnecessary disk space and if you do index maintenance for all indexes, it might add actress overhead to the organisation.

Let's explore the SQL DROP Table in the adjacent action.

Overview of SQL Driblet TABLE

Nosotros use the SQL Drib Tabular array command to driblet a table from the database. Information technology completely removes the table construction and associated indexes, statistics, permissions, triggers and constraints. You might have SQL Views and Stored procedures referencing to the SQL table. SQL Server does not remove these stored procedures and views. We demand to drop them explicitly. Nosotros should check object dependencies before removing a SQL table.

The syntax for SQL DROP Table

DROP Table [ database_name ] . [ schema_name ] . [ table_name ]

It uses the post-obit parameters.

• Database_name: Specify the database name in which table exists. We can skip this parameter if we execute the drib command in the current database context
• Schema_name: Specify the schema name for which the object exists. If the object belongs to the default schema DBO, we can skip this parameter. SQL Server automatically uses dbo schema. Nosotros must specify the schema proper name if the object belongs other than the default schema
• Table proper name: Specify the table that we want to remove

Instance 1: Drib a unmarried table using the SQL DROP Table statement

Execute the post-obit query to drop HumanResources.Employee13072019 table from the AdventureWorks2017 database.

Drop table Employee13072019

It gives the following error bulletin. We go this error because the object belongs to the default schema dbo.

To gear up this, we demand to specify schema name forth with the table name.

Drop table AdventureWorks2017 . HumanResources . Employee13072019

Alternatively, we tin can utilise the following query to driblet a SQL tabular array.

Use AdventureWorks2017 Go Driblet table HumanResources . Employee13072019

Instance 2: Driblet multiple tables together using the SQL Drop Table statement

We tin drop multiple tables together using a single Drib Table statement too.

Let's create three tables and later nosotros will drop information technology.

CREATE TABLE Sam ( id INT ) ; CREATE Tabular array Amp ( id INT ) ; CREATE Table Rmp ( id INT ) ;

At present, we can employ the post-obit drib table statement and specify all tabular array names together to drop it.

DROP TABLE Sam , Amp , Rmp ;

Instance 2: Drop a SQL tabular array having a foreign key constraint using the SQL Driblet Table statement

In SQL Server, we tin use a strange cardinal between multiple table columns to link data between these tables. We cannot drib the table directly in this instance.

Let's understand this using an example. Nosotros will create ii SQL tables Section and Employee. In the database diagram, you tin meet that nosotros accept a foreign key constraint for on the Dept_id column

Execute the following script to create both tables.

CREATE Table Department ( Dept_id INT IDENTITY Master Primal , Dept_name VARCHAR ( 50 ) Non Naught ) ; CREATE TABLE Employee1 ( EmpID INT IDENTITY PRIMARY Central , EmpName VARCHAR ( 50 ) NOT NULL , Dept_id INT Not NULL , Foreign KEY ( Dept_id ) REFERENCES department ( dept_id ) ) ;

Let's effort to drop departments table using SQL DROP TABLE statement.

You lot get the following fault message.

Strange fundamental relationships are like a parent-kid human relationship. We cannot delete a parent table that is referenced by a foreign key constraint. We demand to either remove the foreign key relationship or drop the child table first.

In my instance, we demand to driblet the Employee1 table first because it has a foreign cardinal relationship with the section table.

Drop table Employee1 DROP Table Department ;

We tin use a unmarried SQL Drop Table statement as well in such case with a circumspection to drop the referencing table first.

DROP TABLE Employee1 , Section ;

Example iii: Drib a temp table using the SQL DROP Table statement

We tin too drop a temp tabular array in a way similar to the regular table. The following example creates a temp table and drops information technology.

CREATE Tabular array #temp1 ( col1 INT ) ; Go INSERT INTO #temp1 VALUES ( 100 ) ; GO DROP TABLE #temp1 ;

Instance 4: Dropping a table using IF EXISTS

Usually, developers check for the being of any database object in the database and drib the object if information technology exists.

If we endeavour to drop a tabular array that does non be, we get the following fault message.

We do not want whatever error in executing queries, especially during the execution of a bunch of code. Before SQL Server 2016, developers use the IF EXISTS statement and check for the object existence before dropping it.

For example, in the following query, we check the section tabular array in the sys .objects.

If the object exists, execute the drop table argument else, no actions required.

IF EXISTS ( SELECT * FROM sys . objects WHERE object_id = OBJECT_ID ( N 'Section' ) AND blazon IN ( N 'U' ) ) Drop Tabular array Department ; Go

Alternatively, we can cheque the object id of the SQL tabular array and execute the drop table argument if it is non NULL.

IF OBJECT_ID ( 'Department' , 'U' ) IS NOT Goose egg DROP TABLE Department ; Become

These approaches work fine. However, the issue is that you need to write a long transact SQL code. Starting from SQL Server 2016, we tin can use the new syntax of SQL DROP Table. It drops a SQL table if it already exists.

DROP Table IF EXISTS Table_name

It is a short version of the lawmaking we executed before. Let'south try to drop the department table using this new code. It is a small and easy way to driblet a table.

DROP Tabular array IF EXISTS Department ;

Example five: Dropping a table having a reference in the stored procedures, views

SQL Server does not give any error bulletin if you driblet a SQL table that is being used in the stored procedure, views. We can use the SCHEMABINDING option, just it is not in the scope of this commodity.

Nosotros can check the dependencies of an object using SSMS. Right-click on a table and click on View Dependencies. It opens a dissever window and displays the dependencies.

You can look at the dependencies and resolve it so that the procedures and views tin can function correctly after dropping this object.

Conclusion

In this article, nosotros explored the SQL Driblet Table statement for removing the objects from the SQL database. You should be careful before dropping whatsoever object in the production database.

• Author
• Contempo Posts

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, ready related bug, and Performance Tuning with over xiv years of experience.

I am the author of the volume "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of 1 of the biggest free online collections of manufactures on a unmarried topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I take been recognized equally the prestigious Best Author of the Twelvemonth continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://world wide web.dbblogger.com
I am always interested in new challenges so if you need consulting help, accomplish me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

costigancornind.blogspot.com

Source: https://www.sqlshack.com/an-overview-of-a-sql-drop-table-statement/

Share this post