Skip to main content

Questions tagged [drop-table]

This tag is for the SQL command DROP TABLE

Filter by
Sorted by
Tagged with
0 votes
1 answer
19 views

How can I replicate transactions one by one in mysql?

I have one source server and one replica server. And I set source_delay 30mins, replication is gtid based, mysql version is 8.0.24 If I accidentally drop table A, Can I apply transactions just right ...
제임스으's user avatar
1 vote
2 answers
76 views

Can a dropped table be recovered from Always On availability group?

Hi I have SQL 2016 with AlwaysOn Availability. I dropped a table on the "active" node. Can I recover the table from the other node or the drop is "synched" automatically and thus ...
user547's user avatar
  • 353
0 votes
1 answer
90 views

Postgres drop table (which has no rows) has taken > 20 minutes and isn't finished yet

Before executing the drop table MY_TABLE, I checked count(*) from MY_TABLE and verified that the row count is 0. There exist no foreign key constraints on other tables which reference MY_TABLE. Yet ...
Ben R.'s user avatar
  • 101
0 votes
0 answers
101 views

How to replace (drop and rename) a table without triggering constraints in postgres

I have a table (A) that I need to reprocess from time to time. Doing that using update or upsert is really slow, so, to make things faster, I decided to create a new table (B), copy all the rows from ...
Sassa's user avatar
  • 135
1 vote
1 answer
397 views

How to drop a table referincing a very trafficated table easily?

Let's say I have this two tables CREATE TABLE users ( id_user serial primary key, name text NOT NULL ); CREATE TABLE useless_table ( id serial primary key, id_user int4 NOT NULL, ...
Sotis's user avatar
  • 306
1 vote
0 answers
687 views

MariaDB: Terminate long-running clearnup process?

This is a follow up to my previous question about a long-running INSERT SELECT query that may have exceeded the max_statement_time and was Killed. It has been in the Reset for next command state for 3....
Chris Cox's user avatar
1 vote
1 answer
694 views

Need a PL/SQL code to drop many partition in 1 single SQL statements

I have a requirement where we need to drop historical partitions which are not dropped . Requirement is dropping multiple partitions in one step instead of dropping one by one. like ALTER TABLE sales ...
Dhiren Singh's user avatar
0 votes
1 answer
82 views

MySQL: Dropping table with specific table prefix from the Database using Terminal

I am trying to delete database tables from PhpMyAdmin using a Terminal (Guake Terminal) with a specific table prefix. I am trying to delete tables with the prefix wordpress2_ at the beginning of it. ...
SkyCross12's user avatar
-2 votes
1 answer
15k views

What is the correct way to check and delete temp table?

Approach 1: IF OBJECT_ID('tempdb..#MyTempTbl') IS NOT NULL DROP TABLE #MyTempTbl; Approach 2: IF EXISTS (SELECT * FROM [tempdb].[sys].[objects] WHERE [name] = N'#MyTempTbl') DROP TABLE [#...
variable's user avatar
  • 3,489
1 vote
1 answer
5k views

Dropping PostgreSQL partition is blocked as long as a client is reading data from any partition

I have a simple partitioned table in PostgreSQL 14, like this: create table entity ( dataset_id integer not null references dataset (id), ... ) partition by list (dataset_id); create table ...
EM0's user avatar
  • 210
1 vote
1 answer
1k views

Postgres Materialized Views keeps getting dropped every time underlying views is updated

I am using Postgres as my DB and I have a bunch of Materialized Views that are created from a view. The MVs are in Schema B and Views are in Schema A. They are created like create SchemaB.mv1 as ...
user16573033's user avatar
4 votes
2 answers
3k views

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. ...
user984003's user avatar
-3 votes
1 answer
402 views

Permanently Delete the sql server database table

I wanted the remove the table from the sql server database. The table has 500 million records. I don't need the table anymore. What's the best approach? Can I use drop table command to delete the ...
anu's user avatar
  • 57
1 vote
2 answers
1k views

Massive DROP on a production database with the shortest downtime

I was redesigning legacy database and have merged lots of tables into just four and now I'm looking for a way to drop a several thousand obsolete tables as quickly as it possible. As far as I know we ...
danilabagroff's user avatar
3 votes
1 answer
2k views

