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