Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [drop-table]

This tag is for the SQL command DROP TABLE

55 questions
59
votes
4 answers

How to check foreign keys related to a table

How to see foreign keys related to a table in MySql? Background : I wanted to drop a table in MySql which has a foreign key constraint. When I do it I get this: Error Code: 1217. Cannot delete or update a parent row: a foreign key constraint…
Mr.X
  • 911
  • 1
  • 13
  • 19
20
votes
7 answers

How to drop multiple tables with common prefix in one query?

I'm using Microsoft SQL Server 2008.My question is:How to drop multiple tables with common prefix in one query? something like that table names: LG_001_01_STLINE, LG_001_02_STFICHE
Cell-o
  • 1,106
  • 8
  • 21
  • 40
19
votes
1 answer

Drop table taking too long

I have a table with approx. 200 million rows (approx. 0.5 TB) and I want to drop it, but it is taking a really long time. It's been running for 2 days now. I suspect the rollback function to be the reason for this. Is there a way to avoid the…
Pelle G
  • 193
  • 1
  • 1
  • 6
11
votes
2 answers

DROP TABLE ... CASCADE does not drop any dependent table

I am still relatively new to postgres and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My expectation was that any table that had a…
smac89
  • 215
  • 1
  • 3
  • 8
8
votes
4 answers

Difference between DROP and TRUNCATE

What does the TRUNCATE do differently to drop? I believe it deletes all the data in the table but keeps the table name in the database, where as DROP deletes all data and the table. Is this correct ?
user1829823
  • 189
  • 1
  • 1
  • 2
8
votes
4 answers

Shrink database after drop table?

I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I could shrink the db to a minimum. Is that the way…
Ezi
  • 652
  • 1
  • 12
  • 23
7
votes
2 answers

How to undo DROP TABLE statement?

I’ve accidentally executed DROP TABLE statement on a wrong table. I have backups that are several days old that I can use to recover table and most of the data but not all. I know that SQL Server can’t rollback already committed transaction but I…
Igor Voplov
7
votes
4 answers

SQL Server - Alter table vs drop and create

Our database is SQL Server 2008 R2. We have some tables that have some varchar(500) columns that I want to switch to datetime2 or bigint. I can guarantee all the data in the columns to be switched are valid for the proper type. The column changes do…
scottr
  • 83
  • 1
  • 1
  • 6
5
votes
3 answers

Drop Tables with Bigger size

We have a mysql database with a size of 2 TB, in which we have for to five big tables ranging from 110GB to 350GB. When we try to drop these tables the mysql goes to the defunct mode and hangs in that state for a long time. Is there a solution to…
Prashanth Ganesh
  • 51
  • 1
  • 2
5
votes
2 answers

MySQL RDS: Drop formerly huge table without freezing DB?

I am using MySQL 5.6 in AWS RDS. I want to drop a table that used to have 8 billion rows. Hearing that dropping a large table can freeze the database, I instead chose to delete the rows using a loop. This took a couple of days, but the table is now…
user984003
  • 161
  • 2
  • 7
5
votes
3 answers

Speed difference between Drop table and Truncate table in Postgres

I currently have a program that inserts into a database by creating temp tables, filling the tables, then merging that data into the main tables. Then dropping the tables and doing it all again. I'm wondering what the speed difference is if instead…
Francis Lewis
  • 150
  • 1
  • 1
  • 3
5
votes
3 answers

How can I drop a Memory Optimized Table?

Running SQL Server Standard 2016 SP2-CU1. I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them? Running: DROP TABLE [REF].[Work_xxyyzz] Gives: Msg 12332, Level 16, State 111,…
Rocko
  • 91
  • 1
  • 8
5
votes
2 answers

Postgresql - how to securely delete the storage freed when a table is dropped?

For a system with sensitive information stored in a PostgreSQL 9.5 database, in which data stored in a table that is deleted must be securely deleted (like shred does to files), and where the system is persistent even though a particular data set…
Jonathan Morgan
  • 51
  • 1
  • 2
5
votes
1 answer

Check table before delete a constraint

In SQL Server I used the following code to drop a table with a constraint: IF OBJECT_ID('EMPLOYEES') IS NOT NULL BEGIN ALTER TABLE EMPLOYEES DROP CONSTRAINT EMP_DEPT_FK DROP TABLE EMPLOYEES; END How can I accomplish the same…
andrea
  • 341
  • 1
  • 6
  • 14
5
votes
1 answer

What is preventing DROP TABLE from removing a Postgres table?

I have a postgres database named aarons_db in ubuntu linux. I'm trying to delete a table called pnt within aarons_db. To do this, I am using the following command: DROP TABLE pnt Unfortunately, this does not appear to drop the table. My best guess…
Borealis
  • 151
  • 1
  • 1
  • 4
1
2 3 4