如何在 SQL Server 中将截断日志脚本设为动态 SQL?

问题描述

我正在使用以下脚本截断数据库的日志文件.但是我必须在单独的窗口中执行以下脚本结果，因为我无法安排作业.

I am using the below script to truncate the log files of a database. But I have to execute the below script results in separate window due to this I am not able to schedule the jobs .

SET NOCOUNT ON 

SELECT 
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

预期的 O/P 是脚本必须运行而不是复制粘贴上述查询结果

Expected O/P is script has to run instead of copy pasting the above query results

推荐答案

您可以使用动态 SQL 实现这一点，而无需使用 cursor:

You can achieve this with Dynamic SQL, without using a cursor:

SET NOCOUNT ON 

DECLARE @SQL VARCHAR(MAX) = ''

SELECT @SQL = @SQL +
      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY);' 
    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
FROM 
         sys.master_files mf 
    JOIN sys.databases d 
        ON mf.database_id = d.database_id 
WHERE d.database_id > 4 and mf.type_desc = 'LOG'

PRINT   (@SQL)
EXEC    (@SQL)

做同样的事情...