Chuyển tới nội dung
Home » Delete Table Sql Server | Delete Tables (Database Engine)

Delete Table Sql Server | Delete Tables (Database Engine)

How to DELETE COLUMN in table in SQL

How to use DROP TABLE in SQL Server

Let’s delve into the process of deleting tables in SQL Server databases using the DROP TABLE command. To illustrate how this command works, we’ll use dbForge Studio for SQL Server and execute it against the test databases Sakila.

Drop a single table from the current database

Execute the following query to drop the film_text table from the database:

DROP TABLE film_text;

As you noticed, we are currently connected to the Sakila database. The command will apply to this database by default.

Drop a single table from the different database

Suppose we need to delete a table from a specific database while working with a different one – the BikeStores database in our case. There’s no need to switch between databases; we can easily accomplish the task by specifying the required database name in the command.

DROP TABLE Sakila.dbo.rental_details;

This approach enables us to delete a table in any database on the server instance, regardless of the currently connected database.

Drop multiple tables from the database

When you have to delete multiple tables, there’s no need to run the DROP TABLE command individually for each table. Instead, you can include all the target tables in a single command, listing them and separating their names with commas:

DROP TABLE film_category, film_genre, film_text;

Drop a temporary table from the database

Temporary (temp) tables in SQL Server hold data that users retrieve from regular database tables and allow working with that data without storing such tables in memory.

By default, the temp tables are automatically deleted when the current session ends or the database connection is terminated. However, there may be situations where you need to delete a temp table before the session ends.

In such cases, the DROP TABLE command is the appropriate solution for deleting the temp table promptly.

In our database, we have the #TempTable temporary table created. Now we can remove this temporary table promptly with the following command:

DROP TABLE #TempTable;

The #TempTable has been successfully dropped.

SQL Drop Table Syntax Within a Database if Table is Owned by the dbo Schema

Passing the schema to the DROP TABLE statement is optional if the table is owned by the dbo schema.

USE [MyDatabase] GO DROP TABLE [MyTable3]; GO

As expected, the table is no longer there.

SELECT [Col1] FROM [dbo].[MyTable4]; GO

How to DELETE COLUMN in table in SQL
How to DELETE COLUMN in table in SQL

Dropping a Constraint in SQL Server

To drop a constraint in SQL Server, you’ll need to use the ALTER TABLE statement. The basic syntax of this statement is as follows:


ALTER TABLE table_name DROP CONSTRAINT constraint_name;

Where

table_name

is the name of the table that the constraint is applied to, and

constraint_name

is the name of the constraint that you want to drop.

Here’s a simple example of how you can drop a constraint in SQL Server. Let’s say you have a table called

Orders

with a primary key constraint called

PK_Orders

. To drop this constraint, you would run the following SQL statement:


ALTER TABLE Orders DROP CONSTRAINT PK_Orders;

Once you run this statement, the primary key constraint on the

Orders

table will be removed.

It’s important to note that you can only drop constraints that have been created by you, or by someone with the appropriate permissions. If you try to drop a constraint that has been created by the system, you’ll receive an error message.

Before You Begin

Limitations and Restrictions

  • You cannot drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

  • When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

  • If you drop a table that contains a varbinary (max) column with the FILESTREAM attribute, any data stored in the file system will not be removed.

  • DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

  • Any view or stored procedure that references the dropped table must be explicitly deleted or modified to remove the reference to the table.

Security

Permissions

Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

How to drop (delete) a table from your MSSQL database (SQL & SSMS).
How to drop (delete) a table from your MSSQL database (SQL & SSMS).

Using SQL Server Management Studio

To delete a table from the database
  1. In Object Explorer, select the table you want to delete.

  2. Right-click the table and choose Delete from the shortcut menu.

  3. A message box prompts you to confirm the deletion. Click Yes.

    Note

    Deleting a table automatically removes any relationships to it.

Drop More Than One Table with a Single DROP TABLE Statement

Separate the table names with a comma to drop more than table with a single Transact-SQL DROP TABLE statement.

USE [MyDatabase] GO DROP TABLE [dbo].[MyTable5],[dbo].[MyTable6]; GO

Both tables are gone.

SELECT [Col1] FROM [dbo].[MyTable5]; GO SELECT [Col1] FROM [dbo].[MyTable6]; GO

How to Delete Tables in SQL
How to Delete Tables in SQL

Using Transact-SQL

To delete a table in Query Editor
  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.


    DROP TABLE dbo.PurchaseOrderDetail;

For more information, see DROP TABLE (Transact-SQL)

Conclusion

The DROP TABLE command in SQL Server is a crucial tool for managing database objects. We have demonstrated how to use this command through SQL commands and GUI clients. One such powerful GUI client is dbForge Studio for SQL Server, which offers a wide range of functionality to handle all database tasks efficiently in SQL Server. Consequently, utilizing the DROP TABLE query is straightforward, regardless of the approach you choose.

However, it is crucial to be cautious while using this command, as it allows for the quick, convenient, and irreversible deletion of tables along with all their associated data. Understanding the consequences of this action is vital to preserve the database’s consistency and integrity.

Drop Table SQL Server Examples with T-SQL and SQL Server Management Studio

By: Joe Gavin | Updated: 2023-10-18 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | More > TSQL

Problem

Sometimes Microsoft SQL Server tables are created that are no longer needed. To help keep the SQL relational database clean there is a need to delete these unneeded tables. In this tutorial we look at how to delete a table using the DROP TABLE SQL command.

Solution

We’ll look at the syntax of the DROP TABLE statement along with examples and some gotchas to look out for when deleting SQL Server tables. Keep in mind that DROP TABLE permanently removes one or more tables in a SQL Server database, so use caution because accessing the dropped table is often times not possible.

Sql query to delete from multiple tables
Sql query to delete from multiple tables

Remarks

DROP TABLE cannot be used to drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

Multiple tables can be dropped in any database. If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.

When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

If you delete all rows in a table by using DELETE tablename or use the TRUNCATE TABLE statement, the table exists until it is dropped.

Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.

If you drop a table that contains a VARBINARY(MAX) column with the FILESTREAM attribute, any data stored in the file system will not be removed.

