SQL Server database index sorting statement, automatic sorting of database Indexes

In a large database, data changes are very frequent.
Indexes built on these data also need to be maintained frequently.
Otherwise, these data indexes will not play an appropriate role. It may even become a burden on the database itself.
We need to regularly maintain database indexes. I found this script on MSDN. However, I have fixed some minor issues. You can use this script to perform routine maintenance on Database indexes.
Copy codeThe Code is as follows:
Set nocount on;
DECLARE @ objectid int;
DECLARE @ indexid int;
DECLARE @ partitioncount bigint;
DECLARE @ schemaname sysname;
DECLARE @ objectname sysname;
DECLARE @ indexname sysname;
DECLARE @ partitionnum bigint;
DECLARE @ partitions bigint;
DECLARE @ frag float;
DECLARE @ command varchar (8000 );
DECLARE @ dbId int;
-- Ensure the temporary table does not exist
If exists (SELECT name FROM sys. objects WHERE name = 'work _ to_do ')
Drop table work_to_do;
-- Conditionally select from the function, converting object and index IDs to names.
Set @ dbId = DB_ID ();
SELECT
Object_id AS objectid,
Index_id AS indexid,
Partition_number AS partitionnum,
Avg_fragmentation_in_percent AS frag
INTO work_to_do FROM sys. dm_db_index_physical_stats (@ dbId, NULL, NULL, 'limited ')
WHERE avg_fragmentation_in_percent> 10.0 AND index_id> 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions cursor for select * FROM work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @ objectid, @ indexid, @ partitionnum, @ frag;

WHILE @ FETCH_STATUS = 0
BEGIN;
SELECT @ objectname = o. name, @ schemaname = s. name
FROM sys. objects AS o
JOIN sys. schemas as s ON s. schema_id = o. schema_id
WHERE o. object_id = @ objectid;

SELECT @ indexname = name
FROM sys. indexes
WHERE object_id = @ objectid AND index_id = @ indexid;

SELECT @ partitioncount = count (*)
FROM sys. partitions
WHERE object_id = @ objectid AND index_id = @ indexid;

-- 30 is an arbitrary demo-point at which to switch between reorganizing and rebuilding
IF @ frag <30.0
BEGIN;
SELECT @ command = 'alter INDEX ['+ @ indexname +'] ON '+ @ schemaname +'. ['+ @ objectname +'] REORGANIZE ';
IF @ partitioncount> 1
SELECT @ command = @ command + 'partition = '+ CONVERT (CHAR, @ partitionnum );

EXEC (@ command );
END;

IF @ frag & gt; = 30.0
BEGIN;
SELECT @ command = 'alter INDEX ['+ @ indexname +'] ON '+ @ schemaname +'. ['+ @ objectname +'] REBUILD ';
IF @ partitioncount> 1
SELECT @ command = @ command + 'partition = '+ CONVERT (CHAR, @ partitionnum );

EXEC (@ command );
END;
PRINT 'executed' + @ command;

Fetch next from partitions INTO @ objectid, @ indexid, @ partitionnum, @ frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- Drop the temporary table
If exists (SELECT name FROM sys. objects WHERE name = 'work _ to_do ')
Drop table work_to_do;
GO

This script will automatically delete the table after work_to_do is created during running. If you do not like this, you can use a temporary table.