Why am I getting a syntax error near the "c" keyword?
--Use 'Database'
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
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
Error message
Incorrect syntax near the 'with' keyword (to restore indexes). If this statement is a generic table expression, xmlnamespaces clause, or change context clause, the previous statement must end with a semicolon
+3
source to share
1 answer
You need to use Dynamic Sql
. Try it.
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
+1
source to share