Added Schema Filter to "drop_all_indexes()"

[tkirschke]

The macro "drop_all_indexes()" searches in sys.indexes for all indexes that are applied on a specific table. But this table is only identified by the name of the table.

This leads to the following problem: When having the same table (therefore the same name) in different schemas, the macro will drop the indexes on all the tables with this name, no matter the schema. When working with the "generate_schema_name" macro to create a schema per developer, each of them will also drop all indexes of the tables of other developers.

To solve this, a simple additional WHERE condition can be used, which uses target.schema as the value of the schema.

The drop_all_indexes() macro before:

{% macro drop_all_indexes_on_table() -%}
{# Altered from https://stackoverflow.com/q/1344401/10415173 #}
{# and https://stackoverflow.com/a/33785833/10415173         #}

{{ drop_pk_constraints() }}

{{ log("Dropping remaining indexes...") }}

declare @drop_remaining_indexes_last nvarchar(max);
select @drop_remaining_indexes_last = (
    select 'IF INDEXPROPERTY(' + CONVERT(VARCHAR(MAX), sys.tables.[object_id]) + ', ''' + sys.indexes.[name] + ''', ''IndexId'') IS NOT NULL DROP INDEX [' + sys.indexes.[name] + '] ON ' + '[' + SCHEMA_NAME(sys.tables.[schema_id]) + '].[' + OBJECT_NAME(sys.tables.[object_id]) + ']; '
    from sys.indexes {{ information_schema_hints() }}
    inner join sys.tables {{ information_schema_hints() }}
    on sys.indexes.object_id = sys.tables.object_id
    where sys.indexes.[name] is not null
      and sys.tables.[name] = '{{ this.table }}'
    for xml path('')
); exec sp_executesql @drop_remaining_indexes_last;

{%- endmacro %}

With the additional WHERE condition:

{% macro drop_all_indexes_on_table() -%}
{# Altered from https://stackoverflow.com/q/1344401/10415173 #}
{# and https://stackoverflow.com/a/33785833/10415173         #}

{{ drop_pk_constraints() }}

{{ log("Dropping remaining indexes...") }}

declare @drop_remaining_indexes_last nvarchar(max);
select @drop_remaining_indexes_last = (
    select 'IF INDEXPROPERTY(' + CONVERT(VARCHAR(MAX), sys.tables.[object_id]) + ', ''' + sys.indexes.[name] + ''', ''IndexId'') IS NOT NULL DROP INDEX [' + sys.indexes.[name] + '] ON ' + '[' + SCHEMA_NAME(sys.tables.[schema_id]) + '].[' + OBJECT_NAME(sys.tables.[object_id]) + ']; '
    from sys.indexes {{ information_schema_hints() }}
    inner join sys.tables {{ information_schema_hints() }}
    on sys.indexes.object_id = sys.tables.object_id
    where sys.indexes.[name] is not null
      and SCHEMA_NAME(sys.tables.schema_id) = '{{ this.schema }}'
      and sys.tables.[name] = '{{ this.table }}'
    for xml path('')
); exec sp_executesql @drop_remaining_indexes_last;

{%- endmacro %}