I am not sure from where you have got this script however it seems you are trying to rebuild indexes if the fragmentation level is more than 30 and reorganize if the same is less than 30. Your script is actually command to issue reorg and rebuild statement based on other part of code which is not copied here.
You may try below piece of code for inquiring the fragmentation level within a given database:
1st Query:
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
and indexstats.page_count >=1000 and
dbindexes.name is not null
ORDER BY indexstats.avg_fragmentation_in_percent desc
2nd Query:
DECLARE @DatabaseID int
SET @DatabaseID = DB_ID()
SELECT DB_NAME(@DatabaseID) AS DatabaseName,
schemas.[name] AS SchemaName,
objects.[name] AS ObjectName,
indexes.[name] AS IndexName,
objects.type_desc AS ObjectType,
indexes.type_desc AS IndexType,
dm_db_index_physical_stats.partition_number AS PartitionNumber,
dm_db_index_physical_stats.page_count AS [PageCount],
dm_db_index_physical_stats.avg_fragmentation_in_percent AS AvgFragmentationInPercent
FROM sys.dm_db_index_physical_stats (@DatabaseID, NULL, NULL, NULL, 'LIMITED') dm_db_index_physical_stats
INNER JOIN sys.indexes indexes ON dm_db_index_physical_stats.[object_id] = indexes.[object_id] AND dm_db_index_physical_stats.index_id = indexes.index_id
INNER JOIN sys.objects objects ON indexes.[object_id] = objects.[object_id]
INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id]
WHERE objects.[type] IN('U','V')
AND objects.is_ms_shipped = 0
AND indexes.[type] IN(1,2,3,4)
AND indexes.is_disabled = 0
AND indexes.is_hypothetical = 0
AND dm_db_index_physical_stats.alloc_unit_type_desc = 'IN_ROW_DATA'
AND dm_db_index_physical_stats.index_level = 0
AND dm_db_index_physical_stats.page_count >= 1000
Kindly note that, page count dictates how big is the table and contains how many pages. For performing reorg or rebuild, you may check wonderful script from Mr. Ola Hallengren and use them for your maintenance job, here also you can customize the fragmentation level on which you want to perform reorg or rebuild, it also has option of offline and online, you may read more details at this link.
https://ola.hallengren.com/
You may also check the best practice and possible impacts on website of Mr. Brent Ozar and can be referred as below:
https://www.brentozar.com/archive/2017/12/index-maintenance-madness/
If fragmentation is not showing showing any sign of improvement, that could mean its a very small table having page count much lesser than 1000. Standard practice is to consider only those tables which is at least 1000 pages or more.
Kindly refer below links explaining same:
Why is my database still fragmented after I rebuilt and reindexed everything?
SQL Server 2012, rebuild not lowering avg fragmentation
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/c419f809-c4c6-4c7f-9f36-c9a56d21d37a/index-still-fragmented-after-rebuild?forum=sqldatabaseengine
In case, you have bigger tables however doesn't have clustered index meaning its a heap then you should go for table rebuild with below command:
Alter table SchemaName.TableName rebuild
You can read more details about that on this link.
The most important aspect is to see "How to detect whether index fragmentation affects SQL Server performance". please check more details about that here.
Hope above helps.
