Questions tagged [drop-table]

This tag is for the SQL command DROP TABLE

Filter by
Sorted by
Tagged with
0
votes
1answer
40 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 ...
0
votes
1answer
20 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 ...
0
votes
1answer
35 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, ...
0
votes
0answers
51 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 ...
0
votes
2answers
367 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 ...
2
votes
0answers
53 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 ...
4
votes
3answers
2k 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].[...
3
votes
1answer
4k 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 ...
2
votes
3answers
907 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 ...
0
votes
2answers
751 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 (...
2
votes
1answer
108 views

Proper Way to Remove Mysql Tables

I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?. Removing the .frm files ...
4
votes
2answers
484 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
1answer
98 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 ...
1
vote
1answer
391 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 ...
1
vote
1answer
419 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 ...
1
vote
2answers
157 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 ...
5
votes
1answer
519 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 ...
1
vote
3answers
4k 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 ...
3
votes
1answer
707 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 ...
0
votes
1answer
2k 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?...
1
vote
1answer
585 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 ...
3
votes
1answer
4k 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 ...
0
votes
1answer
415 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?
1
vote
1answer
920 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 ...
4
votes
2answers
8k views

DROP TABLE … CASCADE does not drop any dependent table

I am still relatively new to psql 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 ...
0
votes
1answer
331 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 ...
6
votes
4answers
10k 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 ...
2
votes
1answer
2k 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 ...
40
votes
2answers
76k views

MySQL: 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 ...
2
votes
1answer
3k 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 ...
1
vote
0answers
20 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?
1
vote
1answer
1k 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 ...
5
votes
3answers
2k 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 ...
1
vote
1answer
952 views

How to drop table with all reference to this table

I want to drop a table, and this table has be many table's foreign key. So I want to drop all those reference (just refernece, not other table) within the drop table process. Is there any simple SQL,...
8
votes
4answers
25k views

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 ?...
3
votes
1answer
5k views

Is it possible to force drop of mysql tables with FK?

For dropping a table in which a Foreign Key is used in other tables, it is necessary to drop all other tables having FK connection with the corresponding table. Is there a short way to drop a table, ...
4
votes
2answers
52k views

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 ...
7
votes
4answers
18k views

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 ...
15
votes
6answers
69k views

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