When a ledger table is dropped, its dependent objects (the history table and the ledger view) are also dropped. A history table or a ledger view cannot be dropped directly. The system enforces a soft-delete semantics when dropping ledger tables and its dependent objects – they are not really dropped, but instead they are marked as dropped in system catalog views and renamed. For more information, see Ledger considerations and limitations.

Important

DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

The SQL DELETE Statement

The

DELETE

statement is used to delete existing records in a table.

DELETE Syntax


DELETE FROM table_name WHERE condition;

Note: Be careful when deleting records in a table! Notice the

WHERE

clause in the

DELETE

statement.
The

WHERE

clause specifies which record(s) should be deleted. If
you omit the

WHERE

clause, all records in the table will be deleted!

How to Drop a Table in SQL | SQL Tutorial for Beginners | 2021
How to Drop a Table in SQL | SQL Tutorial for Beginners | 2021

Phản hồi

Gửi và xem ý kiến phản hồi dành cho

Summary: in this tutorial, you will learn how to use the SQL Server

DROP TABLE

statement to remove one or more tables from a database.

Sometimes, you want to remove a table that is no longer in use. To do this, you use the following

DROP TABLE

statement:


DROP TABLE [IF EXISTS] [database_name.][schema_name.]table_name;

Code language: SQL (Structured Query Language) (sql)

In this syntax:

  • First, specify the name of the table to be removed.
  • Second, specify the name of the database in which the table was created and the name of the schema to which the table belongs. The database name is optional. If you skip it, the

    DROP TABLE

    statement will drop the table in the currently connected database.
  • Third, use

    IF EXISTS

    clause to remove the table only if it exists. The

    IF EXISTS

    clause has been supported since SQL Server 2016 13.x. If you remove a table that does not exist, you will get an error. The

    IF EXISTS

    clause conditionally removes the table if it already exists.

When SQL Server drops a table, it also deletes all data, triggers, constraints, permissions of that table. Moreover, SQL Server does not explicitly drop the views and stored procedures that reference the dropped table. Therefore, to explicitly drop these dependent objects, you must use the

DROP VIEW

and

DROP PROCEDURE

statement.

SQL Server allows you to remove multiple tables at once using a single

DROP TABLE

statement as follows:


DROP TABLE [database_name.][schema_name.]table_name_1,

Code language: SQL (Structured Query Language) (sql)

[schema_name.]table_name_2, …

[schema_name.]table_name_n;

Using the DROP TABLE IF EXISTS clause

If the table we want to delete from the database does not exist as specified, SQL Server will throw an error for the DROP TABLE command.

One possible approach is to consistently verify the presence of the table intended for deletion within the database. However, this method might not be the most efficient solution. Alternatively, we can employ the IF EXISTS clause in the DROP TABLE command, which offers a more effective way to handle the situation.

The syntax would be as below:

DROP TABLE IF EXISTS table_name;

This clause verifies the existence of the table in question in the current database. If the table is found, the command proceeds to drop it. However, if the table does not exist, SQL Server simply ignores the command without raising any errors.

Note

The SQL Server DROP TABLE IF EXISTS command is specifically designed for tables and cannot be used with other database objects. Attempting to use IF EXISTS with views, triggers, or stored procedures will result in errors.

Please also be aware that the DROP TABLE IF EXISTS command was introduced in SQL Server 2016 and is not supported in earlier versions.

Let us demonstrate how the SQL Server DROP TABLE IF EXISTS command performs. Assume we want to delete the performers table from the database.

DROP TABLE IF EXISTS performers;

In this example, the IF EXISTS clause checked for the table presence in the database – it was present, and the command dropped that table successfully.

When the target table does not exist in the database, the query will be ignored by SQL Server, and no errors will occur.

DROP TABLE IF EXISTS new.rentals;

The SQL Server DROP TABLE command is a powerful yet radical method of deleting tables from databases. It completely removes the tables and frees up the occupied memory.

Before executing the DROP TABLE command in SQL Server, it is essential to double-check the tables that are intended to be dropped. Additionally, to safeguard your data, always ensure that you have backed up your database beforehand.

Sql Server - How to delete all tables at once in seconds
Sql Server – How to delete all tables at once in seconds

Feedback

Submit and view feedback for

How to drop a table in SQL Server

Database maintenance often requires deleting unnecessary tables. In SQL Server, we have multiple options to accomplish this task: the DELETE command removes rows from tables, TRUNCATE TABLE clears the table data while preserving its structure, and the DROP TABLE command deletes the table entirely.

This article will delve into the DROP TABLE command in SQL Server, providing insights on how to delete single or multiple tables from the database. Furthermore, it will demonstrate how to ensure the existence of a table before proceeding with its deletion.

Syntax


-- Syntax for SQL Server, Azure SQL Database, Warehouse in Microsoft Fabric DROP TABLE [ IF EXISTS ] { database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,...n ] [ ; ]


-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse DROP TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } [;]

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

GIẢI HEARTSTEEL CUP BẮC MỸ: ROBINSONGZ THÀNH THẠO CON BÀI BARD BỤT THỔI KÈN FAST 10 KÈM 4 JAZZ
GIẢI HEARTSTEEL CUP BẮC MỸ: ROBINSONGZ THÀNH THẠO CON BÀI BARD BỤT THỔI KÈN FAST 10 KÈM 4 JAZZ

Conclusion

Dropping a constraint in SQL Server is a relatively simple process. All you need to do is use the

ALTER TABLE

statement with the

DROP CONSTRAINT

clause. With this knowledge, you should now be able to remove constraints that are no longer needed, or that are causing problems in your databases.

We hope this post has been helpful in showing you how to drop a constraint in SQL Server. If you have any questions or comments, please let us know in the comments section below.

This article gives an overview of the SQL DROP TABLE statement to remove one or more tables from a database.

In my earlier article, Difference between SQL Truncate and SQL Delete statements in SQL Server, we explored to delete data from an existing data. We might delete whole data using both SQL Delete and SQL Truncate statements. We might also delete specific data from the SQL Server tables using SQL Delete statement. SQL Delete and Truncate do not move the object structure from the database.

