Copy codeThe Code is as follows:
DECLARE cur CURSOR
SELECT
[Object_name] = s. name + '.' + OBJECT_NAME (A. object_id ),
B. name
FROM sys. dm_db_index_physical_stats (DB_ID ('adventureworks'), NULL, null, null) AS
JOIN sys. indexes AS B
On a. [object_id] = B. [object_id]
And a. [index_id] = B. [index_id]
JOIN sys. objects AS o
On a. [object_id] = o. [object_id]
JOIN sys. schemas AS s
ON o. [schema_id] = s. [schema_id]
Where a. [index_id]> 0
And not exists (
SELECT *
FROM sys. xml_indexes
Where a. [object_id] = [object_id]
And a. [index_id] = [index_id]
);
OPEN cur;
DECLARE @ objname varchar (128), @ indname varchar (128 );
DECLARE @ SQL nvarchar (4000 );
Fetch next from cur INTO @ objname, @ indname;
-- Reassembles all indexes, regardless of the degree of fragmentation of the index.
WHILE @ FETCH_STATUS = 0
BEGIN
SET @ SQL = 'alter Index' + @ indname + 'on' + @ objname + 'rebuild ';
EXEC (@ SQL );
Fetch next from cur INTO @ objname, @ indname;
END
CLOSE cur;
DEALLOCATE cur;