Remove all tables from schema except specific ones in SQL?
NickName:Federico Gentile Ask DateTime:2021-08-30T19:25:35

Remove all tables from schema except specific ones in SQL?

I have a SQL database and I would like to remove almost all tables related to a specific schema except a couple of them. Therefore I think I would need to edit the following sql query (which removes all tables of a specific schema):

EXEC sp_MSforeachtable
@command1 = 'DROP TABLE ?'
, @whereand = 'AND SCHEMA_NAME(schema_id) = ''your_schema_name'' '

Would you be able to suggest a smart and elegant way so that I can add a list of tables that I would like to keep and remove everything else?

Copyright Notice：Content Author:「Federico Gentile」，Reproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article：https://stackoverflow.com/questions/68983346/remove-all-tables-from-schema-except-specific-ones-in-sql

Answers
allmhuran 2021-08-30T12:08:16

If you want to keep using sp_msforeachtable, pass in the set of tables names to keep using a temp table. Here's an example using the boo schema:\n\ncreate schema boo;\ncreate table boo.t(i int);\n\ncreate table #keep (name sysname); \ninsert #keep values ('myFirsttable'), ('mySecondTable'), ('myThirdTable');\n\nexec sp_msforeachtable \n @command1='drop table ?; print ''dropped ?''',\n @whereand = 'and schema_name(schema_id) = ''your_schema_name'' and object_name(object_id) not in (select name from #keep)';\n\nBut personally, I'd probably just write my own stored procedure with a cursor. It's harder to mess up.\nNote that this solution expects you to put the tables you want to keep into the temp table. Charlieface's solution expects you to put the names of tables you want to drop into the table variable.",

Products recommended by Amazon
More >>>
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
SanDisk 128GB microSDXC-Card, Licensed for Nintendo-Switch - SDSQXAO-128G-GNCZN
 HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
HUANUO Dual Monitor Stand - Adjustable Spring Monitor Desk Mount Swivel Vesa Bracket with C Clamp, Grommet Mounting Base for 13 to 27 Inch Computer Screens - Each Arm Holds 4.4 to 14.3lbs
 AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
AMD Ryzen 5 5600X 6-core, 12-Thread Unlocked Desktop Processor with Wraith Stealth Cooler
 Timberland Men's 6-Inch Premium Waterproof Boot
Timberland Men's 6-Inch Premium Waterproof Boot
More about “Remove all tables from schema except specific ones in SQL?” related questions
Remove all tables from schema except specific ones in SQL?

I have a SQL database and I would like to remove almost all tables related to a specific schema except a couple of them. Therefore I think I would need to edit the following sql query (which remove...

Show Detail
SQL query to delete all tables and their data from a specific schema

I need to write a SQL query to delete all tables and their data from a specific schema. For example in my database I have tables with schema dws6 and I want a script in SQL that will delete all the

Show Detail
How to drop all tables from specific schema in C# SQL Server?

I'm working on a database migration where I need to drop all the tables in a specific schema and then run another script to recreate them from another database. I'm running into issues with trying to

Show Detail
SQL to UPDATE all records except ones that belong to a specific table

For my tables users and assigned_roles I want to set users.confirmed = 0 except where users belongs to assigned_roles. assigned_roles table ID| USER_ID| ROLES SQL UPDATE users, assigned_role...

Show Detail
Truncate all tables (except desired ones) is not working

I'm trying to write a command in terminal which will truncate all tables in database except don't truncate those tables which are specified. This are my commands // create tmp database mysql -h

Show Detail
Get all tables in SQL Server database except some selected ones?

I'm looking to display names of all tables in mySQL Server database with the exception of three selected ones. In MySQL I used a Query like: SHOW TABLES FROM " + self.databaseName + " WHERE

Show Detail
SELECT all Tables with specific prefix except one

im trying to select (or delete) rows from many tables except one specific one. Example: One_Table_A One_Table_A1 One_Table_A2 One_Table_A3 One_Table_B One_Table_B1 One_Table_B2 Now I want to select...

Show Detail
Is there a way to TRUNCATE most tables in a MySQL schema?

I'm looking for a query (or series of) to TRUNCATE all tables in my schema (which has a few hundred tables) EXCEPT for a 4 specific ones. How might I go about doing that? Thanks!

Show Detail
Bash script to remove all files and directories except specific ones

I am trying to write a very simple Bash shell script that will cd in a specific directory, it will remove all files and directories except some few selected ones and then cd back to the original di...

Show Detail
Mysql : How to remove NOT NULL constraints from all the tables in a given schema

I am using Mysql(5.7) database. Suppose I have a schema by name main_schema and I have 100 tables in that schema. I want to remove all null constraints for all the tables. How can achieve it? Any

Show Detail