Rebuild all XML indexes in SQL Server 2005 using T-SQL

Question

How can I use T-SQL to force a rebuild / reindex of all XML indexes in a SQL Server 2005 database?

I only have Management Studio Express locally so I don't have the luxury of any UI options such as the "Rebuild Indexes" dialog that I know exists in SQL Server 2008.

Answer 1

On the SQL 2008 server, there's a Script Action menu option on the dialog, it will show you what it is doing, it will be T-SQL commands like:

ALTER INDEX [indexname] ON [dbo].[tablename] REBUILD PARTITION = ALL ...

Answer 2

To recreate then you can just drop them and then "CREATE Primary XML Index idx_1 ON OrdersXML (xOrders)"

The only thing is that you will have to know if your other indexes are Path, Value, or Property. But if rebuilding them is all you need, you should be able to pull that out of the management tables. If I can find the syntax will add it in the comments.