Sql DROP Statement

The SQL DROP TABLE statement is used to delete or remove a database table.

The SQL DROP TABLE statement will remove the database table completely (it removes all table records and table structure).

You may need to delete one or more columns from a table if they are unwanted or obsolete. You can use the ALTER TABLE DROP column statement to remove one or more columns from an existing table at that time.

It will assist you in removing the column as well as all of its data.

SQL Server

A column can’t be dropped when it’s:

• Used in an index as a key column or an INCLUDE clause.
• In a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint, this value is used.
• Related with a default object or one declared with the DEFAULT keyword.
• Bound by a set of rules.

MySQL

When columns are eliminated from a table, they are likewise removed from any indexes that contain them. When all of the columns that make up an index are removed, the index is also removed.

PostgreSQL

• The column's indexes and table restrictions will be automatically removed.
• If the omitted column causes multivariate statistics to only have data for a single column, the statistics will be eliminated as well.

SQLite

SQLite doesn’t support the DROP COLUMN syntax. You can’t drop a column in SQLite.

1. Syntax For Delete Single Column

ALTER TABLE table_name
DROP COLUMN column_name;

The name of the table from which the column should be removed is table_name. Replace table_name with the name of the table.

The name of the column you want to remove is column_name. Replace column_name with the name of the column.

2.Syntax For Delete Multiple Columns

ALTER TABLE table_name
DROP COLUMN column_name_1, column_name_2,...;

Example 1: Dropping a Single Column from a Table:

1. The following statement drops the city column from the Employee table:

ALTER TABLE Employee
DROP COLUMN city;

Example 2: Dropping Multiple Columns from a Table

The following statement drops the date_of_birth, phone, email column from the Employee table:

ALTER TABLE Employee
DROP COLUMN date_of_birth, phone, email;

2. You can drop several columns in a single ALTER TABLE query in some RDBMs. RDBMS have different syntax.

You can just list each column in SQL Server, divided by a comma:

ALTER TABLE t1 
DROP COLUMN c1, c2;

Example 3: Drop the Last Column:

To drop the last remaining column in the table, I used the following statement in PostgreSQL.

ALTER TABLE t1 
DROP COLUMN c3;

But in SQL Server, if I do the same:

ALTER TABLE t1 
DROP COLUMN c1, c2, c3;

Output:

Despite the error message's language, c3 was not the sole column that remained. There were three columns in total. If the other two had been dropped, c3 would have been the last one standing. None of the three columns were actually dropped in this example.

Example 4: SQL Server ALTER TABLE DROP COLUMN examples:

Step 1: Let’s create a new table named sales.price_lists for the demonstration.

CREATE TABLE sales.price_lists(
    product_id int,
    valid_from DATE,
    price DEC(10,2) NOT NULL CONSTRAINT ck_positive_price CHECK(price >= 0),
    discount DEC(10,2) NOT NULL,
    surcharge DEC(10,2) NOT NULL,
    note VARCHAR(255),
    PRIMARY KEY(product_id, valid_from)
); 

Step 2: The following statement drops the note column from the price_lists table:

ALTER TABLE sales.price_lists
DROP COLUMN note;

Step 3: You can't delete the price column because it contains a CHECK constraint. If you try to run the following command, you will get the following error:

ALTER TABLE sales.price_lists
DROP COLUMN price;

Here is the error message:

Step 4: To drop the price column, first, delete its CHECK constraint:

ALTER TABLE sales.price_lists
DROP CONSTRAINT ck_positive_price;

And then, delete the price column:

ALTER TABLE sales.price_lists
DROP COLUMN price;

The following example deletes two columns discount and surcharge at once:

ALTER TABLE sales.price_lists
DROP COLUMN discount, surcharge;

The DROP INDEX statement deletes one or more indexes from the current database. Use the DROP INDEX command to delete a non-primary key index.

PRIMARY KEY and UNIQUE restrictions do not erase indexes established by the DROP INDEX statement. The ALTER TABLE DROP CONSTRAINT statement is used to remove indexes connected with these constraints.

The command DROP INDEX removes the index index name from the table tbl_ name. To drop the index, this statement is mapped to an ALTER TABLE statement.

A metadata lock is in effect if another connection is using the table, and this statement will wait until the lock is released before continuing. Non-transactional tables are no exception.

All index partitions are removed when you eliminate a global partitioned index, a range-partitioned index, or a hash-partitioned index. When you drop a composite-partitioned index, it also drops all of the index partitions and subpartitions.

Prerequisites - You must have the DROP ANY INDEX system privilege or the index must be in your own schema.

Note: The DROP INDEX statement modifies the schema.

Example: You specify a comma-separated list of index names with the appropriate table names after the DROP INDEX clause to remove multiple indexes from one or more tables at the same time, as illustrated:

DROP INDEX [IF EXISTS] 
    index_name1 ON table_name1,
    index_name2 ON table_name2,
    ...;

In this syntax:

• After the DROP INDEX clause, enter the name of the index you like to delete.
• Second, type the name of the table that the index relates to.
• An error will occur if you remove an index that does not exist. You can, however, use the IF EXISTS option to drop the index conditionally and prevent the problem.

Example 1: Dropping an Index:

This statement drops an index named ord_customer_ix_demo, which was created in "Compressing an Index: Example":

DROP INDEX ord_customer_ix_demo;

Example 2: SQL Server DROP INDEX statement examples

customers

The following picture shows the indexes of the sales.customers table:

A) Using SQL Server DROP INDEX to remove one index example

