Questions tagged [drop-table]
This tag is for the SQL command DROP TABLE
56 questions
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 ...
- 1
1
vote
2
answers
75
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 ...
- 353
0
votes
1
answer
89
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 ...
- 101
0
votes
0
answers
100
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 ...
- 135
1
vote
1
answer
395
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,
...
- 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....
- 23
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 ...
- 13
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.
...
-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 [#...
- 3,499
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 ...
- 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 ...
- 11
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. ...
- 151
-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 ...
- 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 ...
- 131
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
...
- 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?
- 131
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?
- 39.5k
1
vote
1
answer
805
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 ...
- 123
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 ...
- 21
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 ...
- 113
0
votes
1
answer
808
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, ...
- 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 ...
- 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 ...
- 150
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 ...
- 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].[...
- 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 ...
- 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 ...
- 1,481
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 (...
- 64.6k
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 ...
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 ...
- 327
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 ...
- 115
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 ...
- 137
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 ...
- 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 ...
- 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 ...
- 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 ...
- 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?...
- 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 ...
- 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 ...
- 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?
- 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 ...
- 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. ...
- 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 ...
- 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 ...
- 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 ...
- 19
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 ...
- 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 ...
- 2,116
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?
- 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 ...
- 23
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 ...
