USP_DropXMLIndexes

Removes all XML Indexes from a table, takes schema name and table name as parameters.
e.g. EXEC USP_DropXMLIndexes 'Person', 'Contact'

CREATE PROCEDURE USP_DropXMLIndexes
              @schemaname VARCHAR(128),
              @tablename  VARCHAR(128)
             
AS
-- USP_DropXMLIndexes by sql solace
 DECLARE  @sqlstring NVARCHAR(500)
                    
 WHILE EXISTS (SELECT *
               FROM   sys.internal_tables AS IT
                      JOIN sys.tables AS T
                        ON IT.PARENT_ID = T.OBJECT_ID
                      JOIN sys.schemas AS S
                        ON T.SCHEMA_ID = S.SCHEMA_ID
                      JOIN sys.xml_indexes AS XI
                        ON IT.PARENT_ID = XI.OBJECT_ID
                       AND IT.PARENT_MINOR_ID = XI.INDEX_ID
               WHERE  S.NAME = @schemaname
                      AND T.NAME = @tablename)
   BEGIN
     SELECT @sqlstring = 'DROP INDEX [' + XI.NAME + '] ON [' + S.NAME + '].[' + T.NAME + '] '
     FROM   sys.internal_tables AS IT
            JOIN sys.tables AS T
              ON IT.PARENT_ID = T.OBJECT_ID
            JOIN sys.schemas AS S
              ON T.SCHEMA_ID = S.SCHEMA_ID
          JOIN sys.xml_indexes AS XI
              ON IT.PARENT_ID = XI.OBJECT_ID
                 AND IT.PARENT_MINOR_ID = XI.INDEX_ID
     WHERE  S.NAME = @schemaname
            AND T.NAME = @tablename
                        
     PRINT @sqlstring
    
     EXECUTE sp_executesql @sqlstring
   END

GO