50

Firstly, find out your FOREIGN KEY constraint name in this way: SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'My_Table'; And then you can remove the named constraint in the following way: ...


31

You can build up a string using the catalog views, e.g.: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += ' DROP TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';' FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE t.name LIKE 'LG_001%'; PRINT @sql; -- EXEC sp_executesql @sql; Of ...


22

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-...


9

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. ...


9

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 ...


7

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.


6

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 ...


6

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) ...


5

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 ...


5

You can use information_schema to check if the table exists but in MySQL there is a simpler method: DROP TABLE IF EXISTS employees ;


5

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 | ...


4

Superusers are always exempt from lowly permission checks. The documentation: superuser status 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: SHOW IS_SUPERUSER; To list all superuser roles: ...


4

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. ...


4

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 ...


3

Editied the Query above. Changed Referenced Table name to Table Name as Referenced Table name is the table which is being referenced and hence the result of the original query wont show you the foreign keys on your table. SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, -- <<-- the one you want! REFERENCED_TABLE_NAME, ...


3

This is how it's documented: CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely. Emphasis mine


3

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 ', TABLE_SCHEMA, '.', TABLE_NAME, ' DROP FOREIGN KEY ', CONSTRAINT_NAME, ';') FROM INFORMATION_SCHEMA....


3

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


3

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 -- ------------------------------------------------...


3

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 ...


3

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 ...


3

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, Memory-optimized ...


2

My solution at this point is a function CREATE OR REPLACE FUNCTION dropview(IN view_schema TEXT, IN view2drop TEXT) RETURNS void LANGUAGE plpgsql AS $$ DECLARE drop_stmnt VARCHAR; BEGIN SELECT CASE WHEN relkind = 'm' THEN 'MATERIALIZED VIEW' WHEN relkind = 'v' THEN 'VIEW' END FROM pg_class WHERE relname = view2drop INTO drop_stmnt;...


2

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-...


2

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 ...


2

According to the documentation it is not possible: https://dev.mysql.com/doc/refman/5.6/en/drop-tablespace.html Important 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, “ALTER ...


2

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


2

Using such a CURSOR, you can print or execute all the DROP statements: DECLARE cur_del CURSOR FOR SELECT QUOTENAME(SCHEMA_NAME(schema_id))+'.'+QUOTENAME(name) FROM sys.tables WHERE name NOT IN ('x', 'y', 'z', ...); OPEN cur_del; DECLARE @table sysname; DECLARE @sql nvarchar(max); FETCH NEXT FROM cur_del INTO @table; WHILE @@FETCH_STATUS = 0 ...


2

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: CASCADE Automatically drop objects that depend on the table (such as views). (Emphasis mine.) When you are dropping a table that is referenced by another table, the object that immediately ...


Only top voted, non community-wiki answers of a minimum length are eligible