René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"ColumnstoreOptimize @table parameter doesn't handle '-' values correctly","articleBody":"Hello!\n\nI faced an issue while I was trying to exclude a table from an entire schema on the ColumnstoreOptimize procedure (just like on StatsOptimize).\nExample:\n\n**Command**\n```sql\nEXEC [dba].[ColumnstoreOptimize]\n     @Tables = N'SchemaLab.%,-TableB',\n     @DensityThreshold = 0,\n     @OpenThreshold = 0,\n     @DeleteThreshold = 0,\n     @TimeLimit = NULL,\n     @Execute = N'N';\n```\n\n**Output**\n\n\u003e Date and time: 2026-01-16 12:56:10\n\u003e Server: my-lab-workspace\n\u003e Version: 10.0.29311.0\n\u003e Edition: SQL Azure\n\u003e Parameters: @Tables= 'SchemaLab.%,-TableB', @DensityThreshold = 0.00, @OpenThreshold = 0, @DeleteThreshold = 0, @Execute = N\n\u003e Version: Microsoft Azure SQL Data Warehouse - 10.0.29311.0 Sep 9 2025 04:02:32 Copyright (c) Microsoft Corporation\n\u003e Source: https://github.com/ProdataSQL/SynapseTools/tree/main/SqlPools/Maintenance\n\u003e Date and time: 2026-01-16 12:56:14\n\u003e [SchemaLab].[TableA]\n\u003e SqlCommand: ALTER INDEX ClusteredIndex_0c9429ed384a4e268945b46059844aa7 ON [SchemaLab].[TableA] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)\n\u003e (null)\n\u003e Date and time: 2026-01-16 12:56:14\n\u003e [SchemaLab].[TableB]\n\u003e SqlCommand: ALTER INDEX ClusteredIndex_bd57f7f732c84f6b8d17da2e280697ca ON [SchemaLab].[TableB] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)\n\u003e (null)\n\u003e Date and time: 2026-01-16 12:56:15\n\u003e [SchemaLab].[TableC]\n\u003e SqlCommand: ALTER INDEX ClusteredIndex_66416c0fa78f49eb8b2bb59d30974002 ON [SchemaLab].[TableC] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)\n\u003e (null)\n\nAs you can see, [SchemaLab].[TableB] wasn't excluded from the table list.\n\nI tried a change by replacing the following code:\n```sql\nParamTables as (\n    select t.object_id, s.name as [Schema], t.name as [Table]\n    FROM Param2 p\n    CROSS JOIN sys.tables t \n    INNER JOIN sys.schemas s on s.schema_id = t.schema_id\n    WHERE t.type='U'  and is_external=0 \n    and ( s.name like p.[schema] AND t.name like p.[Table] )\n    GROUP BY t.object_id , s.name, t.name\n    having max(p.Op) ='+'\n)\n```\nwith:\n```sql\nParamTables AS (\n    SELECT t.object_id, s.name AS [Schema], t.name AS [Table]\n    FROM sys.tables t\n    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id\n    WHERE t.type='U' AND is_external=0\n    AND EXISTS (\n        SELECT 1 FROM Param2 p\n        WHERE p.Op = '+' \n          AND ( (p.[Schema] = '%' OR s.name LIKE p.[Schema]) \n                AND (p.[Table] = '%' OR t.name LIKE p.[Table]) )\n    )\n    AND NOT EXISTS (\n        SELECT 1 FROM Param2 p\n        WHERE p.Op = '-' \n          AND ( (p.[Schema] = '%' OR s.name LIKE p.[Schema]) \n                AND (p.[Table] = '%' OR t.name LIKE p.[Table]) )\n    )\n)\n```\n\n**Output after the change:**\n\n\u003e Date and time: 2026-01-16 12:57:50\n\u003e Server: my-lab-workspace\n\u003e Version: 10.0.29311.0\n\u003e Edition: SQL Azure\n\u003e Parameters: @Tables= 'SchemaLab.%,-TableB', @DensityThreshold = 0.00, @OpenThreshold = 0, @DeleteThreshold = 0, @Execute = N\n\u003e Version: Microsoft Azure SQL Data Warehouse - 10.0.29311.0 Sep 9 2025 04:02:32 Copyright (c) Microsoft Corporation\n\u003e Source: https://github.com/ProdataSQL/SynapseTools/tree/main/SqlPools/Maintenance\n\u003e Date and time: 2026-01-16 12:57:54\n\u003e [SchemaLab].[TableA]\n\u003e SqlCommand: ALTER INDEX ClusteredIndex_0c9429ed384a4e268945b46059844aa7 ON [SchemaLab].[TableA] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)\n\u003e (null)\n\u003e Date and time: 2026-01-16 12:57:54\n\u003e [SchemaLab].[TableC]\n\u003e SqlCommand: ALTER INDEX ClusteredIndex_66416c0fa78f49eb8b2bb59d30974002 ON [SchemaLab].[TableC] REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON)\n\u003e (null)\n\nIt seems to work, but I would like a feedback from you!\n\nThanks in advance!","author":{"url":"https://github.com/VlachosChris","@type":"Person","name":"VlachosChris"},"datePublished":"2026-01-16T13:04:48.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":0},"url":"https://github.com/3/SynapseTools/issues/3"}