Sometimes, we do want to perform database clean up by removing unnecessary tables. Let’s say you want to make bulk changes to a table. Most of the DBA’s take table-level backup before making any change to it. It involves creating another backup table in a similar database with a different name.

In the following table, we want to delete multiple records.

10

11

12

13

14

15

16

17

SELECT [BusinessEntityID]

,[NationalIDNumber]

,[LoginID]

,[OrganizationNode]

,[OrganizationLevel]

,[JobTitle]

,[BirthDate]

,[MaritalStatus]

,[Gender]

,[HireDate]

,[SalariedFlag]

,[VacationHours]

,[SickLeaveHours]

,[CurrentFlag]

,[rowguid]

,[ModifiedDate]

FROM [AdventureWorks2017].[HumanResources].[Employee]

Before removing the data, take a backup using SELECT INTO command.

10

11

12

13

14

15

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];

It created another table with existing column structure and copies the data into it. 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 extra overhead to the system.

Let’s explore the SQL DROP TABLE in the next action.

Using SQL Server Management Studio

To delete a database
  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Expand Databases, right-click the database to delete, and then select Delete.

  3. Confirm the correct database is selected, and then select OK.

How to uninstall SQL Server completely on Windows 10?
How to uninstall SQL Server completely on Windows 10?

Arguments

database_nameIs the name of the database in which the table was created.

Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #. Azure SQL Database does not support four-part names.

IF EXISTSApplies to: SQL Server ( SQL Server 2016 (13.x) through current version).

Conditionally drops the table only if it already exists.

schema_nameIs the name of the schema to which the table belongs.

table_nameIs the name of the table to be removed.

DROP TABLE SQL Server T-SQL Syntax

DROP TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,…n ] [ ; ]

Permissions Required for DROP TABLE Statement

DROP TABLE requires the user to have one of the following:

  • ALTER permission on the table’s schema
  • CONTROL permission on the table
  • Membership in the db_ddladmin fixed database role
Example Data

First, we’ll create a database called MyDatabase, create six simple tables in it called MyTable1 through MyTable6 with a basic table definition then insert a value in each. Here are the basic DDL and DML SQL statements:

CREATE DATABASE [MyDatabase]; GO USE [MyDatabase]; GO CREATE TABLE [dbo].[MyTable1] ( [Col1] [int] NULL ); GO CREATE TABLE [dbo].[MyTable2] ( [Col1] [int] NULL ); GO CREATE TABLE [dbo].[MyTable3] ( [Col1] [int] NULL ); GO CREATE TABLE [dbo].[MyTable4] ( [Col1] [int] NULL ); GO CREATE TABLE [dbo].[MyTable5] ( [Col1] [int] NULL ); GO CREATE TABLE [dbo].[MyTable6] ( [Col1] [int] NULL ); GO INSERT INTO [dbo].[MyTable1] (Col1) VALUES (1); GO INSERT INTO [dbo].[MyTable2] (Col1) VALUES (1); GO INSERT INTO [dbo].[MyTable3] (Col1) VALUES (1); GO INSERT INTO [dbo].[MyTable4] (Col1) VALUES (1); GO INSERT INTO [dbo].[MyTable5] (Col1) VALUES (1); GO INSERT INTO [dbo].[MyTable6] (Col1) VALUES (1); GO

These SQL queries show the tables and data in each of them.

SELECT [Col1] FROM [dbo].[MyTable1]; SELECT [Col1] FROM [dbo].[MyTable2]; SELECT [Col1] FROM [dbo].[MyTable3]; SELECT [Col1] FROM [dbo].[MyTable4]; SELECT [Col1] FROM [dbo].[MyTable5]; SELECT [Col1] FROM [dbo].[MyTable6];

Connect to SQL Server Using Visual Studio 2022 and Run SQL Queries (Create Read Update Delete)
Connect to SQL Server Using Visual Studio 2022 and Run SQL Queries (Create Read Update Delete)

Prerequisites

  • Delete any database snapshots that exist on the database. For more information, see Drop a Database Snapshot (Transact-SQL).

  • If the database is involved in log shipping, remove log shipping.

  • If the database is published for transactional replication, or published or subscribed to merge replication, remove replication from the database.

Warning

Consider taking a full backup of the database before dropping it. A deleted database can be re-created only by restoring a full backup. For more information, see Quickstart: Backup and restore a SQL Server database on-premises.

Security

Permissions

To execute DROP DATABASE, at a minimum, a user must have CONTROL permission on the database.

DROP TABLE Syntax

The DROP TABLE command in SQL Server effectively removes the entire table from the database, including its structure, all stored data, indexes, triggers, and constraints.

The basic syntax of the DROP TABLE command is:

DROP TABLE table_name;

Where:

table_name is the name of the table to be removed.

By default, this command operates within the current database. However, if your objective is to remove a table from a different database, you must include the database_name parameter in the command:

DROP TABLE [database_name].table_name;

Note

When a table is referenced by a FOREIGN KEY constraint, direct deletion becomes impossible. To address this, you must first delete the referencing table before you can drop the target table successfully.
Moreover, the DROP TABLE command does not delete functions, stored procedures, or views that refer to the table we intend to delete. Hence, you need to ensure that the table’s deletion won’t impact the functionality. Alternatively, you can choose to delete the relevant stored procedures and views as well if it aligns with your requirements.

Học SQL Server 40. Câu lệnh DELETE xóa dữ liệu trong bảng
Học SQL Server 40. Câu lệnh DELETE xóa dữ liệu trong bảng

Examples

Dropping a table in the current database

The following example removes the

ProductVendor1

table and its data and indexes from the current database.


DROP TABLE ProductVendor1 ;

Dropping a table in another database

The following example drops the

SalesPerson2

table in the

AdventureWorks2022

database. The example can be executed from any database on the server instance.


DROP TABLE AdventureWorks2022.dbo.SalesPerson2 ;

Dropping a temporary table

The following example creates a temporary table, tests for its existence, drops it, and tests again for its existence. This example does not use the IF EXISTS syntax which is available beginning with SQL Server 2016 (13.x).


CREATE TABLE #temptable (col1 INT); GO INSERT INTO #temptable VALUES (10); GO SELECT * FROM #temptable; GO IF OBJECT_ID(N'tempdb..#temptable', N'U') IS NOT NULL DROP TABLE #temptable; GO --Test the drop. SELECT * FROM #temptable;

