从模式中删除所有表，除了SQL中的特定表?

Question

我有一个SQL数据库，我想删除几乎所有与特定模式相关的表，除了其中的几个。因此，我认为我需要编辑以下sql查询(删除特定模式的所有表):

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

你能建议一个聪明而优雅的方法，这样我就可以添加一个我想要保留的表列表，并删除其他所有表吗?

Answer 1

如果您想继续使用sp_msforeachtable，则传入一组表名以继续使用临时表。下面是一个使用boo模式的示例:

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)';

但就我个人而言，我可能只会用游标编写自己的存储过程。更不容易搞砸。

注意，该解决方案要求您将想要保留的表放入临时表中。Charlieface的解决方案要求您将想要的表名放入到table变量中。

Answer 2

你可以将你想要删除的表列表存储在表变量或表值参数@tables中，然后你可以简单地使用它执行动态SQL。

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;

或者，直接从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;