get_tables_by_pattern_sql, ilike doesn't work with SQL Server

[craiha]

Describe the bug

Azure SQL and SQL Server doesn't have the ilike SQL operator. The SQL code generated by get_tables_by_pattern_sql uses the ilike operator.
The bug was found while running the generate-source macro from the https://github.com/dbt-labs/dbt-codegen package.

Steps to reproduce

1. Create an Azure SQL database
2. Add one table to the database
3. Set up a dbt project connecting to the database
4. run the command: dbt run-operation generate_source --args '{schema_name: y,database_name: x}'

Expected results

Source YAML code

Actual results

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")

Screenshots and log output

============================== 13:42:18.998428 | 11325469-e83a-4fe8-8f85-afe9ab89d79a ==============================
�[0m13:42:18.998428 [info ] [MainThread]: Running with dbt=1.7.8
�[0m13:42:19.000386 [debug] [MainThread]: running dbt with arguments {'printer_width': '80', 'indirect_selection': 'eager', 'log_cache_events': 'False', 'write_json': 'True', 'partial_parse': 'True', 'cache_selected_only': 'False', 'warn_error': 'None', 'debug': 'False', 'fail_fast': 'False', 'log_path': 'C:\Users\me\Documents\Projects\project1\databas\datalager_dm\logs', 'profiles_dir': 'C:\Users\me\.dbt', 'version_check': 'True', 'use_colors': 'True', 'use_experimental_parser': 'False', 'no_print': 'None', 'quiet': 'False', 'log_format': 'default', 'invocation_command': 'dbt run-operation generate_source --args {schema_name: y,database_name: x}', 'warn_error_options': 'WarnErrorOptions(include=[], exclude=[])', 'introspect': 'True', 'target_path': 'None', 'static_parser': 'True', 'send_anonymous_usage_stats': 'True'}
�[0m13:42:19.552476 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'project_id', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE561C6FB0>]}
�[0m13:42:19.712082 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'adapter_info', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE5612F280>]}
�[0m13:42:19.713081 [info ] [MainThread]: Registered adapter: sqlserver=1.7.2
�[0m13:42:19.765939 [debug] [MainThread]: checksum: 67f0013ca5f0bd43af9a0873dd50792fde83ef69de63b71cacd0b4ac656c52e5, vars: {}, profile: , target: , version: 1.7.8
�[0m13:42:20.017233 [debug] [MainThread]: Partial parsing enabled: 0 files deleted, 0 files added, 0 files changed.
�[0m13:42:20.067133 [debug] [MainThread]: Partial parsing enabled, no changes found, skipping parsing
�[0m13:42:20.082060 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'load_project', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56E40130>]}
�[0m13:42:20.107991 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'resource_counts', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56CE1A50>]}
�[0m13:42:20.108989 [info ] [MainThread]: Found 12 models, 18 tests, 0 sources, 0 exposures, 0 metrics, 579 macros, 0 groups, 0 semantic models
�[0m13:42:20.110982 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'runnable_timing', 'label': '11325469-e83a-4fe8-8f85-afe9ab89d79a', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56CE3670>]}
�[0m13:42:20.112016 [debug] [MainThread]: Acquiring new sqlserver connection 'macro_generate_source'
�[0m13:42:20.113011 [debug] [MainThread]: On macro_generate_source: COMMIT
�[0m13:42:20.172850 [debug] [MainThread]: Using sqlserver connection "macro_generate_source"
�[0m13:42:20.173847 [debug] [MainThread]: On macro_generate_source: /* {"app": "dbt", "dbt_version": "1.7.8", "profile_name": "datalager_dm", "target_name": "dev", "connection_name": "macro_generate_source"} */

    select distinct
        table_schema as "table_schema",
        table_name as "table_name",
        
        case table_type
            when 'BASE TABLE' then 'table'
            when 'EXTERNAL TABLE' then 'external'
            when 'MATERIALIZED VIEW' then 'materializedview'
            else lower(table_type)
        end as "table_type"

    from x.information_schema.tables
    where table_schema ilike 'y'
    and table_name ilike '%'
    and table_name not ilike ''

�[0m13:42:20.175808 [debug] [MainThread]: Opening a new connection, currently in state init
�[0m13:42:20.176806 [debug] [MainThread]: fabric adapter: Using connection string: DRIVER={ODBC Driver 17 for SQL Server};SERVER=sql-project1-dev-datalager-weu.database.windows.net;Database=sqldb-project1-dev-datalager-weu;encrypt=Yes;TrustServerCertificate=No;APP=dbt-sqlserver/1.7.4;ConnectRetryCount=1
�[0m13:42:21.667370 [debug] [MainThread]: fabric adapter: Connected to db: sqldb-project1-dev-datalager-weu
�[0m13:42:21.702273 [debug] [MainThread]: fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
�[0m13:42:21.704270 [debug] [MainThread]: On macro_generate_source: ROLLBACK
�[0m13:42:21.706264 [debug] [MainThread]: On macro_generate_source: Close
�[0m13:42:21.707263 [debug] [MainThread]: fabric adapter: Error running SQL: macro generate_source
�[0m13:42:21.709255 [debug] [MainThread]: fabric adapter: Rolling back transaction.
�[0m13:42:21.711251 [error] [MainThread]: Encountered an error while running operation: Database Error
('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")
�[0m13:42:21.718232 [debug] [MainThread]: Traceback (most recent call last):
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 257, in exception_handler
yield
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 435, in add_query
cursor.execute(sql)
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Users\me\dbt-env\lib\site-packages\dbt\task\run_operation.py", line 58, in run
self._run_unsafe(package_name, macro_name)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\task\run_operation.py", line 43, in _run_unsafe
res = adapter.execute_macro(
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\base\impl.py", line 1112, in execute_macro
result = macro_function(**kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call
return self.call_macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro
return macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call
return self._invoke(arguments, autoescape)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
rv = self._func(*arguments)
File "", line 42, in macro
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\sandbox.py", line 393, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 298, in call
return __obj(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call
return self.call_macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro
return macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call
return self._invoke(arguments, autoescape)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
rv = self._func(*arguments)
File "", line 131, in macro
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\sandbox.py", line 393, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 298, in call
return __obj(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call
return self.call_macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro
return macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call
return self._invoke(arguments, autoescape)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
rv = self._func(*arguments)
File "", line 42, in macro
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\sandbox.py", line 393, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 298, in call
return __obj(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call
return self.call_macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro
return macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call
return self._invoke(arguments, autoescape)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
rv = self._func(*arguments)
File "", line 28, in macro
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\sandbox.py", line 393, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 298, in call
return __obj(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call
return self.call_macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro
return macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call
return self._invoke(arguments, autoescape)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
rv = self._func(*arguments)
File "", line 39, in macro
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\sandbox.py", line 393, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 298, in call
return __obj(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 330, in call
return self.call_macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\clients\jinja.py", line 257, in call_macro
return macro(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 763, in call
return self._invoke(arguments, autoescape)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 777, in _invoke
rv = self._func(*arguments)
File "", line 52, in macro
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\sandbox.py", line 393, in call
return __context.call(__obj, *args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\jinja2\runtime.py", line 298, in call
return __obj(*args, **kwargs)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\base\impl.py", line 310, in execute
return self.connections.execute(sql=sql, auto_begin=auto_begin, fetch=fetch, limit=limit)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 488, in execute
_, cursor = self.add_query(sql, auto_begin)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 417, in add_query
with self.exception_handler(sql):
File "C:\Program Files\WindowsApps\PythonSoftwareFoundation.Python.3.10_3.10.3056.0_x64__qbz5n2kfra8p0\lib\contextlib.py", line 153, in exit
self.gen.throw(typ, value, traceback)
File "C:\Users\me\dbt-env\lib\site-packages\dbt\adapters\fabric\fabric_connection_manager.py", line 268, in exception_handler
raise dbt.exceptions.DbtDatabaseError(str(e).strip()) from e
dbt.exceptions.DbtDatabaseError: Database Error
('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]An expression of non-boolean type specified in a context where a condition is expected, near 'ilike'. (4145) (SQLExecDirectW)")

�[0m13:42:21.724218 [debug] [MainThread]: Command dbt run-operation failed at 13:42:21.724218 after 2.82 seconds
�[0m13:42:21.726227 [debug] [MainThread]: Connection 'macro_generate_source' was properly closed.
�[0m13:42:21.727214 [debug] [MainThread]: Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE537B1BA0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56CE3850>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x000001DE56CE1B10>]}
�[0m13:42:21.729208 [debug] [MainThread]: Flushing usage events

System information

packages:

• package: dbt-labs/dbt_utils
  version: 1.1.1
• package: dbt-labs/codegen
  version: 0.12.1d

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• [x ] other (specify: Azure SQL/SQL Server)

The output of dbt --version:

<output goes here>

Additional context

Are you interested in contributing the fix?

[craiha]

Adding this code fixes the issue

{% macro sqlserver__get_tables_by_pattern_sql(schema_pattern, table_pattern, exclude='', database=target.database) %}

        select distinct
            table_schema as {{ adapter.quote('table_schema') }},
            table_name as {{ adapter.quote('table_name') }},
            {{ dbt_utils.get_table_types_sql() }}
        from [{{ database }}].information_schema.tables
        where table_schema like '{{ schema_pattern }}'
        and table_name like '{{ table_pattern }}'
        and table_name not like '{{ exclude }}'

{% endmacro %}

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[github-actions]

Although we are closing this issue as stale, it's not gone forever. Issues can be reopened if there is renewed community interest. Just add a comment to notify the maintainers.