Dropping a table using IF EXISTS

Applies to: SQL Server ( SQL Server 2016 (13.x) through current version).

The following example creates a table named T1. Then the second statement drops the table. The third statement performs no action because the table is already deleted, however it does not cause an error.


CREATE TABLE T1 (Col1 INT); GO DROP TABLE IF EXISTS T1; GO DROP TABLE IF EXISTS T1;

How to apply DROP TABLE in dbForge Studio

In our demonstration, we utilized T-SQL commands using the GUI client, dbForge Studio for SQL Server. However, the Studio provides the flexibility to either execute SQL commands directly against the databases or perform the task visually through the Database Explorer interface. This user-friendly approach simplifies the process of table deletion.

  • 1. Expand the Tables node for the necessary database.
  • 2. Select the table you want to delete.
  • 3. Right-click the table and select Delete from the context menu.

The Delete option in this menu operates as the DROP TABLE command, deleting the entire table permanently.

The Database Explorer feature in dbForge Studio for SQL Server offers an additional benefit. It provides a visual means to identify tables that may have references to the table you wish to delete.

  • 1. Expand the specific table mode in the Database Explorer.
  • 2. Check the Depends On folder.

This feature allows you to view all tables linked to the target table, making it easy to verify dependencies and eliminate tables or other objects that could hinder the deletion process. Furthermore, dbForge Studio for SQL Server can generate scripts to create or delete multiple objects simultaneously, enabling you to drop all related objects with a single click.

Connect to SQL Server Using Visual Studio Code 2022 and Run SQL Queries (Create Read Update Delete)
Connect to SQL Server Using Visual Studio Code 2022 and Run SQL Queries (Create Read Update Delete)

Before You Begin

Limitations and Restrictions

  • You cannot drop a table that is referenced by a FOREIGN KEY constraint. The referencing FOREIGN KEY constraint or the referencing table must first be dropped. If both the referencing table and the table that holds the primary key are being dropped in the same DROP TABLE statement, the referencing table must be listed first.

  • When a table is dropped, rules or defaults on the table lose their binding, and any constraints or triggers associated with the table are automatically dropped. If you re-create a table, you must rebind the appropriate rules and defaults, re-create any triggers, and add all required constraints.

  • If you drop a table that contains a varbinary (max) column with the FILESTREAM attribute, any data stored in the file system will not be removed.

  • DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.

  • Any view or stored procedure that references the dropped table must be explicitly deleted or modified to remove the reference to the table.

Security

Permissions

Requires ALTER permission on the schema to which the table belongs, CONTROL permission on the table, or membership in the db_ddladmin fixed database role.

Understanding Constraints in SQL Server

Before we dive into how to drop a constraint, let’s take a quick look at what constraints are and what they do in SQL Server.

A constraint is a rule that restricts the values that can be entered into a column in a table. There are several types of constraints in SQL Server, including primary key, foreign key, unique, check, and default constraints.

Constraints help you ensure the accuracy and consistency of your data. For example, you can use a primary key constraint to ensure that each row in a table has a unique identifier, or a foreign key constraint to ensure that a value in one table corresponds to a value in another table.

PHP and MySQL with CRUD Operations: Create, Read, Update, Delete
PHP and MySQL with CRUD Operations: Create, Read, Update, Delete

Feedback

Submit and view feedback for

SQL Server DELETE or DROP Table

SQL Server allows DBA to remove a table, which is not relevant or obsolete in our database. When we remove a table, the complete data and the whole structure or definition are deleted permanently from the table. Therefore, we should have to be very careful while dropping a table from the specified database. If the SQL Server does not find the target database table, it will throw an error message.

SQL Server provides mainly two ways to remove a table:

Let us discuss them one by one in detail.

DROP TABLE using T-SQL command

We can use the following syntax to remove a table in SQL Server:

In the above syntax,

If we want to delete more than one table within a single command, we can use the comma-separated list of table names as below:

When we remove a table in SQL Server, it also erases all of the table’s data, triggers, constraints, and permissions. This query does not allow deletion of the views, user-defined functions, and stored procedures explicitly referencing the dropped table. Hence, we should use the DROP VIEW and DROP PROCEDURE statements if we want to remove these objects explicitly.

We must remember the following points before deleting a table:

NOTE: We must have an ALTER privilege on the schema to which the table belongs and CONTROL privilege on the table to execute the DROP TABLE statement.
Example

This example specifies how we can drop an existing table from the database. Suppose our database has a table named “student_data” containing the following data:

If we want to drop this table from the database, we can use the statement as follows:

It will remove the table permanently. If we re-access this table, we will get an error message as shown in the below output:

If we try to remove a table that does not exist in the database, SQL Server will through the following error message:

However, if we try to delete a table that is not available in the database with the IF EXISTS clause, SQL Server does not show any error or warning message. See the below output:

DROP TABLE using SQL Server Management Studio

We can also use the SSMS to drop a table from the database. The following steps explain how to delete a table using SSMS:

Step 1: Open the SSMS and then navigate to the Object Explorer-> Database -> Tables folder. It will show all available tables.

Step 2: Select your desired table that is no longer required in your database and right-clicks on it to get the pop menu. Now, select the Delete option from the appeared menu. See the below image:

Step 3: Once we click on the Delete option, we will see the Delete Model Object box to confirm the deletion process. Click on the OK button to delete the database from the SQL Server permanently.

Step 6: If everything is correct, the selected table will be removed from the database. We can verify this by clicking the refresh button or pressing the F5 on our keyboard to update the object list. We can see that the ‘backup_employee’ table is no longer available.

This article helps to learn how to remove the table from a database permanently using SQL query and SQL Server Management Studio.

Next TopicSQL Server Insert Data

How to drop a table in SQL Server

Database maintenance often requires deleting unnecessary tables. In SQL Server, we have multiple options to accomplish this task: the DELETE command removes rows from tables, TRUNCATE TABLE clears the table data while preserving its structure, and the DROP TABLE command deletes the table entirely.

