The object selection needs to filter external tables

[alasdaircs]

ALTER TABLE [vf].[v_matter] REBUILD ; FAILED : 46518The feature 'ALTER TABLE' is not supported with external tables.

It bashes on past this, but obviously the script then ends in the error state so other errors will be concealed. I'll see if I can find time to put together a PR.

[atedsimple]

Hi @alasdaircs

How is your PR coming along to filter out external tables, as we too, are experiencing this issue?

[nvdbosch1990]

@alasdaircs I used this cursor to only retrieve the objectids of our user_tables, this also improves the performance of retrieving the results. Take note that i use an older version of the script so the table definition and query are not equal to the latest version.

IF OBJECT_ID('tempdb..#idxBefore') IS NULL
        BEGIN
            CREATE TABLE #idxBefore (
                object_id INT,
                ObjectSchema NVARCHAR(256),
                ObjectName NVARCHAR(256),
                IndexName NVARCHAR(256),
                type INT,
                type_desc NVARCHAR(256),
                avg_fragmentation_in_percent FLOAT,
                page_count INT,
                index_id INT,
                partition_number INT,
                avg_page_space_used_in_percent FLOAT,
                record_count BIGINT,
                ghost_record_count BIGINT,
                forwarded_record_count BIGINT,
                OnlineOpIsNotSupported BIT,
                ObjectDoesNotSupportResumableOperation BIT,
                SkipIndex BIT,
                SkipReason NVARCHAR(128)
            )
        END

        DECLARE @ObjectIds NVARCHAR(40)
        DECLARE @Sql NVARCHAR(MAX)

        -- Cursor to iterate over all objectids of user tables
        DECLARE ObjectIdCursor CURSOR FOR
        select object_id from sys.objects where type= 'U'
        OPEN ObjectIdCursor
        FETCH NEXT FROM ObjectIdCursor INTO @ObjectIds

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- Step 2: Construct the dynamic SQL command to insert into #idxBefore
            SET @Sql = 'INSERT INTO #idxBefore
                        SELECT
                            idxs.[object_id],
                            OBJECT_SCHEMA_NAME(idxs.object_id) AS ObjectSchema,
                            OBJECT_NAME(idxs.object_id) AS ObjectName,
                            idxs.name AS IndexName,
                            idxs.type,
                            idxs.type_desc,
                            i.avg_fragmentation_in_percent,
                            i.page_count,
                            i.index_id,
                            i.partition_number,
                            i.avg_page_space_used_in_percent,
                            i.record_count,
                            i.ghost_record_count,
                            i.forwarded_record_count,
                            NULL AS OnlineOpIsNotSupported,
                            NULL AS ObjectDoesNotSupportResumableOperation,
                            0 AS SkipIndex,
                            REPLICATE('''', 128) AS SkipReason
                        FROM sys.indexes idxs
                        JOIN sys.dm_db_index_physical_stats(DB_ID(), '+@ObjectIds+', NULL, NULL, ''LIMITED'') i
                            ON i.object_id = idxs.object_id AND i.index_id = idxs.index_id
                        WHERE idxs.type IN (1, 2, 5, 6)
                            AND (alloc_unit_type_desc = ''IN_ROW_DATA'' OR alloc_unit_type_desc IS NULL)
                            AND OBJECT_SCHEMA_NAME(idxs.object_id) != ''sys''
                            AND idxs.is_disabled = 0'

            -- Step 3: Execute the dynamic SQL
            EXEC sp_executesql @Sql

            FETCH NEXT FROM ObjectIdCursor INTO @ObjectIds

        END

        CLOSE ObjectIdCursor
        DEALLOCATE ObjectIdCursor