The ix_cust_email index is removed from the sales.customers table using the DROP INDEX statement:

DROP INDEX IF EXISTS ix_cust_email
ON sales.customers;

When looking at the indexes of the sales.customers table, you'll notice that the ix_cust_email index has been removed.

B)Using SQL Server DROP INDEX to remove multiple indexes example

The DROP INDEX command is used in the following example to remove the ix cust city and ix_cust_fullname indexes from the sales.customers table:

DROP INDEX 
    ix_cust_city ON sales.customers,
    ix_cust_fullname ON sales.customers;

The sales.customers table now has no non-clustered index.

Example 3: drop a non-primary key index, use the DROP INDEX command:

drop index products.products_category;

To drop a primary key index, use the ALTER TABLE .. DROP CONSTRAINT command :

alter table products
drop constraint pk_products;

Example 4: remove an index with no dependencies:

Suppose you create an index on the name and city columns of the users table:

CREATE INDEX ON users (name, city);

SHOW INDEXES FROM users;

table_name |     index_name      | non_unique | seq_in_index | column_name | direction | storing | implicit
-----------+---------------------+------------+--------------+-------------+-----------+---------+-----------
users      | primary             |   false    |            1 | city        | ASC       |  false  |  false
users      | primary             |   false    |            2 | id          | ASC       |  false  |  false
users      | users_name_city_idx |    true    |            1 | name        | ASC       |  false  |  false
users      | users_name_city_idx |    true    |            2 | city        | ASC       |  false  |  false
users      | users_name_city_idx |    true    |            3 | id          | ASC       |  false  |   true

You can drop this index with the DROP INDEX statement:

DROP INDEX users@users_name_city_idx;
SHOW INDEXES FROM users;

table_name | index_name | non_unique | seq_in_index | column_name | direction | storing | implicit
-----------+------------+------------+--------------+-------------+-----------+---------+-----------
users      | primary    |   false    |            1 | city        | ASC       |  false  |  false
users      | primary    |   false    |            2 | id          | ASC       |  false  |  false

We may also use a single DROP Table query to drop several tables simultaneously.

Syntax:

CREATE TABLE Table1 (
  Table1ID  int            NOT NULL PRIMARY KEY,
  col1      varchar(50)    NOT NULL,
)
CREATE TABLE Table2 (
  Table1ID  int            NOT NULL PRIMARY KEY,
  col1      varchar(50)    NOT NULL,
)
CREATE TABLE Table3 (
  Table1ID  int            NOT NULL PRIMARY KEY,
  col1      varchar(50)    NOT NULL,
)

DROP TABLE table1,table2,table3;

Example 1: To show dropping several tables with a single DROP statement, let's establish the following three tables.

USE TempDB
GO
CREATE TABLE testing1(ID INT, NAME VARCHAR(100));
CREATE TABLE testing2(ID INT, NAME VARCHAR(100));
CREATE TABLE testing3(ID INT, NAME VARCHAR(100));

You do not need to use three DROP statements to DROP all of these three tables; instead, use a single DROP statement and specify the three table names separated by commas as shown below.

DROP TABLE testing1,testing2,testing3;

Now All three tables testing1,testing2, and testing3 are dropped.

This easy trick can be used in your code if you create a lot of temporary tables and wish to delete them all in the end.

Example 2: 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);

We can now drop it using the following drop table statement, specifying all table names together.

DROP TABLE Sam, Amp, Rmp;

To remove all tables from a database, SQL does not provide a command or function. To remove all tables, we'll create an unknown(anonymous) code block and run it.

Deleting all of the database's tables will also remove all of the data from the tables, leaving you with an empty database.

To delete all of the tables in a database. We had to drop all tables several times during our assignment. Dropping all of the tables becomes laborious when there are a lot of them.

To delete all tables in a database, run DROP DATABASE, which will delete both the database and all tables.

Example 1:

1. MySQL Drop All Tables with SQL

Although the command "mysql drop all tables" does not exist, it is simple to create.

This process involves:

• To construct a set of Drop Table statements, select a list of tables from the data dictionary and combine it with some text.
• Turn off and on the foreign key verification.
• For all tables, create a list of drop table statements.

This query will return a list of all tables in your MySQL database:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'database_name';

Replace the word “database_name” with the name of your database.

Output:

SELECT CONCAT('DROP TABLE IF EXISTS `', table_name, '`;')
FROM information_schema.tables
WHERE table_schema = 'database_name';

Backticks are used to prevent table names containing special characters from causing problems with the results.

Output:

‘DROP TABLE IF EXISTS `customer`;’
‘DROP TABLE IF EXISTS `customer`;’
‘DROP TABLE IF EXISTS `orders`;’
‘DROP TABLE IF EXISTS `product`;’

2. Copy and Paste The Results Into a New Window

Using the Drop Table command, we now have a list of tables. These should be pasted into a new SQL editor window.

This is how your window should seem:

DROP TABLE IF EXISTS `customer`;
DROP TABLE IF EXISTS `employee`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `product`;

You might need to right-click on your results and choose "Copy Row (unquoted)" to avoid getting quotes around each line.

Disable and Enable Foreign Key Checks

If your database has foreign keys, dropping a table that is linked to another table using foreign keys may result in issues.

One solution is to alter your SQL queries such that the tables are dropped in the correct sequence.

But that takes too much time. You can avoid the mistake by disabling the foreign key verification when these statements are performed.

To disable the foreign key checks, place this line above all of your Drop Table statements:

SET FOREIGN_KEY_CHECKS = 0;