This article will delve into the DROP TABLE command in SQL Server, providing insights on how to delete single or multiple tables from the database. Furthermore, it will demonstrate how to ensure the existence of a table before proceeding with its deletion.

Dropping Tables That Has a Foreign Key Relationship

Let’s create a couple of simple tables with Transact-SQL called Parent and Child with the Child table having a Foreign Key in the Parent table then populate them with one row each.

CREATE TABLE [dbo].[Parent] ( ID INT PRIMARY KEY, Name VARCHAR(10) NULL ); GO INSERT INTO [dbo].[Parent]([ID],[NAME]) VALUES (1,’Joe’); GO CREATE TABLE [dbo].[Child] ( CID INT PRIMARY KEY, ID INT ); GO ALTER TABLE [dbo].[Child] ADD CONSTRAINT FK_Parent_Child FOREIGN KEY (CID) REFERENCES [Parent](ID) GO INSERT INTO [dbo].[Child] (CID,ID) VALUES (1,1); GO SELECT * FROM [dbo].[Parent]; GO SELECT * FROM [dbo].[Child] ; GO

We’ll see the Parent table can’t be dropped where there is a relationship to the Child table which is the referencing table.

DROP TABLE [dbo].[Parent]; GO

We get the error ‘Msg 3726, Level 16, State 1, Line 156 Could not drop object ‘dbo.Parent’ because it is referenced by a FOREIGN KEY constraint.’.

But we’re successful if we drop the Child table first, then the Parent table.

DROP TABLE [dbo].[Child]; GO DROP TABLE [dbo].[Parent]; GO

And to drop the Parent table without dropping the Child Table, we can drop the Primary Key Constraint first then the table.

ALTER TABLE [dbo].[Child] DROP CONSTRAINT FK_ID; GO DROP TABLE [dbo].[Parent]; GO

Office Hours Speed Round
Office Hours Speed Round

Using Transact-SQL

For more information, see DROP DATABASE (Transact-SQL).

To delete a database
  1. Connect to the Database Engine.

  2. From the Standard bar, select New Query.

  3. Copy and paste the following example into the query window and select Execute. This example removes the


    Sales

    and

    NewSales

    databases.


USE master ; GO DROP DATABASE Sales, NewSales ; GO

DROP TABLE Syntax

The DROP TABLE command in SQL Server effectively removes the entire table from the database, including its structure, all stored data, indexes, triggers, and constraints.

The basic syntax of the DROP TABLE command is:

DROP TABLE table_name;

Where:

table_name is the name of the table to be removed.

By default, this command operates within the current database. However, if your objective is to remove a table from a different database, you must include the database_name parameter in the command:

DROP TABLE [database_name].table_name;

Note

When a table is referenced by a FOREIGN KEY constraint, direct deletion becomes impossible. To address this, you must first delete the referencing table before you can drop the target table successfully.
Moreover, the DROP TABLE command does not delete functions, stored procedures, or views that refer to the table we intend to delete. Hence, you need to ensure that the table’s deletion won’t impact the functionality. Alternatively, you can choose to delete the relevant stored procedures and views as well if it aligns with your requirements.

EP 1 Complete Employees Management System Using ASPNET Core MVC, EF Core,SQL|AdminLTE| Perform CRUD💥
EP 1 Complete Employees Management System Using ASPNET Core MVC, EF Core,SQL|AdminLTE| Perform CRUD💥

SQL DELETE Example

The following SQL statement deletes the customer “Alfreds Futterkiste” from the “Customers” table:

Example

The “Customers” table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country
Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

Using Transact-SQL

To delete a table in Query Editor
  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.


    DROP TABLE dbo.PurchaseOrderDetail;

For more information, see DROP TABLE (Transact-SQL)

SQL Server Insert, Delete and Update Triggers
SQL Server Insert, Delete and Update Triggers

Conclusion

The DROP TABLE command in SQL Server is a crucial tool for managing database objects. We have demonstrated how to use this command through SQL commands and GUI clients. One such powerful GUI client is dbForge Studio for SQL Server, which offers a wide range of functionality to handle all database tasks efficiently in SQL Server. Consequently, utilizing the DROP TABLE query is straightforward, regardless of the approach you choose.

However, it is crucial to be cautious while using this command, as it allows for the quick, convenient, and irreversible deletion of tables along with all their associated data. Understanding the consequences of this action is vital to preserve the database’s consistency and integrity.

SQL Server is a popular and powerful relational database management system. It provides a variety of constraints to help you enforce data integrity in your databases. However, sometimes you may want to remove a constraint because it’s no longer needed, or because it’s causing problems. In this post, we’ll show you how to drop a constraint in SQL Server.

A Database Manager That Is Modern, Fast, & Easy To Use

Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990’s feel to them – Allan

I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree – they love using Beekeeper and they tell me every day! Give it a try, I bet you’ll like it too.

Overview of SQL DROP TABLE

We use the SQL DROP Table command to drop a table from the database. It completely removes the table structure 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 need to drop them explicitly. We 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 following parameters.

  • Database_name: Specify the database name in which table exists. We can skip this parameter if we execute the drop 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. We must specify the schema name if the object belongs other than the default schema
  • Table name: Specify the table that we want to remove

Example 1: Drop a single table using the SQL DROP Table statement

Execute the following query to drop HumanResources.Employee13072019 table from the AdventureWorks2017 database.

Drop table Employee13072019

It gives the following error message. We get this error because the object belongs to the default schema dbo.

To fix this, we need to specify schema name along with the table name.

Drop table AdventureWorks2017.HumanResources.Employee13072019

Alternatively, we can use the following query to drop a SQL table.

Use AdventureWorks2017

Go

Drop table HumanResources.Employee13072019

Example 2: Drop multiple tables together using the SQL DROP Table statement

We can drop multiple tables together using a single DROP Table statement as well.

Let’s create three tables and later we will drop it.

CREATE TABLE Sam(id INT);

CREATE TABLE Amp(id INT);

CREATE TABLE Rmp(id INT);

Now, we can use the following drop table statement and specify all table names together to drop it.

DROP TABLE Sam, Amp, Rmp;

Example 2: Drop a SQL table having a foreign key constraint using the SQL DROP Table statement

