Firstly, find out your FOREIGN KEY constraint name in this way:
CONSTRAINT_NAME, -- <<-- the one you want!
REFERENCED_TABLE_NAME = 'My_Table';
And then you can remove the named constraint in the following way:
DELETE - DATA MANIPULATION LANGUAGE(DML)
The DELETE statement in any RDBMS is considered a DML statement. Also known as CRUD (Create, Read, Update, Delete), this kind of statement is intended to manipulate data in a database without affecting the underlying structure of the objects. What this means in practical term is:
A DELETE statement can be fine-...
I recently did this in my organization wherein we wanted to handle a table with billion + rows.
All the credit for the idea goes to Aaron Bertrand and is from his blog post Trick Shots : Schema Switch-A-Roo
Test below process on a small table and get your self comfortable before doing it in PROD.
create 2 schemas fake and shadow with authorization dbo.
Dropping a table will free up the space within the database, but will not release the space back to Windows. That requires shrinking the database file. However we don't want the database file full. We want lots of free space so that as we load more data we don't have to grow the data file frequently. This causes fragmentation of the data file on the ...
When you drop table data is still in the database but SQL Server is treating it as free space and it will overwrite it soon. You need to stop all database activities or make a copy of mdf and ldf files and see if you can find a third party tool that supports this. One tool I know is ApexSQL Recover but I guess there are probably others out there.
If you just want to Truncate and reload data, then faffing around with indexes is not necessarily useful.
If you're inserting data in Clustered Index order i.e. in CONCEPT_CD ASC order, then there's no real advantage to dropping the Clustered Index. It'll be far more pain rebuilding it at the end on 3 billion rows than it would inserting the data in your ...
In addition to the restrictions pointed out in Evan's answer, there are a number of other things that can prevent dropping tables. Most of them are similar to the restrictions on normal disk-based tables, and you should see them called out in your error message.
For instance, if I have an in-memory table referenced by a natively-compiled (schemabound) ...
Superusers are always exempt from lowly permission checks. The documentation:
A database superuser bypasses all permission checks, except the right
to log in. This is a dangerous privilege and should not be used carelessly; ...
To see whether your current user is a superuser:
To list all superuser roles:
Although I cannot speak for MySQL, here's a quick table comparing some aspects of truncate vs. delete within Oracle.
truncate | delete
--------------------------------- | ---------------------------
DDL (implicitly commits, | DML
including any pending DML) |
Does not generate undo info | ...
If the database is in full recovery, and the pages haven't been reused yet, you may be able to recover some of this data from the log by creating a dummy table and grabbing data from things like DBCC PAGE.
The full technique is way too involved for a full answer here, and I don't expect this to be a long-term answer on this site, nor do I want to replicate ...
I ran this query then pasted the results back into query window to drop all the tables:
SELECT 'DROP TABLE ' + NAME from sys.tables
ORDER BY NAME
If you want to delete all tables but keep those with names that started with A, B, C or D:
SELECT 'DROP TABLE ' + NAME from sys.tables
WHERE NAME NOT LIKE '[ABCD]%'
GROUP BY NAME
Here is the way I see it.
Pros for #1
Because you are using a separate table your production table stays in use until you are done. No locks on it (beyond those needed to read the data).
This also goes with what @AaronBertrand said: you can do it piecemeal, test etc
You can change column order at need
Pros for #2
It is an all or nothing operation. ...
You can list the running backends with
SELECT * FROM pg_stat_activity;
identify the process trying to rollback something which is working on this table.
Find its pid.
You can terminate a backend with the query SELECT pg_terminate_backend(64738), where 64738 is the pid column in your previous SELECT.
After that, you can likely DROP that table.
If even ...
You can use the following:
The following query will generate all the keys which you have to drop auto, then simply run:
SELECT CONCAT ('alter table ',
' DROP FOREIGN KEY ',
While ON DELETE CASCADE can be attempted, it is worth noting what the output of a mysqldump looks like. At the top of every mysqldump you will see directives which disable foreign key checks and unique key checks:
-- MySQL dump 10.13 Distrib 5.5.12, for Win64 (x86)
-- Host: localhost Database: sample
Using such a CURSOR, you can print or execute all the DROP statements:
DECLARE cur_del CURSOR FOR
WHERE name NOT IN ('x', 'y', 'z', ...);
DECLARE @table sysname;
DECLARE @sql nvarchar(max);
FETCH NEXT FROM cur_del
WHILE @@FETCH_STATUS = 0
You can see you are creating temptable with same name.You cannot create temp table with same name in single query or batch this is documented as per BOL document. From Bol document
If more than one temporary table is created inside a single stored procedure or batch, they must have different names.
You should use below logic it will just check before ...
MyISAM -- DROP is like a file delete. TRUNCATE performs the C file action of removing all the data but leaving the file in existence.
InnoDB with its own tablespace (cf innodb_file_per_table) -- like a file delete
InnoDB without its own tablespace -- lots of blocks to free up; this could take time.
A file drop can be fast or not so fast, depending on the ...
Just use DROP TABLE, there is one caveat,
Memory-optimized tables and natively compiled stored procedures cannot be created or dropped if there is a server or database trigger for that DDL operation. Remove the server and database triggers on CREATE/DROP TABLE and CREATE/DROP PROCEDURE.
Make sure there are no DDL event notifications,
My solution at this point is a function
CREATE OR REPLACE FUNCTION dropview(IN view_schema TEXT, IN view2drop TEXT)
DECLARE drop_stmnt VARCHAR;
WHEN relkind = 'm' THEN 'MATERIALIZED VIEW'
WHEN relkind = 'v' THEN 'VIEW'
WHERE relname = view2drop
You can modify the ntext columns to nvarchar(max) using T-SQL. I'd only recommend doing this if the table/database is not actively being used. If the table is very small, this method will be quite fast and typically problem-free. However, if you have hundreds of millions of rows or more, I'd be concerned about doing this without testing it first in a non-...
The simple way around this is to change some options in Management Studio.
Go to Tools, Options, Designers, Table and Database Designers. Uncheck "Prevent saving changes that require table re-creation".
Generally speaking, Management Studio will properly handle dependencies when you modify a table in a way that requires it to be recreated (it will do ...
This is an old verified bug in MySQL for InnoDB on larger tables.
As this has been patched quite some time ago, perhaps it's not the same situation, but it sure seems similar.
The workarounds for the older versions are:
Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of ...
According to the documentation it is not possible:
The tablespace to be dropped must not contain any data files; in other
words, before you can drop a tablespace, you must first drop each of its
data files using ALTER TABLESPACE ... DROP DATAFILE (see Section 13.1.8,
Community Wiki answer, originally a comment on the question by a_horse_with_no_name
You need to end every SQL statement with ;. You didn't do that and thus your statement was not executed at all. Run DROP TABLE pnt; instead. See e.g. here: Understanding postgres prompts
The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to:
Automatically drop objects that depend on the table (such as views).
When you are dropping a table that is referenced by another table, the object that immediately ...
DROP TABLE TableName -> delete the table from the database.
TRUNCATE TABLE TableName -> delete the table's data, unconditionally... and reset the IDENTITY seed value, if the table has an IDENTITY field.