gpt4 book ai didi

sql - 为什么我在关键字 'with' 附近收到语法错误?

转载 作者:行者123 更新时间:2023-12-04 20:49:41 25 4
gpt4 key购买 nike

--Use 'Database'BEGIN TRANdeclare @name1 varchar(150)declare @name2 varchar(150)declare CRS Cursor for     SELECT         OBJECT_NAME(ind.OBJECT_ID) AS DBtable,        ind.name AS IndexName       FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats         INNER JOIN sys.indexes ind          ON ind.object_id = indexstats.object_id         AND ind.index_id = indexstats.index_id     WHERE indexstats.avg_fragmentation_in_percent > 10 and ind.name is not null    ORDER BY indexstats.avg_fragmentation_in_percent DESC;open CRS    fetch Next from CRS into @name1,@name2    While (@@FETCH_STATUS=0)     begin       select @name1 as [Table name],@name2 as [Index name];          ALTER INDEX @name2 ON @name1 REBUILD PARTITION = ALL           WITH (fillfactor=85,  PAD_INDEX  = on, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, ONLINE = on, SORT_IN_TEMPDB = on )       fetch next from CRS into @name1,@name2;     end close CRS Deallocate CRS      ROLLBACK

错误信息

Incorrect syntax near the keyword 'with' (for rebuild the indices). If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon

最佳答案

您需要使用Dynamic Sql。试试这个。

BEGIN TRAN

DECLARE @name1 VARCHAR(150)
DECLARE @name2 VARCHAR(150)
DECLARE CRS CURSOR FOR
SELECT Object_name(ind.OBJECT_ID) AS DBtable,
ind.NAME AS IndexName
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent > 10
AND ind.NAME IS NOT NULL
ORDER BY indexstats.avg_fragmentation_in_percent DESC;

OPEN CRS

FETCH Next FROM CRS INTO @name1, @name2

WHILE ( @@FETCH_STATUS = 0 )
BEGIN

DECLARE @sql NVARCHAR(max)

SET @sql='ALTER INDEX ' + Quotename(@name2) + ' ON '
+ Quotename(@name1)
+ ' REBUILD PARTITION = all WITH (FILLFACTOR=85, PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = ON )'

EXEC sp_executesql
@sql

FETCH next FROM CRS INTO @name1, @name2;
END

CLOSE CRS

关于sql - 为什么我在关键字 'with' 附近收到语法错误?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/27852467/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com