In SQL Server, we can use a foreign key between multiple table columns to link data between these tables. We cannot drop the table directly in this case.

Let’s understand this using an example. We will create two SQL tables Department and Employee. In the database diagram, you can see that we have a foreign key constraint for on the Dept_id column

Execute the following script to create both tables.

10

CREATE TABLE Department

(Dept_id INT IDENTITY PRIMARY KEY,

Dept_name VARCHAR(50) NOT NULL

);

CREATE TABLE Employee1

(EmpID INT IDENTITY PRIMARY KEY,

EmpName VARCHAR(50) NOT NULL,

Dept_id INT NOT NULL,

FOREIGN KEY(Dept_id) REFERENCES department(dept_id)

);

Let’s try to drop departments table using SQL DROP TABLE statement.

Drop table Department

You get the following error message.

Foreign key relationships are like a parent-child relationship. We cannot delete a parent table that is referenced by a foreign key constraint. We need to either remove the foreign key relationship or drop the child table first.

In my example, we need to drop the Employee1 table first because it has a foreign key relationship with the department table.

Drop table Employee1

DROP TABLE Department;

We can use a single SQL Drop Table statement as well in such case with a caution to drop the referencing table first.

DROP TABLE Employee1, Department;

Example 3: Drop a temp table using the SQL DROP Table statement

We can also drop a temp table in a way similar to the regular table. The following example creates a temp table and drops it.

CREATE TABLE #temp1(col1 INT);

GO

INSERT INTO #temp1

VALUES(100);

GO

DROP TABLE #temp1;

Example 4: Dropping a table using IF EXISTS

Usually, developers check for the existence of any database object in the database and drop the object if it exists.

If we try to drop a table that does not exist, we get the following error message.

We do not want any 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 department table in the sys .objects.

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

IF EXISTS

SELECT *

FROM sys.objects

WHERE object_id = OBJECT_ID(N’Department’)

AND type IN(N’U’)

DROP TABLE Department;

GO

Alternatively, we can check the object id of the SQL table and execute the drop table statement if it is not NULL.

IF OBJECT_ID(‘Department’, ‘U’) IS NOT NULL

DROP TABLE Department;

GO

These approaches work fine. However, the issue is that you need to write a long transact SQL code. Starting from SQL Server 2016, we 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 code we executed before. Let’s try to drop the department table using this new code. It is a small and easy way to drop a table.

DROP TABLE IF EXISTS Department;

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

SQL Server does not give any error message if you drop a SQL table that is being used in the stored procedure, views. We can use the SCHEMABINDING option, but it is not in the scope of this article.

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

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

MySQL: How to UPDATE and DELETE data from a TABLE
MySQL: How to UPDATE and DELETE data from a TABLE

How to use DROP TABLE in SQL Server

Let’s delve into the process of deleting tables in SQL Server databases using the DROP TABLE command. To illustrate how this command works, we’ll use dbForge Studio for SQL Server and execute it against the test databases Sakila.

Drop a single table from the current database

Execute the following query to drop the film_text table from the database:

DROP TABLE film_text;

As you noticed, we are currently connected to the Sakila database. The command will apply to this database by default.

Drop a single table from the different database

Suppose we need to delete a table from a specific database while working with a different one – the BikeStores database in our case. There’s no need to switch between databases; we can easily accomplish the task by specifying the required database name in the command.

DROP TABLE Sakila.dbo.rental_details;

This approach enables us to delete a table in any database on the server instance, regardless of the currently connected database.

Drop multiple tables from the database

When you have to delete multiple tables, there’s no need to run the DROP TABLE command individually for each table. Instead, you can include all the target tables in a single command, listing them and separating their names with commas:

DROP TABLE film_category, film_genre, film_text;

Drop a temporary table from the database

Temporary (temp) tables in SQL Server hold data that users retrieve from regular database tables and allow working with that data without storing such tables in memory.

By default, the temp tables are automatically deleted when the current session ends or the database connection is terminated. However, there may be situations where you need to delete a temp table before the session ends.

In such cases, the DROP TABLE command is the appropriate solution for deleting the temp table promptly.

In our database, we have the #TempTable temporary table created. Now we can remove this temporary table promptly with the following command:

DROP TABLE #TempTable;

The #TempTable has been successfully dropped.

Archive table before deleting

We may want to archive an existing table prior to dropping it. This can be done using SELECT INTO which will create a table that’s the same as the source table and then populate it with the source table’s values, effectively making a copy.

To demonstrate, we’ll create a new table called MyTable7 and insert a few records into it.

CREATE TABLE [dbo].[MyTable7] ( emp_num INT IDENTITY(1,1), name varchar (20) NOT NULL ); GO INSERT INTO [dbo].[MyTable7] (name) VALUES (‘a’); INSERT INTO [dbo].[MyTable7] (name) VALUES (‘b’); INSERT INTO [dbo].[MyTable7] (name) VALUES (‘c’); GO

SELECT * INTO [dbo].[MyTable7_archived] FROM [dbo].[MyTable7]; GO

We now have two tables with the same data and we can then drop the original table, if needed. This way we have a temporary table of sorts to store the data, if necessary

SELECT * FROM [dbo].[MyTable7] SELECT * FROM [dbo].[MyTable7_archived]

How to Create and Drop Table in SQL
How to Create and Drop Table in SQL

Using Azure Data Studio (preview)

To delete a database
  1. In Object Explorer, connect to an instance of the SQL Server Database Engine, and then expand that instance.

  2. Expand Databases, right-click the database to delete, and then select Delete.

  3. To confirm you want to delete the database, select Yes.

TRUNCATE TABLE

The

TRUNCATE TABLE

command deletes the data inside a table, but
not the table itself.

The following SQL truncates the table “Categories”:

Example

TRUNCATE TABLE Categories;

DROP TABLE (Transact-SQL)

Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
Warehouse in Microsoft Fabric

Removes one or more table definitions and all data, indexes, triggers, constraints, and permission specifications for those tables. Any view or stored procedure that references the dropped table must be explicitly dropped by using DROP VIEW or DROP PROCEDURE. To report the dependencies on a table, use sys.dm_sql_referencing_entities.

Transact-SQL syntax conventions

