问题描述
我正在使用以下脚本截断数据库的日志文件.但是我必须在单独的窗口中执行以下脚本结果,因为我无法安排作业.
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)
做同样的事情...