Can't do simple query or drop table due to out of memory error, but pg_locks return no lock

I received this out of memory error when trying to drop a large table (70GB including index) but couldn't identify the root cause from pg_locks view DROP TABLE big_table; ERROR: out of shared memory ...
Hannah's user avatar
  • 31
1 vote
1 answer
343 views

Allow user to drop only tables / views created by that user in MySQL

Is it possible to allow a user to only drop tables and views that he/she created in MySQL? How is this achieved?
Giovanni Colitti's user avatar
1 vote
1 answer
1k views

Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?

If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?
J.D.'s user avatar
  • 39.5k
1 vote
1 answer
806 views

mysql workbench - Couldn't find option to remove DROP TABLE IF EXIST

Recently I've been looking into export options to find an option to remove: DROP TABLE IF EXISTS and replace it with CREATE TABLE IF NOT EXISTS. I've seen on other MySQL clients like HeidiSQL or ...
Abdulaziz Hamdan's user avatar
0 votes
1 answer
450 views

Oracle Global Temp table - data is truncated but the table remains in the DB ! is this an expected behavior or a configuration?

We are trying to create global temp table using Create global Temporary table t1 ( id NUMBER, description VARCHAR2(20) ) ON COMMIT Delete Rows; and trying to drop it at the end manually but ...
Guru vyas V's user avatar
1 vote
1 answer
723 views

How to avoid DROP TABLE for a specific MySQL table

I have used a Wordpress plugin that for some unknown reason is always deleting a database table (cerber_files). The plugin's author is not giving support for this issue. I think in order to solve ...
Rogério Dec's user avatar
0 votes
1 answer
809 views

Dropping a table/column automatically drops views using it - how to warn instead?

If I have a view that uses a column, and I remove that column/table, the view is dropped automatically. Is there a setting I can change such that it instead prevents the removal of the column/table, ...
Sarov's user avatar
  • 281
1 vote
0 answers
926 views

Azure SQL DB: Can't create table because it already exists, can't drop or write to table because it doesn't exist

I have an Azure SQL DB database that I am copying data into from a source database using SSIS. My SSIS package for one table fails because it can't write to or create a specific table name (it is ...
Mel's user avatar
  • 207
5 votes
3 answers
12k views

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 ...
Francis Lewis's user avatar
2 votes
0 answers
274 views

Is it possible to drop inheritance from the parent table (top down)?

Our system uses inheritance on two levels: master child_level_1 child_level_2 child_level_2 child_level_2 child_level_1 child_level_2 child_level_2 child_level_1 child_level_2 The idea was to ...
Jesse's user avatar
  • 121
