Are you running a company with a focus on big data? You aren’t alone.
One survey showed that 32% of companies have a formal big data strategy. These companies tend to be far more profitable than businesses that do not utilize big data.
However, some companies have to learn the hard way that desiring to utilize big data is not enough. They have to develop the right infrastructure to take advantage of it effectively. This entails using SQL servers appropriately.
One of the things that you need to understand while running a data-driven company is how to use drop tables with SQL servers.. This article shows how you can drop tables in SQL Server using a variety of different methods and applications. Companies using big data will have an easier time if they do this.
Using Drop Tables for Your Data-Driven Business
Depending upon the client application that you use to manage your SQL Server, there are multiple ways of dropping tables in SQL Server.
Some of the ways involve SQL queries on command line terminals or query windows, while the other ways allow you to drop tables in SQL Server by simply right clicking the table name and clicking “delete” or “drop” option.
In this article, you will review five different techniques of dropping tables in SQL Server:
- Drop a table Using SQLCMD Utility
- Drop a table Using SQL Server Management Studio Query Window
- Drop a table Using SQL Server Management Studio GUI
- Drop a table Using SQL Query Window in dbForge Studio for SQL Server
- Drop a Tables Using GUI in dbForge Studio for SQL Server
Creating a Dummy Database
In this article, you will be executing your examples on a fictional “Library” database with two tables: “‘Book” and “Author”.
The “Author” and “Book” tables will have a one-to-many relationship.
The relationship will be implemented via a foreign key column in the “Book” table that references the primary key of the “Author” table.
Run the following script to create your dummy database and populate it with sample data:
| CREATE DATABASE Library USE Library CREATE TABLE Author ( Id INT IDENTITY PRIMARY KEY, Name varchar(255), Age INT ); CREATE TABLE Book ( Id INT IDENTITY PRIMARY KEY, Name varchar(255), Author_id INT FOREIGN KEY (Author_id) REFERENCES Author(Id) ); INSERT INTO Author VALUES(‘John’, 35), ( ‘Mike’, 40), ( ‘Sara’, 25), ( ‘Nick’, 22) INSERT INTO Book VALUES(‘History of Mathematics’, 2), (‘Practical Python’, 3) |
You can check the data in the “Author” and “Book” tables via the following script:
| SELECT * FROM Author SELECT * FROM Book |
Output:
Drop Tables Using SQLCMD Utility
SQLCMD is a command-line tool developed by Microsoft.
You can use this tool to interact with SQL Server instances and perform various database management and administration tasks including dropping tables from SQL Server databases.
You can download the SQLCMD tool from the following link:
https://docs.microsoft.com/en-us/sql/tools/sqlcmd-utility?view=sql-server-ver15
To run the SQLCMD tool, open the “Run” shell, and enter the command:
“sqlcmd -S server_name -E”.
Replace the “server_name” in the above command with the name of the SQL Server that you want to connect to.
The flag “-E” in the above command specifies that windows authentication is enabled to access the SQL Server. In case the Windows Authentication is not enabled, you will have to replace -E with the “-U your_user -P your_password” command.
Once you run the above command, the SQLCMD utility will open where you can execute SQL commands, as shown in the script below:
Let’s print all the tables in our dummy Library database before we delete a table.
| SELECT name FROM Library.sys.tables |
The output shows names of all the tables in the “Library” database. You can see the “Author” and “Book” table.
Let’s now look at how you can drop a table from an SQL Server database. To do so, you have to execute the DROP TABLE command.
To avoid any exceptions, it is always better to check if the table exists before dropping it. You can append the IF EXISTS command with the DROP TABLE command to check if the table exists before dropping it.
Finally, after the IF EXISTS command, you have to pass the table name that you want to delete. The full syntax of an SQL query to delete a table is as follows:
| DROP TABLE IF EXISTS Table_Name |
Let’s delete the “Book” table from our dummy Library database.
| USE Library DROP TABLE IF EXISTS Book |
Execute the following script to see the remaining tables in the Library database.
| SELECT name FROM Library.sys.tables |
You can see that the “Book” table is now dropped, and you can only see the “Author” table.
Let’s see what happens if you try to drop the “Author” table whose “Id” column is referenced as a foreign key column in the “Book” table.
Before executing the script below, create your dummy Library database from scratch and make sure that your Library Database contains both “Book” and “Author” tables where the “Book” table has a foreign key column “Author_id” which references the “Id” column of the “Author” table.
Next, try to delete the “Author” table by executing the following command:
| USE Library DROP TABLE IF EXISTS Author |
From the above output, you can see the error message that the object could not be dropped because it is referenced by a FOREIGN key constraint.
This error occurs because the “Book” table has records that reference the “Author” table.
Therefore, in case if a table is referenced by another table via a FOREIGN key constraint, you first have to drop the table that is referencing the original.
In our case, before dropping the “Author” table, you have to drop all the tables that reference the “Author” table. Hence, you need to drop the “Book” table, before the “Author” table. After that you can drop the “Book” table too.
Drop Table Using SQL Server Management Studio
You can also drop tables in SQL Server via Microsoft’s SQL Server Management Studio which is a GUI-based management tool that allows you to perform database administration tasks on SQL Server instances.
SQL Server Management Studio contains a query window where you can directly execute SQL queries. Hence, you can use the DROP TABLE IF EXISTS query to drop tables in SQL Server via SQL Server Management Studio.
The other option is to drop a table via a few mouse clicks.
You will see both the methods in the following sections:
Drop Tables Using SQL Query Window
To open the Query window in SQL Server Management Studio, click the “New Query” option from the main dashboard of your SQL Server Management Studio as shown in the following screenshot.
You can also see your Library database and the “Author” and “Book” tables in the “Object Explorer” window on the left.
The script for dropping a table via the SQL query window is the same as the query you executed in SQLCMD.
| USE Library DROP TABLE IF EXISTS Book |
In the output message window you can see that the command is successfully executed.
You can see the remaining tables in your Library database using the following command.
| SELECT name FROM Library.sys.tables |
Or you can refresh your “Object Explorer” and then go to “Databases -> Library -> Tables” to see the remaining tables in the Library database.
It is once again important to mention that you cannot drop the “Author” table before the “Book” table since the “Author” table is referenced by the “Book” table via a FOREIGN Key constraint.
Drop Tables Using SQL Server Management Studio GUI
You can also drop a table via SQL Server Management Studio GUI.
To do so right click on the table that you want to drop. From the list of options that appear select “Delete” as shown in the following screenshot.
You will see the following Window, here click the “OK” button to drop the table.
If you have not dropped the “Book” table before the “Author” table, you will see the following error which basically says that the “Author” table cannot be removed since it is referenced by another table via a FOREIGN key constraint.
You can see the dependencies for your table by clicking the “Show Dependencies” button as shown below:
Since the “Book” table depends on the “Author” table, you can see the dependency in the following screen.
If you remove the “Book” table before the “Author” table, you will see that there is no table or object upon the “Author” table.
If you see no dependency, you can go back to the previous window and click the “OK” button to drop the “Author” table, as shown below:
Drop Tables Using dbForge Studio for SQL Server
You can drop tables in SQL Server using the dbForge Studio for SQL Server which is a state-of-the-art, fully-featured IDE that allows you to perform a range of database management, administration, and manipulation tasks on SQL Server.
SQL Studio provides a user-friendly interface to perform a variety of tasks on SQL Server.
Among many other tasks, you can drop tables from an SQL Server using the dbForge Studio for SQL Server.
You have two options to drop tables from SQL Server databases. You can either use
the query window for executing SQL scripts. Or you can drop a table by right-clicking the table name and then selecting the “Delete” option. You will see both these methods in the next section.
Connecting dbForge Studio with SQL Server
You need to connect dbForge Studio to an SQL Server Instance before you can perform any operations on the SQL Server instance.
To connect the dbForge Studio to an SQL Server instance, click the “New Connection” button from the main dashboard of dbForge Studio.
The “Database Connection Properties” window will appear as shown below.
In the “Server” field, enter the SQL Server instance name that you want to connect to. Also, select the appropriate authentication mode. Enter user and password if needed and click the “Test Connection” button.
You will see the following message, if your connection is successful.
Drop Tables Using SQL Query Window in dbForge Studio
To open a new SQL query window, click the “New SQL” option from the top left menu of the dbForge Studio dashboard. A new window will open where you can write your SQL queries. Look at the following screenshot for reference.
Let’s delete the “Book” table from our dummy Library database. Execute the following script in the dbForge Studio New SQL query window.
| USE Library DROP TABLE IF EXISTS Book |
When you run the above script, dbForge Studio displays an alert box that tells you that this operation can result in data loss and if you are sure to execute this command? If you want to delete your table anyway, click the “Execute Anyway” button.
The output below shows that the query was successful.
To see the list of remaining tables in the Library database, execute the script below:
| SELECT name FROM Library.sys.tables |
In the output, you can see the “Book” table has been deleted and we are only left with the “Author” table.
Drop Tables Using GUI in dbForge Studio
To drop tables via the GUI interface in dbForge Studio for SQL Server, right click the table that you want to drop. From the list of options, select “Delete”. Here is a screenshot for your reference where we delete the “Author” table from the Library database.
A message box will appear asking you if you really want to delete the “Author” table, as shown in the screenshot below. Click the “Yes” button and the table will be deleted.
Data-Driven Businesses Must Use Drop Tables Appropriately
In this article we have reviewed 5 different ways that you can drop a table in SQL using SQLCMD, SSMS, and dbForge Studio for SQL Server. As a data-driven company, you have to make the most of these commands to manage your data, because they will have a huge impact on the success of your data strategy.