可以在 SQL 服务器中使用游标截断多个表

Question

我试过这个但没有用

ALTER Procedure [dbo].[SP_TruncateDTS] as
begin
declare @Table Varchar(200)
declare cur1 cursor for
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE  TABLE_NAME like '%DTS%'  and  TABLE_TYPE = 'BASE TABLE'
open cur1
fetch next from cur1 into @Table
while @@FETCH_STATUS <> -1
begin

TRUNCATE TABLE @Table

fetch next from cur1 into @Table
end
close cur1
deallocate cur1
end

Answer 1

不能将标识符(如表名、列名等(作为参数在查询中传递。 相反，您需要使用动态 SQL。

因此，将内部循环替换为：

declare @sql nvarchar(max);
set @sql = 'TRUNCATE TABLE [Table]';
set @sql = replace(@sql, '[Table]', @table);
exec sp_executesql @sql;

Answer 2

下面的示例使用sys.tables目录视图和聚合字符串串联，而不是任务的光标。

DECLARE @SQL nvarchar(MAX) = (
SELECT N'TRUNCATE TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name) +N';'
FROM sys.tables
WHERE  name like N'%DTS%'
FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
EXECUTE sp_executesql @SQL;

在 SQL Serveer 2017 及更高版本中，可以使用STRING_AGG：

DECLARE @SQL nvarchar(MAX) = (
SELECT STRING_AGG ('TRUNCATE TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + N'.' + QUOTENAME(name),';')
FROM sys.tables
WHERE  name like N'%DTS%'
);
EXECUTE sp_executesql @SQL;

请注意，此架构限定名称并使用QUOTENAME来处理名称不符合常规标识符命名规则的情况。