5 votes
3 answers
9k views

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].[...
Rocko's user avatar
  • 91
19 votes
1 answer
42k views

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 ...
Pelle G's user avatar
  • 193
3 votes
3 answers
8k views

Can a TRUNCATE or DROP table ever be very time consuming?

A couple of months ago, I discovered in a (not so well maintained) database a totally useless table containing 430 million unindexed rows, growing by about 80 000 rows per day. It was a top candidate ...
Jérôme Verstrynge's user avatar
1 vote
2 answers
7k views

How do I CASCADE DROP a table being referenced in MariaDB?

If I have a table that references another one CREATE TABLE foo ( x int PRIMARY KEY ); CREATE TABLE bar ( x int, CONSTRAINT asdf FOREIGN KEY (x) REFERENCES foo(x) ); DROP TABLE foo; ERROR 1451 (...
Evan Carroll's user avatar
  • 64.7k
5 votes
2 answers
3k views

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 ...
Jonathan Morgan's user avatar
1 vote
1 answer
396 views

PostgreSQL drop view in case of unknown whether materialized or not

I'm trying to drop a view, of which I cannot assure whether it is a MATERIALIZED VIEW or a VIEW. DROP VIEW IF EXISTS myview; throws an error, if myview is a MATERIALIZED VIEW and vice versa. What I ...
Jochen Schwarze's user avatar
1 vote
1 answer
1k views

drop table causes ORA-08103 with another one

In my process I have 2 tables. target table, load table. The target table holds the current data, and a lot of queries are fired against it. When new data coming we use a FULL LOAD and the goal ...
user2671057's user avatar
1 vote
2 answers
1k views

Speedup DROP huge MySQL replicated table

I've a huge InnoDB table customer_users_old (150G) that it's not in use anymore in the database, it was renamed from customer_users. My setup has a Master-Slave replication of MySQL 5.5 with ...
pconcepcion's user avatar
1 vote
3 answers
2k views

Replacing MYSQL table with data from another table - no downtime

I have two tables with identical structures: Table1 and Table2 id INT PRIMARY KEY, a TINYINT, b TINYINT, c TINYINT Table1 is actively being used by queries from other sessions; Table2 is not ...
Phil W.'s user avatar
  • 11
5 votes
1 answer
1k views

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 ...
andrea's user avatar
  • 341
2 votes
3 answers
16k views

How to safely recreate a MS SQL table that has other objects depending on it?

I have a table in A Microsoft SQL 2012 database, called Courses. It was "upconverted" from an originally SQL 2000 database, upconverted to SQL 2008 database, and then upconverted again to 2012 (You ...
Rob V's user avatar
  • 375
3 votes
1 answer
2k views

SQL Server 'ignores' drop table command [duplicate]

I have a 500+ lines stored proccedure which I am writting and testing using the Microsoft SQL Management studio. Today I encounter the following 'strange' case. My query (in pseudocode) look like the ...
Athafoud's user avatar
  • 186
0 votes
1 answer
6k views

Why can I drop table when logged in as not owner?

I read that only owner can drop table and no other user may be granted to do so. I created a table with owner postgres and then drop it as another user. What privileges should I revoke to disallow it?...
Vlad's user avatar
  • 445
1 vote
1 answer
1k views

Is it possible to drop tablespace CASCADE in MySQL?

I am newbie with MySQL. I have created a tablespace with a datafile, and I have tried to drop this tablespace without drop any object in that tablespace by using that SQL query: DROP TABESPACE ...
BongSey's user avatar
  • 119
5 votes
1 answer
12k views

What is preventing DROP TABLE from removing a Postgres table? [duplicate]

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 ...
Borealis's user avatar
  • 151
1 vote
1 answer
699 views

Trigger to drop table only if there are no records

How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?
F_Face's user avatar
  • 21
1 vote
1 answer
2k views

Drop all tables from database command apart from named few

I have been looking for a command to drop all tables from a single database apart from a few tables without doing something like this: DROP table table1, table2, table3 and ignoring the tables I do ...
medina's user avatar
  • 197
11 votes
2 answers
42k views

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. ...
smac89's user avatar
  • 215
0 votes
2 answers
681 views

Why we can FLASHBACK DROP table where it is DDL command and hence autocommited?

DROP and TRUNCATE both are DDL commands and both are autocommited.Then why we can recover DROP table by using FLASHBACK(unless it is purge) and why we can not recover TRUNCATED data(don't consider ...
Paresh's user avatar
  • 81
7 votes
4 answers
16k views

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 ...
scottr's user avatar
  • 83
1 vote
1 answer
3k views

Reclaim disk space after dropping table(innodb_file_per_table = ON), strange scenario

I know it is often asking question. But I can't find answer for my scenario with specific condition. We use MySQL 5.5 We have always(I am sure) innodb_file_per_table = ON option in our mysql ...
user2746626's user avatar
59 votes
4 answers
155k views

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 ...
Xameer's user avatar
  • 911
2 votes
1 answer
5k views

Fastest way to disable indices and drop constraints and rebuild SQL Server table

Below are some T-SQL commands that we're running on a table with a few billion records. Most of the database's size is taken up by 5 tables like this. What is the quickest way to perform these steps ...
JustBeingHelpful's user avatar
1 vote
0 answers
21 views

Delete datafile from parttioned table

I'm used SQL Server 2014. My big table partitioned by date column and each partition has own datafile. I want delete oldest partition data and data file too. How to do it?
Tugs L's user avatar
  • 11
1 vote
1 answer
2k views

'DROP TABLE mysql.failover_console;' replicates to slaves, but 'Use mysql; DROP TABLE failover_console;' does not

I am currently looking to setup automatic failover for my MySQL 5.6.21 Master on CentOS 6.6. I have mysqlfailover set up in my lab, and it is working well enough. My question is not about that, but ...
David Hulick's user avatar
5 votes
3 answers
3k views

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 ...
Prashanth Ganesh's user avatar