MySQL: Deleting Tables
MySQL: Deleting Tables

Using SQL Server Management Studio

To delete a table from the database
  1. In Object Explorer, select the table you want to delete.

  2. Right-click the table and choose Delete from the shortcut menu.

  3. A message box prompts you to confirm the deletion. Click Yes.

    Note

    Deleting a table automatically removes any relationships to it.

Drop a Table in One Database from Another Database

To drop a table from another database (applies to on-premises not Azure SQL Database) we simply give it the database and names in the form [database_name].[schema_name].[table_name].

USE master GO DROP TABLE [MyDatabase].[dbo].[MyTable2]; GO

And if we try to select from the table we get an error that shows us the table is no longer there.

SELECT [Col1] FROM [MyDatabase].[dbo].[MyTable2];

[Khóa học sử dụng SQL server] - Bài 5: Insert, delete, update table| HowKteam
[Khóa học sử dụng SQL server] – Bài 5: Insert, delete, update table| HowKteam

SQL Server DROP TABLE examples

Let’s see some examples of using the SQL Server

DROP TABLE

statement.

A) Drop a table that does not exist

The following statement removes a table named

revenues

in the

sales

schema:


DROP TABLE IF EXISTS sales.revenues;

Code language: SQL (Structured Query Language) (sql)

In this example, the

revenues

table does not exist. Because it uses the

IF EXISTS

clause, the statement executes successfully with no table deleted.

B) Drop a single table example

The following statement creates a new table named

delivery

in the

sales

schema:


CREATE TABLE sales.delivery ( delivery_id INT PRIMARY KEY, delivery_note VARCHAR (255) NOT NULL, delivery_date DATE NOT NULL );

Code language: SQL (Structured Query Language) (sql)

To remove the

delivery

table, you use the following statement:


DROP TABLE sales.delivery;

Code language: SQL (Structured Query Language) (sql)

C) Drop a table with a foreign key constraint example

The following statement creates two new tables named

supplier_groups

and

suppliers

in the

procurement

schema:


CREATE SCHEMA procurement; GO CREATE TABLE procurement.supplier_groups ( group_id INT IDENTITY PRIMARY KEY, group_name VARCHAR (50) NOT NULL ); CREATE TABLE procurement.suppliers ( supplier_id INT IDENTITY PRIMARY KEY, supplier_name VARCHAR (50) NOT NULL, group_id INT NOT NULL, FOREIGN KEY (group_id) REFERENCES procurement.supplier_groups (group_id) );

Code language: SQL (Structured Query Language) (sql)

Let’s try to drop the

supplier_groups

table:


DROP TABLE procurement.supplier_groups;

Code language: SQL (Structured Query Language) (sql)

SQL Server issued the following error:


Could not drop object 'procurement.supplier_groups' because it is referenced by a FOREIGN KEY constraint.

Code language: SQL (Structured Query Language) (sql)

SQL Server does not allow you to delete a table that is referenced by a foreign constraint. To delete this table, you must drop the referencing foreign key constraint or referencing table first. In this case, you have to drop the foreign key constraint in the

suppliers

table or the

suppliers

table first before removing the

supplier_groups

table.


DROP TABLE procurement.supplier_groups; DROP TABLE procurement.suppliers;

Code language: SQL (Structured Query Language) (sql)

If you use a single

DROP TABLE

statement to remove both tables, the referencing table must be listed first as shown in the query below:


DROP TABLE procurement.suppliers, procurement.supplier_groups;

Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL Server

DROP TABLE

statement to remove one or more tables from a database.

SQL DROP TABLE and TRUNCATE TABLE Keywords

Demo Database

Below is a selection from the Customers table used in the examples:

CustomerID CustomerName ContactName Address City PostalCode Country
Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
34 How do I DELETE data from SQL table
34 How do I DELETE data from SQL table

Using the DROP TABLE IF EXISTS clause

If the table we want to delete from the database does not exist as specified, SQL Server will throw an error for the DROP TABLE command.

One possible approach is to consistently verify the presence of the table intended for deletion within the database. However, this method might not be the most efficient solution. Alternatively, we can employ the IF EXISTS clause in the DROP TABLE command, which offers a more effective way to handle the situation.

The syntax would be as below:

DROP TABLE IF EXISTS table_name;

This clause verifies the existence of the table in question in the current database. If the table is found, the command proceeds to drop it. However, if the table does not exist, SQL Server simply ignores the command without raising any errors.

Note

The SQL Server DROP TABLE IF EXISTS command is specifically designed for tables and cannot be used with other database objects. Attempting to use IF EXISTS with views, triggers, or stored procedures will result in errors.

Please also be aware that the DROP TABLE IF EXISTS command was introduced in SQL Server 2016 and is not supported in earlier versions.

Let us demonstrate how the SQL Server DROP TABLE IF EXISTS command performs. Assume we want to delete the performers table from the database.

DROP TABLE IF EXISTS performers;

In this example, the IF EXISTS clause checked for the table presence in the database – it was present, and the command dropped that table successfully.

When the target table does not exist in the database, the query will be ignored by SQL Server, and no errors will occur.

DROP TABLE IF EXISTS new.rentals;

The SQL Server DROP TABLE command is a powerful yet radical method of deleting tables from databases. It completely removes the tables and frees up the occupied memory.

Before executing the DROP TABLE command in SQL Server, it is essential to double-check the tables that are intended to be dropped. Additionally, to safeguard your data, always ensure that you have backed up your database beforehand.

Drop Table Statement Within a Database

To drop a table from within the current database (on-premises or Azure SQL Database) we simply pass the name of the table in the form [schema_name].[table_name].

USE [MyDatabase] GO DROP TABLE [dbo].[MyTable3]; GO

As before, we get an error that shows us the table is no longer there trying when we try to select from it.

SELECT [Col1] FROM [dbo].[MyTable3];

MS SQL Server 2019 | How to Delete rows in a Table using GUI and SQL
MS SQL Server 2019 | How to Delete rows in a Table using GUI and SQL

How to apply DROP TABLE in dbForge Studio

