CODEWITHSUNDEEP
sqlsql-serverschema
Federico Gentile
Federico Gentile

Reputation: 5940

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?

Upvotes: 0

Views: 854

Answers (2)

allmhuran
allmhuran

Reputation: 4464

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:


create schema boo;
create table boo.t(i int);

create table #keep (name sysname); 
insert #keep values ('myFirsttable'), ('mySecondTable'), ('myThirdTable');

exec sp_msforeachtable 
   @command1='drop table ?; print ''dropped ?''',
   @whereand = 'and schema_name(schema_id) = ''your_schema_name'' and object_name(object_id) not in (select name from #keep)';

But personally, I'd probably just write my own stored procedure with a cursor. It's harder to mess up.

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

Upvotes: 1

Charlieface
Charlieface

Reputation: 71805

You could place a list of tables you want to delete stored in a table variable or Table-Valued Parameter @tables then you can simply execute dynamic SQL with it.

DECLARE @tables TABLE (tablename sysname);
INSERT @tables (tablename)
SELECT t.name
FROM sys.tables t
WHERE t.schema_id = SCHEMA_ID('your_schema_name');

DECLARE @sql nvarchar(max) =
(
    SELECT STRING_AGG(CAST(
        'DROP TABLE ' + QUOTENAME('your_schema_name') + '.' + QUOTENAME(tablename) + ';'
      AS nvarchar(max)), '
'   )
    FROM @tables
);

EXEC sp_executesql @sql;

Alternatively, select it directly from sys.tables

DECLARE @sql nvarchar(max) =
(
    SELECT STRING_AGG(CAST(
        'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' + QUOTENAME(t.name) + ';'
      AS nvarchar(max)), '
'   )
    FROM sys.tables t
    WHERE t.schema_id = SCHEMA_ID('your_schema_name')
);

EXEC sp_executesql @sql;

Upvotes: 1

Related Questions