Then, add this line at the end of your script to enable them:

SET FOREIGN_KEY_CHECKS = 1;

Step 4: Run The Script

Your script should now look something like this:

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `customer`;
DROP TABLE IF EXISTS `employee`;
DROP TABLE IF EXISTS `orders`;
DROP TABLE IF EXISTS `product`;
SET FOREIGN_KEY_CHECKS = 1;

Run the script on your database, and all of the tables should be removed.

Example 2:

Step 1: Creation of database

create database Daljeet  
go  
use Daljeet  

First of all we create three tables in a database.

Step 2: Creation of the first table

create table emp(empId int, empName varchar(15))  
go  
insert into emp  
select 1,'Deepak'union all  
select 2,'Arora'  
go  
select * from emp

Step 3: Creation of the second table

create table stu(stuId int, stuName varchar(15))  
go  
insert into stu  
select 11,'Daljeet'union all  
select 22,'Singh'  
go  
select * from stu

Step 4: Creation of the third table

create table prod(stuId int, stuName varchar(15))  
go  
insert into prod  
select 101,'Mobile'union all  
select 202,'laptop'  
go  
select * from prod

It's a Stored Procedure that does a query against each table in the database. Sp_foreachtable is an undocumented Stored Procedure that does not appear in the MSDN publications. We use? instead of the table name when running a query using Sp_msforeachtable.

Step 5: Query to remove all the tables:

declare @command varchar(15)  
set @command = 'drop table ?'  
exec sp_msforeachtable @command

Example 3: We retrieve all table names for a schema from pg_tables and save the names in the RECORD type variable to complete the process. Following that, loop through these table names and run the drop table cmd for each one.

DO $$ DECLARE
  r RECORD;
BEGIN
  FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = current_schema()) LOOP
    EXECUTE 'DROP TABLE ' || quote_ident(r.tablename) || ' CASCADE';
  END LOOP;
END $$;

After running the above query, you will see that the public schema contains notables.

Example 4: If you wish to drop all tables in the database while keeping the database and any other non-table objects, you'll need to run DROP TABLE on each one separately. By searching the TABLES table in the information_schema database, you can create these DROP TABLE instructions. For example:

SELECT CONCAT('DROP TABLE IF EXISTS `', TABLE_SCHEMA, '`.`', TABLE_NAME, '`;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'mydb';

DROP TABLE Employees, Customers;

Table constraints can be used to limit the kind of data that can be entered. Because we can declare limitations on a table, we'll need a means to remove this one as well. The ALTER TABLE statement in SQL is used to accomplish this.

To minimize the effect on table processing, we can eliminate constraints.

The SQL syntax to remove a constraint from a table is,

ALTER TABLE "table_name"
DROP [CONSTRAINT|INDEX] "CONSTRAINT_NAME";

Syntax to delete foreign key constraint:

ALTER TABLE child_table_name
DROP CONSTRAINT foreignkey_name;

Syntax to delete primary key constraint:

ALTER TABLE child_table_name
DROP CONSTRAINT primarykey_name;

Syntax to delete composite key constraint:

ALTER TABLE table_name
DROP CONSTRAINT existing_compkey_name;

Syntax to delete unique key constraint:

ALTER TABLE table_name
DROP CONSTRAINT UK_name
UNIQUE (column_name);

Example 1:

1. Deleting existing Primary key.

Deleting existing pk_employee primary key on employee_details table. The query was as follows:

ALERT TABLE employee_details DROP CONSTRAINT pk_employee;

By executing above query, we can delete existing primary key on employee_details table.

2. Deleting Foreign key on existing table.

Deleting fk_employee foreign key on existing department_details table. The query was as follows:

ALTER TABLE department_details DROP CONSTRIANT fk_employee;

By executing above query, we can delete foreign key on existing department table.

3. Deleting existing composite key.

Deleting existing ck_employee composite key on employee_details table.

ALERT TABLE employee_details DROP CONSTRAINT ck_employee;

By executing above query, we can delete existing composite key on existing employee_details table.

4. Deleting existing unique key

Deleting existing uq_employee unique key on employee_details table.

ALERT TABLE employee_details DROP CONSTRAINT uq_employee;

By executing above query, we can delete existing unique key on existing employee_details table.

5. Deleting existing check constraint.

Deleting existing chk_dept_id on employee_details table

ALERT TABLE employee_details DROP CHECK chk_dept_id;

By executing above query, we can delete existing check constraint on existing employee_details table.

Example 2: Consider the following scenario. Assume we're starting with the Customer table from the CREATE TABLE section:

Table Customer

Column Name	Data Type
First_Name	char(50)
Last_Name	char(50)
Address		char(50)
City		char(50)
Country		char(25)
Birth_Date	datetime

Let's say we wish to remove the UNIQUE constraint from the "Address" column, and the constraint's name is "Con First." To do so, type the following into the box:

MySQL:

ALTER TABLE Customer DROP INDEX Con_First;

For index-type constraints like UNIQUE, MySQL utilizes the DROP INDEX command.

SQL Server:

ALTER TABLE Customer DROP CONSTRAINT Con_First;

When new rows are added to a table, you may use the default constraint to automatically specify default values for MySQL columns. However, you might need to drop the default value or delete the default constraint in MySQL on occasion.

We remove the Default value like any other Constraint because it is a Constraint. However, we must first use the query to determine the Constraint's name.

To remove the default value of a column in MySQL, we will use the ALTER TABLE statement with the DROP DEFAULT clause.

Syntax:

You can eliminate a constraint using the edit table drop Constraint syntax after you know its name.

Alter table <tableName> DROP Constraint <constrain-name>

Example :

Alter table Employee DROP Constraint DF__Employee__Depart__29572725

Identity columns are a useful feature of SQL Server that is frequently used. This function makes it easy to generate a unique value for each row in your database. It's simple to add a new column and make it an identity column, as well as to remove an existing identity column, but how do you change an existing column to make it an identity column or delete the identity property from an existing column?

There is no easy SQL code that can be used to remove the IDENTITY attribute from a table that has an IDENTITY primary key. It is not an easy task. In fact, there are several processes to be completed, and it's best to complete them all in one transaction to prevent leaving the database in an unstable state (without FK, or PKs).

Steps to be done:

• A new temporary column should be added.
• Modify the values in the new column with the same ones.
• Set the value of the new column to NOT NULL.
• DROP the Foreign Keys Restriction.
• DROP the primary key.
• DROP the IDENTITY column.
• Replace the old column's name with the new one's.
• Insert a new Primary Key.
• Make new limitations (without rechecking them).

Example 1:

A master table named [Person] exists in the AdventureWorks database. There are only 6 rows in [AddressType].

Because the entries can be inserted in a different sequence based on the server, all master tables in my databases can't contain IDENTITYs. So we're going to get rid of it.

I duplicated the tables' schema to compare the differences between the original table definition and the output after the method was discussed, and we can see that two things have changed:

There’s no IDENTITY.

The order of the column in the definition has changed.

-- The original table:
CREATE TABLE [Person].[AddressType](
 [AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
 [Name] [dbo].[Name] NOT NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL 
    CONSTRAINT [DF_AddressType_rowguid]  DEFAULT (newid()),
 [ModifiedDate] [datetime] NOT NULL 
    CONSTRAINT [DF_AddressType_ModifiedDate]  DEFAULT (getdate()),
 CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
 ([AddressTypeID] ASC)) ON [PRIMARY]

-- The result after removing the IDENTITY property:
CREATE TABLE [Person].[AddressType](
 [Name] [dbo].[Name] NOT NULL,
 [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL 
    CONSTRAINT [DF_AddressType_rowguid]  DEFAULT (newid()),
 [ModifiedDate] [datetime] NOT NULL 
    CONSTRAINT [DF_AddressType_ModifiedDate]  DEFAULT (getdate()),
 [AddressTypeID] [int] NOT NULL,
 CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
 ([AddressTypeID] ASC)) ON [PRIMARY]

In our example, it’s important to note that the Person.AdressType has references to two tables:

[Purchasing].[VendorAddress].[AddressTypeID] named [FK_VendorAddress_AddressType_AddressTypeID]
[Sales].[CustomerAddress].[AddressTypeID] named [FK_CustomerAddress_AddressType_AddressTypeID]

The script is, as explained before, inside a transactions and the looks like this:

SET NOCOUNT ON;

BEGIN TRANSACTION
BEGIN TRY
    -- Adding a new temporary column
    ALTER TABLE Person.AddressType
        ADD [new_AddressTypeID] INT NULL;

    -- Updating the new column with the values sorted as we want
    EXEC sp_executesql N'UPDATE Person.AddressType SET [new_AddressTypeID] = AddressTypeID'

    -- Setting the new column as NOT NULL
    ALTER TABLE Person.AddressType
        ALTER COLUMN [new_AddressTypeID] INT NOT NULL;

    -- Disable Foreign Keys Constraints
    ALTER TABLE [Purchasing].[VendorAddress]
        DROP CONSTRAINT [FK_VendorAddress_AddressType_AddressTypeID]
    ALTER TABLE [Sales].[CustomerAddress]
        DROP CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]

    -- Drop Primary Key
    ALTER TABLE Person.AddressType
    DROP CONSTRAINT [PK_AddressType_AddressTypeID]

    -- Drop IDENTITY column
    ALTER TABLE Person.AddressType
    DROP COLUMN [AddressTypeID]

    -- Rename column ID_EXENCION_NEW --> ID_EXENCION
    EXEC sp_rename 'Person.AddressType.new_AddressTypeID', 'AddressTypeID', 'COLUMN';

    -- Add new Primary Key
    ALTER TABLE Person.AddressType
        ADD CONSTRAINT [PK_AddressType_AddressTypeID] PRIMARY KEY CLUSTERED
        (
        [AddressTypeID] ASC
        )

    -- Enable constraints (without rechecking them)
    ALTER TABLE [Purchasing].[VendorAddress] WITH NOCHECK
         ADD CONSTRAINT [FK_VendorAddress_AddressType_AddressTypeID]
         FOREIGN KEY([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID])
    ALTER TABLE [Sales].[CustomerAddress] WITH NOCHECK
        ADD CONSTRAINT [FK_CustomerAddress_AddressType_AddressTypeID]
        FOREIGN KEY([AddressTypeID]) REFERENCES [Person].[AddressType] ([AddressTypeID])

    PRINT 'IDENTITY removed successfully'
END TRY

BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;
PRINT 'ERROR:' + ERROR_MESSAGE()
END CATCH;
IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;

The UPDATE of the new column must be done with dynamic SQL because we're inserting the column in the same script and SQL Server tries to evaluate the script before running and fails to discover the column, resulting in an error.

Example 2:

• To an existing Table, add a new column called TestID.
• Change the Id (Identity enable Column) to TestID (Newly Added) Column in the records.
• Table's Id (Identity Enable Column) should be removed.
• The Recently Added Column (TestID) should be renamed to Id.

--Create Table with Identity Property
CREATE TABLE dbo.Employee ( Id INT IDENTITY(1,1), Name VARCHAR(10))
GO

--Insert the record after creating Table with Identity Property on Id Column
INSERT INTO dbo.Employee 
VALUES('Shahzad')
GO

--Run to See the Data
SELECT * FROM dbo.Employee

--Find out all the columns for all the tables on which Identity Property is enabled
SELECT OBJECT_NAME(OBJECT_ID) AS TableName,name AS ColumnName FROM sys.columns
WHERE is_identity=1

/** Drop Identity ********/
--Add a new column with any name
ALTER TABLE dbo.Employee
ADD TestId INT

--Update the Records in newly Added column , in our case TestID
UPDATE dbo.Employee
SET TestId=Id

--Drop Identity Column
ALTER TABLE dbo.Employee
DROP COLUMN Id

--Rename the newly added column to the original Identity Column.
EXEC sp_rename 'dbo.Employee.TestId','Id','COLUMN'  

Example 3: Here is a simple table that has two columns and one column is the identity column.

CREATE TABLE [dbo].[Test1]( 
   [id] [int] IDENTITY(1,1) NOT NULL, 
   [name] [nchar](10) NULL
)

When we remove the identity value from column "id" using SQL Server Management Studio, a new temporary table is generated, the data is moved to the temporary table, the old table is dropped, and the new table is renamed. The script below demonstrates this.

To get this script, make the change in SQL Server Management Studio, then right-click on the designer and select "Generate Change Script."

Before running this script outside of the database designer, you should go over it carefully to avoid any data loss issues.

BEGIN TRANSACTION 
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET NUMERIC_ROUNDABORT OFF 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
COMMIT 
BEGIN TRANSACTION 
GO 

CREATE TABLE dbo.Tmp_Test1 
   ( 
   id INT NOT NULL, 
   name NCHAR(10) NULL 
   )  ON [PRIMARY] 
GO 
IF EXISTS(SELECT * FROM dbo.Test1) 
    EXEC('INSERT INTO dbo.Tmp_Test1 (id, name) 
      SELECT id, name FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)') 
GO 

DROP TABLE dbo.Test1 
GO 

EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT'  
GO 
COMMIT 

Example 4: We can see that if we make this instance a little more involved by using a primary key and adding a second table with a foreign key constraint referencing the first table, we'll need to do even more work.

CREATE TABLE [dbo].[Test1]( 
   [id] [int] IDENTITY(1,1) NOT NULL, 
   [name] [nchar](10) NULL, 
 CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED  
( 
   [id] ASC 
)) 
GO 

CREATE TABLE [dbo].[Test2]( 
   [id] [int] NULL, 
   [name2] [nchar](10) NULL 
) ON [PRIMARY] 
GO 

ALTER TABLE [dbo].[Test2] WITH CHECK ADD CONSTRAINT [FK_Test2_Test1] FOREIGN KEY([id]) 
REFERENCES [dbo].[Test1] ([id]) 
GO 

ALTER TABLE [dbo].[Test2] CHECK CONSTRAINT [FK_Test2_Test1] 
GO

We can see that extra steps are required if we do the same operation and use SQL Server Management Studio to remove the identity value from column "id" in table "test1" and script out the modification.

• First a temp table "Tmp_Test1" is created with the correct column attributes
• The data is moved to "Tmp_Test1" from "Test1"
• The FK constraint on "Test2" is dropped
• Table "Test1" is dropped
• Table "Tmp_Test1" is renamed to "Test1"
• The primary key is created on table "Test1"
• And lastly the FK constraint is recreated on table "Test2". That's a lot of steps.

Before running this script outside of the database designer, you should go over it carefully to avoid any data loss issues.

BEGIN TRANSACTION 
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET NUMERIC_ROUNDABORT OFF 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
COMMIT 
BEGIN TRANSACTION 
GO 

CREATE TABLE dbo.Tmp_Test1 
   ( 
   id INT NOT NULL, 
   name NCHAR(10) NULL 
   )  ON [PRIMARY] 
GO 

IF EXISTS(SELECT * FROM dbo.Test1) 
    EXEC('INSERT INTO dbo.Tmp_Test1 (id, name) 
      SELECT id, name FROM dbo.Test1 WITH (HOLDLOCK TABLOCKX)') 
GO 

ALTER TABLE dbo.Test2 
   DROP CONSTRAINT FK_Test2_Test1 
GO 

DROP TABLE dbo.Test1 
GO 

EXECUTE sp_rename N'dbo.Tmp_Test1', N'Test1', 'OBJECT'  
GO 

ALTER TABLE dbo.Test1 ADD CONSTRAINT 
   PK_Test1 PRIMARY KEY CLUSTERED  
   ( 
   id 
   ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] 

GO 
COMMIT 

BEGIN TRANSACTION 
GO 

ALTER TABLE dbo.Test2 ADD CONSTRAINT 
   FK_Test2_Test1 FOREIGN KEY 
   ( 
   id 
   ) REFERENCES dbo.Test1 
   ( 
   id 
   ) ON UPDATE  NO ACTION  
    ON DELETE  NO ACTION  
GO 

COMMIT 

The same is true if we want to make one of our current columns an identification column. This is a less likely case, yet it may be necessary.

Other Approaches

Another option is to create a new column with the identity property and move the data from the old column to the new column. After that, you may delete the old column and use the sp_rename stored procedure to rename it. If these columns include indexes, foreign keys, or other constraints, you'll still need to drop them before making the changes, so this method isn't significantly faster.

As you can see, there is no simple solution to this problem. There are a few different methods for changing values in the system tables that you can find on the internet. These methods work, but if you make a mistake, your data could be completely ruined, so be sure you know what you're doing before editing system tables.

To delete one or more columns from a table, use PostgreSQL's DROP COLUMN clause in the ALTER TABLE statement.

By simply dividing a list of DROP COLUMN statements with a comma, several columns can be discarded in a single query.

DROP keyword and column name list are separated with a comma in a single ALTER statement to remove them from the MySQL table.

Syntax:

ALTER TABLE table_name
DROP COLUMN column_name1,
[DROP COLUMN column_name2];

In this syntax

• The name of the table containing the columns you're deleting is table_name.
• The columns that you are dropping are column_name1 and column_name2.

Example 1: SQL DROP COLUMN examples

The following statement creates a new table named persons for the demonstration:

CREATE TABLE persons (
    person_id INT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    phone VARCHAR(25),
    email VARCHAR(255)
);

The following statement drops the date_of_birth and phone columns:

ALTER TABLE persons
DROP COLUMN date_of_birth,
DROP COLUMN phone;

Example 2: Let's begin by gathering the sample data. We'll make a table called sale details and then insert rows into it. Finally, we'll use a single_query to delete three columns from the sale_details table.

Step 1: #create the table sale_details

CREATE TABLE sale_details (
    id INT ,
    sale_person_id VARCHAR(255) ,
    sale_person_name VARCHAR(255),
    no_products_sold INT,
    sales_department VARCHAR(255)
);

Step 2: #insert data into table sale_details

INSERT INTO sale_details (id,sale_person_id,sale_person_name,no_products_sold,sales_department) 
 VALUES(1,"sd1","Henry",2000,"Kitchen Essentials"),
 (2,"sd1","Henry",5000,"Apparels"),
 (3,"sd1","Henry",40,"Medicines"),
 (4,"sd2","Richa",3000,"Kitchen Essentials"),
 (5,"sd2","Richa",500,"Apparels"),
 (6,"sd2","Richa",50,"Medicines"),
 (7,"sd3","Ved",100,"Kitchen Essentials"),
 (8,"sd3","Ved",150,"Apparels"),
 (9,"sd3","Ved",1000,"Medicines");

Step 3: To view the snapshot of the table sale_details, we will execute:

SELECT * FROM sale_details;

Step 4: From table sale_details, we will now delete the columns sale_person_id, no products_sold, and sales department. For the answer, look at the following query.

ALTER TABLE sale_details DROP sale_person_name,DROP no_products_sold, DROP sales_department;

Step 5: To verify if the query dropped the columns along with data from table sale_details execute:

SELECT * FROM sale_details;

The output shows that multiple columns got deleted along with data.

Example 3: To drop both the "foo" and "bar" columns from the "test" table do this:

ALTER TABLE test
DROP COLUMN foo,
DROP COLUMN bar;

The list can include as many more columns as needed. Depending on the magnitude of the data in the table, this could take anything from a few milliseconds to many minutes.

Example 4: PostgreSQL DROP COLUMN examples

For the demonstration, three tables will be created: books, categories, and publishers.

Each book in this case has just one publisher, and each publisher can publish several books. Each book is given to a category, which can contain a large number of books.

The following statements create the three tables:

CREATE TABLE publishers (
    publisher_id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE categories (
    category_id serial PRIMARY KEY,
    name VARCHAR NOT NULL
);

CREATE TABLE books (
    book_id serial PRIMARY KEY,
    title VARCHAR NOT NULL,
    isbn VARCHAR NOT NULL,
    published_date DATE NOT NULL,
    description VARCHAR,
    category_id INT NOT NULL,
    publisher_id INT NOT NULL,
    FOREIGN KEY (publisher_id) 
       REFERENCES publishers (publisher_id),
    FOREIGN KEY (category_id) 
       REFERENCES categories (category_id)
);

In addition, we create a view based on the books and publishers tables as follows:

CREATE VIEW book_info 
AS SELECT
    book_id,
    title,
    isbn,
    published_date,
    name
FROM
    books b
INNER JOIN publishers 
    USING(publisher_id)
ORDER BY title;

To remove the category_id column from the books table, use the ALTER TABLE DROP COLUMN statement as shown below:

ALTER TABLE books 
DROP COLUMN category_id;

Let’s show the structure of the books table:

test=# \d books;

Table "public.books"

     Column     |       Type        |                        Modifiers
----------------+-------------------+---------------------------------------------------------
 book_id        | integer           | not null default nextval('books_book_id_seq'::regclass)
 title          | character varying | not null
 isbn           | character varying | not null
 published_date | date              | not null
 description    | character varying |
 publisher_id   | integer           | not null

Indexes:

"books_pkey" PRIMARY KEY, btree (book_id)

Foreign-key constraints:

"books_publisher_id_fkey" FOREIGN KEY (publisher_id) REFERENCES publishers(publisher_id)

The statement eliminated not just the category_id column, but also the category_id column's foreign key constraint, as you can see from the output.

The following statement tries to remove the publisher_id column from the database:

ALTER TABLE books 
DROP COLUMN publisher_id;

PostgreSQL issued the following error:

The column publisher_id of the books table is used by the book_info view, according to the documentation. To remove both the publisher_id column and the book_info view, use the CASCADE option as demonstrated in the following statement:

ALTER TABLE books 
DROP COLUMN publisher_id CASCADE;

The statement issued the following notice, which is what we expected.

NOTICE: drop cascades to view book_info

Add several DROP COLUMN clauses like follows to eliminate both the isbn and the descriptive columns with a single statement:

ALTER TABLE books 
  DROP COLUMN isbn,
  DROP COLUMN description;

The DROP DATABASE statement can drop several databases. It demonstrates how commas may be used to divide several databases.

Example 1: First, create 4 databases:

CREATE DATABASE DB1;
CREATE DATABASE DB2;
CREATE DATABASE DB3;
CREATE DATABASE DB5;

Now drop them:

DROP DATABASE DB1, DB2, DB3, DB5;

But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).

DROP DATABASE DB1, DB2, DB3, DB4, DB5;

The above statement will fail with an error stating that it cannot drop DB4 because it does not exist, but the other four databases will be dropped without issue. If a user is connected to one of the databases, the user will remain attached to that database, but all others will be dropped.

Example 2: Create two databases Sales & Purchase as shown below :

create database Sales
create database Purchase

Drop the both database :

Drop Database Sales, Purchase

DROP IF EXIST If an item already exists, it is dropped and a new one is created; otherwise, the SQL code is continued.

Before attempting to drop the table, make sure it exists. You drop the table if it exists; if it does not, you can omit the DROP TABLE command.

DROP statement that can be used as DROP IF EXISTS, with IF EXISTS as an optional parameter.

When you need to verify if an object exists before dropping and recreating it, the DROP IF EXISTS statement can be handy since it eliminates the need to write SQL code that uses the system catalogue view or any Metadata function to check if an object exists before dropping and recreating it.

SYNTAX :

DROP OBJECT_TYPE [ IF EXISTS ] OBJECT_NAME

• WHERE: OBJECT_TYPE like Table, Procedure, View, Function, Database, Trigger, Assembly, Sequence, Index etc.
• IF EXISTS is provided, it checks if an object exists, then delete the existing object and recreate it, else it continues to execute SQL.
• OBJECT_NAME is a name of an object.

Example 1: If you want to check whether or not a table exists with a single T-SQL command, you can use DROP TABLE IF EXISTS, which will do both for you in one line.

-- use database
USE [MyDatabase];
GO

-- attempt to run DROP TABLE only if it exists 
DROP TABLE IF EXISTS [dbo].[MyTable0];
GO

Example 2:

1. If the table exists, we used the statement below in prior versions of SQL Server.

IF EXISTS(SELECT 1 FROM sys.Tables 
          WHERE  Name = N'Customers' AND Type = N'U')
BEGIN
  DROP TABLE dbo.Customers
END

To examine what options were available in prior versions of SQL Server to verify for the existence of a Table, type check if a Table exists.

DROP DataBase IF EXISTS

[ALSO READ] How to check if a Database exists see below.

2. In Sql Server 2016 we can write a statement like below to drop a DataBase if exists.

USE MASTER
GO
DROP DATABASE IF EXISTS SqlHintsDemoDB

Example 3: Using DROP IF EXISTS On Stored Procedure

Before releasing the IF EXISTS clause with the DROP statement, the following SQL code shows how it checks to see if any stored procedures exist, and if they do, drop and reconstruct them.

Let's make a stored procedure that retrieves data from the SampleTable table based on the value of the @Id argument.

IF OBJECT_ID('Usp_GetData', 'P') IS NOT NULL 
DROP PROCEDURE Usp_GetData;
GO

CREATE PROCEDURE Usp_GetData 
@ID AS INT
AS BEGIN 
SELECT * FROM dbo.SampleTable 
WHERE Id =@Id;
END
GO

Example 4: Creating & Dropping View using DROP VIEW IF EXISTS

The first statement in the following example examines whether the tempdb database contains a view named Vw Test. If it exists, it will attempt to DROP the view.

DROP VIEW IF EXISTS does not throw an error if the Vw_Test view does not exist.

A Vw_Test view will be created by the second statement.

Use tempdb
Go

DROP VIEW IF EXISTS dbo.Vw_Test;
GO

CREATE VIEW dbo.Vw_Test
AS
    SELECT 1 as Col;
GO

In SQL Server 2016 and later, we can use DROP IF EXISTS on the following database objects.

DROP IF EXISTS ASSEMBLY <Assembly_Name>;
DROP IF EXISTS ROLE <Role_Name>;
DROP IF EXISTS TRIGGER <Trigger_Name>;
DROP IF EXISTS TYPE <Type_Name>;
DROP IF EXISTS DATABASE <Data_Name>;
DROP IF EXISTS SCHEMA <Schema_Name>;
DROP IF EXISTS USER <User_Name>;
DROP IF EXISTS DEFAULT <Default_Name>;
DROP IF EXISTS SECURITY POLICY <Policy_Name>;
DROP IF EXISTS VIEW <View_Name>;
DROP IF EXISTS FUNCTION <Function_Name>;
DROP IF EXISTS SEQUENCE <Sequence_Name>;
DROP IF EXISTS INDEX <Index_Name>;
DROP IF EXISTS SYNONYM <Synonym_Name>;

Example 5: DROP Function IF EXISTS

To drop a function if it exists in SQL Server 2016 and higher:

DROP FUNCTION IF EXISTS fnSayHello
GO

To drop a function if in SQL Server 2014 and older:

IF  EXISTS (SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'fnSayHello') 
          AND type = N'FN')
    DROP FUNCTION fnSayHello
GO

Example 5: Using DROP IF EXISTS on Database Objects

IF OBJECT_ID('dbo.Test', 'U') IS NOT NULL
DROP TABLE dbo.Test;
GO

CREATE TABLE dbo.Test
(
    Id INT
);
GO
 
IF OBJECT_ID('dbo.Insert_Test', 'P') IS NOT NULL
DROP PROCEDURE dbo.Insert_Test;
GO

CREATE PROCEDURE dbo.Insert_Test
AS
BEGIN
    SELECT 1;
END
GO

In SQL Server, temporary tables are used to store data for a set period of time. Temporary tables have many similarities to persistent tables. For example, just with persistent tables, we may build indexes, statistics, and constraints for these tables.

Types of the Temporary Tables

• Local Temporary Tables: This sort of temporary table has a name that begins with a single "#" hashtag symbol and is only accessible during the session that was generated. When we end the local temporary table, SQL will drop the temporary table.
• Global Temporary Tables: This type of temporary table has a name that begins with a double "##" hashtag sign and can be reached from any connection.

Syntax:

The syntax to create a temporary table is as follows:

DROP TABLE #tblname

CREATE TABLE #tblname | ##tblname
  ( 
     column_1 [DATA TYPE], 
     column_2 [DATA TYPE], 
     column_3 [DATA TYPE], 
     column_4 [DATA TYPE] 
  ) 

Example 1: The following query will verify for the #LocalCustomer table in the tempdb database and drop it if it exists.

1. For the local temporary tables:

IF OBJECT_ID(N'tempdb..#LocalCustomer') IS NOT NULL
BEGIN
DROP TABLE #LocalCustomer
END
GO
 
CREATE TABLE #LocalCustomer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

2. For the global temporary tables:

IF OBJECT_ID(N'tempdb..##GlobalCustomer') IS NOT NULL
BEGIN
DROP TABLE ##GlobalCustomer
END
GO
 
CREATE TABLE ##GlobalCustomer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

3. Using sys.tables table to check temporary table existence

We'll use sys.tables to check for the presence of the temporary table in this method because this table gives user tables in the relevant database.

For the local temporary tables:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#LocalCustomer%') 
BEGIN
   DROP TABLE #LocalCustomer;
END;
 
CREATE TABLE #LocalCustomer
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

For the global temporary tables:

IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '##GlobalCustomer%') 
BEGIN
   DROP TABLE ##GlobalCustomer ;
END;
 
CREATE TABLE ##GlobalCustomer 
(
 CustomerId int,
 CustomerName varchar(50), 
 CustomerAdress varchar(150)
)

As can be seen, we check for the #LocalCustomer table in the tempdb database, and if it exists, we must drop it. We need to emphasise one aspect at this time: the table name is inspected using the LIKE operator, and the wildcard character has been put to the end of the temp table name. As previously indicated, local temp tables are generated with a random suffix, thus we don't know their exact names.

Example 2: The phrase TEMP or TEMPORARY must be specified in the CREATE TABLE statement when creating a temporary table. Let's say you'd like to make a temporary table called tblCountries. The syntax is as follows:

CREATE TEMPORARY TABLE tblCountries
(
	Country_id int,
	COuntry_Code varchar(500),
	Country_Name varchar(1000)	
)

The temporary table is dropped using the DROP TABLE query. The TEMPORARY keyword is not required in the DROP TABLE statement, unlike it is in the CREATE TEMPORARY TABLE statement. Let's remove the tblCountries table from the query:

DROP TABLE tblCountries

DROP VIEW is a command that allows you to remove one or more views. You must be the owner of the view to run this command.

For each view, you'll need the DROP permission.

If any of the views mentioned in the parameter list do not exist, the statement fails with a message that specifies which non-existing views it was unable to drop by name, and no modifications are done.

Compatibility

Except for the IF EXISTS option, which is a PostgreSQL modification, this command follows the SQL standard, with the exception that the standard only permits one view to be discarded per command.

Syntax:

1. To remove views.

DROP VIEW -- remove a view

Synopsis

DROP VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

Otherwise

The DROP VIEW statement is used to remove a view from a database as follows:

DROP VIEW [IF EXISTS] schema_name.view_name;

Parameters :

• IF EXISTS: If the view does not exist, do not throw an error. In this instance, a notice is issued.
• name: The name of the view to be removed (optionally schema-qualified).
• CASCADE: Drop things that are dependent on the view automatically (such as other views).
• RESTRICT: If any objects depend on the view, don't let it go. This is the standard.

2. To remove multiple views, you use the following syntax:

DROP VIEW [IF EXISTS] 
    schema_name.view_name1, 
    schema_name.view_name2,
    ...;

Example 1: Removing one view example

The following example shows how to drop the sales.daily_sales view from the sample database:

DROP VIEW IF EXISTS sales.daily_sales;

Removing multiple views example

The following statement creates a view named product_catalogs for demonstration purpose:

CREATE VIEW sales.product_catalog
AS
SELECT 
    product_name, 
    category_name, 
	brand_name,
    list_price
FROM 
    production.products p
INNER JOIN production.categories c 
    ON c.category_id = p.category_id
INNER JOIN production.brands b
	ON b.brand_id = p.brand_id;

The following statement removes both sales.staff_sales and sales.product_catalog views at the same time:

DROP VIEW IF EXISTS 
    sales.staff_sales, 
    sales.product_catalogs;

Example 2: This command will remove the view called kinds:

DROP VIEW kinds;