In our demonstration, we utilized T-SQL commands using the GUI client, dbForge Studio for SQL Server. However, the Studio provides the flexibility to either execute SQL commands directly against the databases or perform the task visually through the Database Explorer interface. This user-friendly approach simplifies the process of table deletion.

  • 1. Expand the Tables node for the necessary database.
  • 2. Select the table you want to delete.
  • 3. Right-click the table and select Delete from the context menu.

The Delete option in this menu operates as the DROP TABLE command, deleting the entire table permanently.

The Database Explorer feature in dbForge Studio for SQL Server offers an additional benefit. It provides a visual means to identify tables that may have references to the table you wish to delete.

  • 1. Expand the specific table mode in the Database Explorer.
  • 2. Check the Depends On folder.

This feature allows you to view all tables linked to the target table, making it easy to verify dependencies and eliminate tables or other objects that could hinder the deletion process. Furthermore, dbForge Studio for SQL Server can generate scripts to create or delete multiple objects simultaneously, enabling you to drop all related objects with a single click.

Conclusion

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

  • How to install PostgreSQL on Ubuntu – July 13, 2023
  • How to use the CROSSTAB function in PostgreSQL – February 17, 2023
  • Learn the PostgreSQL COALESCE command – January 19, 2023

Delete Tables (Database Engine)

Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

You can delete (drop) a table from your database in SQL Server by using SQL Server Management Studio or Transact-SQL.

Caution

Think carefully before you delete a table. If existing queries, views, user-defined functions, stored procedures, or programs refer to that table, the deletion will make these objects invalid.

In This Topic

  • Before you begin:

  • To Delete a Table, using:

How To Delete Data from the Table with SQL Script | Microsoft SQL Server 2017 for Everyone
How To Delete Data from the Table with SQL Script | Microsoft SQL Server 2017 for Everyone

Delete a Table

To delete the table completely, use the

DROP TABLE

statement:

Example

Remove the Customers table:

Delete Tables (Database Engine)

Applies to:
SQL Server 2016 (13.x) and later
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)

You can delete (drop) a table from your database in SQL Server by using SQL Server Management Studio or Transact-SQL.

Caution

Think carefully before you delete a table. If existing queries, views, user-defined functions, stored procedures, or programs refer to that table, the deletion will make these objects invalid.

In This Topic

  • Before you begin:

  • To Delete a Table, using:

Rename SQL Server Table

Another scenario is if we need to archive a table before creating a new table with the same name. We can do this with the built-in stored procedure, sp_rename.

Create a table called MyTable and insert some values into it.

CREATE TABLE [dbo].[MyTable8] ( emp_num INT IDENTITY(1,1), name varchar (20) NOT NULL ); GO INSERT INTO [dbo].[MyTable8] (name) VALUES (‘d’); INSERT INTO [dbo].[MyTable8] (name) VALUES (‘e’); INSERT INTO [dbo].[MyTable8] (name) VALUES (‘f’); GO

Now, we execute sp_rename in the form ‘EXEC sp_rename ‘old_name’, ‘new_name’. Square brackets, if any, need to be removed and we omit the schema name from ‘new_name’. Otherwise, the brackets and / or schema become part of the new name. The SQL Server generates this friendly warning: ‘Caution: Changing any part of an object name could break scripts and stored procedures.’

— remove brackets, enclose each table in single quotes, and omit schema from new name EXEC sp_rename ‘dbo.MyTable8’, ‘MyTable8_renamed’

Attempting to select from [dbo].[MyTable8] fails saying the object is invalid.

SELECT * FROM [dbo].[MyTable8];

But here we see selecting from [dbo].[MyTable8_renamed] is successful.

SELECT * FROM [dbo].[MyTable8_renamed];

Alternatively, to rename a table in the SSMS GUI, we’ll create and populate one more table, called MyTable9.

CREATE TABLE [dbo].[MyTable9] ( emp_num INT IDENTITY(1,1), name varchar (20) NOT NULL ); GO INSERT INTO [dbo].[MyTable9] (name) VALUES (‘g’); INSERT INTO [dbo].[MyTable9] (name) VALUES (‘h’); INSERT INTO [dbo].[MyTable9] (name) VALUES (‘i’); GO

In the SSMS Objects Explorer

  1. Right click on the table name
  2. Rename
  1. Enter new table name
  2. Click outside the text box

And the table is renamed.

Next Steps

Here are links with some further information:

  • DROP TABLE IF EXISTS Examples for SQL Server
  • Drop Index Example in SQL Server
  • The T-SQL DELETE statement
  • Drop table from a published SQL Server database
  • Solving Table Drop Error Related to SQL Server Replication’s sp_MStran_ddlrepl
  • INSERT INTO new SQL table with SELECT INTO
  • Script to rename constraints and indexes to conform to a SQL Server naming convention
  • Differences between Delete and Truncate Table in SQL Server
  • SQL Server 2016 Truncate Table with Partitions
About the author

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-10-18

SQL Tutorial #25 - How to Delete Data in SQL Table
SQL Tutorial #25 – How to Delete Data in SQL Table

Keywords searched by users: delete table sql server

Sql Server Tutorial 39: Deleting Data From Tables - Youtube
Sql Server Tutorial 39: Deleting Data From Tables – Youtube
The Internals Of Sql Truncate And Sql Delete Statements
The Internals Of Sql Truncate And Sql Delete Statements
How To Create And Drop Table In Sql - Youtube
How To Create And Drop Table In Sql – Youtube
Sql Query To Delete A Data From A Table Based On Date - Geeksforgeeks
Sql Query To Delete A Data From A Table Based On Date – Geeksforgeeks
Sql Server Drop Table If Exists Examples
Sql Server Drop Table If Exists Examples
Sql Delete Statement: Best Ways To Use It
Sql Delete Statement: Best Ways To Use It
Delete Data Using Delete Statement In Sql Server
Delete Data Using Delete Statement In Sql Server
Insert, Delete, Update Table Trong Sql Server | How Kteam
Insert, Delete, Update Table Trong Sql Server | How Kteam
Hệ Quản Trị Sql Server - Xóa Bảng Sql Server
Hệ Quản Trị Sql Server – Xóa Bảng Sql Server
Delete Data Using Delete Statement In Sql Server
Delete Data Using Delete Statement In Sql Server

See more here: kientrucannam.vn

Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *