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