我有一堆数据库表，我只想删除一个。我的脚本如下...。但是，当我尝试运行时，我创建了一个无限循环。
任何想法我该怎么做？
/* Drop all tables */
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects
WHERE [type] = 'U' AND category = 0 ORDER BY [name])
WHILE @name IS NOT NULL
IF @name != 'tableNotToBeDropped'
BEGIN
SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'
EXEC (@SQL)
PRINT 'Dropped Table: ' + @name
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects
WHERE [type] = 'U' AND category = 0 AND [name] > @name
ORDER BY [name])
END
GO
根本不需要循环。DROP TABLE可以使用一条语句删除多个表：
DECLARE @tables NVARCHAR(MAX) =
STUFF((SELECT ',' + QUOTENAME([table_name]) AS [text()]
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'dbo'
AND TABLE_NAME <> 'tableNotToBeDropped'
FOR XML PATH('')),1,1,'');
DECLARE @sql NVARCHAR(MAX) = 'DROP TABLE ' + @tables;
-- debug
SELECT @sql;
EXEC sp_executesql @sql;
怎么运行的：
请记住，如果您的表已定义外键，则删除顺序很重要。
SQL Server 2017版本：
DECLARE @sql NVARCHAR(MAX) = 'DROP TABLE IF EXISTS '
+ (SELECT string_agg(QUOTENAME(table_name), ',')
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = 'dbo'
AND TABLE_NAME <> 'tableNotToBeDropped'
AND TABLE_NAME LIKE 't%');
SELECT @sql;
EXEC sp_executesql @sql;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句