删除SQL Server数据库中的所有表，除了少数 [英] Delete all tables in SQL Server database except few

问题描述

我在我的数据库中有大约50+个表现在我想要的是删除数据库中的所有表，除了少数。

I have around 50+ table in my database now what I want is drop all the tables in database except few.

现在我知道的是 sys.tables 是一个列出所有表的表，所以最初我运行一个查询

now what I know is sys.tables is a table that list all tables so initially I ran a query like this

delete from sys.tables where name like '%DynamicSurgery' (or any other condition)

认为它可能工作。但是如我预期的，它会引发一个错误

thinking that it might work. But as I expected it throws an error as

不允许对系统目录进行特别更新。

Ad hoc updates to system catalogs are not allowed.

请告诉我是否有办法删除SQL Server中的多个。

Please tell me if there is a way to delete multiples in SQL Server?

推荐答案

您可以使用动态查询 DROP 所需的表格：

You can use dynamic query to DROP the required tables:

DECLARE @ExecSQL AS NVARCHAR (MAX) = '';

SELECT @ExecSQL = @ExecSQL + 
    'DROP TABLE ' + QUOTENAME(S.name) + '.' + QUOTENAME(T.name) + '; ' 
FROM sys.tables T
JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE T.name LIKE '%DynamicSurgery'

--PRINT @ExecSQL
EXEC (@ExecSQL)

这篇关于删除SQL Server数据库中的所有表，除了少数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！