How to delete hypothetical index _WA_SYS_%

[youdao]

I have a thirt-party program that can compare two database schema for a commercial ERM software. However, those hypothetical index starting with _WA_Sys_ are causing problem for the program. I was able to delete those index one by one and table by table. Is there a better way to delete all the hypothetical index within a database.

Thanks for any advice

Youdao

 

[tlbroadbent]

The indexes starting with _WA are not "hypothetical indexes" but statistics for non-indexed columns. The stats are used by SQL Server for query optimization.

You can use DROP STATISTICS to remove the auto created statistics. You can turn off the auto create statistics option on the database if you don't want the stats.

Make sure you understand the negative performance impact dropping statistics might have. Statistics occupy a single column in the sysindexes table and usually take up less than one page. There is really no reason to get rid of them! Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[youdao]

Thanks, Terry. I only want to get rid of them to allow the thirty party program to run. That is just one time deal.

Youdao