Support SYNONYM as a table type for DatabaseMetadata methods

[dqmdev]

SQL Server JDBC 10.2 and older versions.

Unlike other database vendors, SQL Server JDBC driver does not deal with synonyms.

DatabaseMetadata interface methods return nothing.

DatabaseMetadata.getTableTypes does not include anything regarding synonyms

DatabaseMetadata.getTables does not return anything regarding objects (tables/views) synonym references.

...

This feature limitation has been raised several times over many years, as it impacts products relying on the JDBC API methods.

Adding hard coded SQL to navigate catalog tables as a workaround is not ideal, and not required with other vendors.

[tkyc]

Hi @dqmdev, we'll plan and triage this feature in our semester planning.

[dqmdev]

Is there any expectation that this will be addressed in 2023?

[lilgreenbird]

hi @dqmdev

At this point it seems unlikely. We do semester planning twice a year we have to allocate resources to those bugs and features which are higher priority. Please note that we have to consider severity of issue, impact, complexity, and the number of upvotes/requests from users. Please feel free to fill out our survey if you have features or bug fixes that you would like to see in the future as our product manager does look at these and take them into consideration.

[dqmdev]

@lilgreenbird

When SQL Server introduced synonyms, it did not account for those table types re the JDBC driver, as it did re ODBC driver etc.
Which was noted to Microsoft SQL Server team.

This forces applications to bypass using JDBC standard Metadata methods, and issue direct queries on the catalog tables. Ideally applications let the driver abstract how metadata is persisted within the catalog tables.

Unclear why SQL Server JDBC continues to have this gap. Is the current SQL Server product management team aware of the gap the driver?

Meanwhile other vendors who had or added SYNONYMS ensured their JDBC drivers functions (getTableTypes, getTables...).

[rileymcdowell]

I would really love to see this feature implemented.

I pulled down the code to see what it would take to implement this. It's deceptively complex. Let's suppose a driver config option was added that the user could set like enableFirstClassSynonyms=true in the connection string. Now we just need to modify the metadata queries to include tables, views, and synonyms if that flag is set. It can just stick with the default behavior if it's not set, so this is opt-in. This doesn't sound so bad. I'm ignoring the fact that there are other sp_* queries that need to be mitigated for this to work.

Option 1 - A simple UNION

A naive approach would be to UNION together the output of the sp_tables stored procedure with a query on sys.synonyms. In theory, I believe this would work.

SELECT * 
FROM (
  { call sp_tables }

  -- Add more rows to the result
  UNION 
  
  -- Select synonyms
  SELECT 
      s.name AS TABLE_QUALIFIER, 
      s.schema_id AS TABLE_OWNER, 
      s.name AS TABLE_NAME, 
      'SYNONYM' AS TABLE_TYPE, 
      NULL AS REMARKS
  FROM 
      sys.synonyms AS s
)
WHERE ...

However, MS SQL Server does not appear to support combining the output of a stored procedure with another query using a UNION, so this can't work.

Option 2 - OPENROWSET

The classic workaround for the stored procedure UNION limitation is OPENROWSET:

WITH TableResults AS (
 SELECT TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, TABLE_TYPE, REMARKS
    FROM OPENROWSET('SQLNCLI', 'Server=.;Trusted_Connection=yes;', 
                    'EXEC sp_tables')
), 
SynonymResults AS (
    SELECT 
        s.name AS TABLE_NAME,
        s.schema_id AS TABLE_OWNER,
        s.base_object_name AS TABLE_QUALIFIER,
        'SYNONYM' AS TABLE_TYPE,
        NULL AS REMARKS
    FROM sys.synonyms s
)
SELECT * FROM TableResults
UNION ALL 
SELECT * FROM SynonymResults
WHERE ...

Unfortunately, most default MS SQL Server configurations don't allow OPENROWSET due to security concerns. Since this driver needs to be robust to virtually any server configuration, this is also not an option.

Option 3 - Recreate sp_tables

We could bundle a modified version of sp_tables that includes sys.synonyms within this driver and run that instead. This actually has a pretty high likelihood of working, but it creates a bunch of other issues.

1. sp_tables ships with MS SQL Server. Presumably different version of MS SQL Server have different versions of sp_tables. Therefore, this driver would need to detect the sever version and provide the correct implementation of sp_tables to the sever. This would be a maintenance headache and some unpleasantly duplicated code with all those slightly different copies of sp_tables.
2. There's more than just sp_tables to worry about. A cursory glance suggests we need to properly handle sp_tables, sp_columns, sp_columns_100, sp_column_privileges, sp_special_columns, sp_special_columns_100, sp_fkeys, sp_statistics, sp_statistics_100, sp_table_privileges, and sp_pkeys. This seems like the maintenance overhead raises to the point of an impossibility for this driver.
3. This driver is already handling pre and post SS10 (Katmai) stored proc syntax. Adding another layer on top would further increase complexity of what is currently an elegant implementation of SQLServerDatabaseMetaData.CallableHandles. It's so clean that both pre-10 and post-10 metadata queries for every stored proc fit on a single line of source code. I think any fix here is going to ruin that elegance.

Option 4 - Use information_schema.tables

Pretty much all of these problems are caused by stored procedures, so what if we abandon stored procedures entirely? information_schema.tables and sys.synonyms seem like good candidates to replace the sp_tables functionality.

-- Query to get INFORMATION_SCHEMA tables
SELECT * 
FROM ( 
  SELECT 
    TABLE_CATALOG AS TABLE_QUALIFIER, 
    TABLE_SCHEMA AS TABLE_OWNER, 
    TABLE_NAME AS TABLE_NAME,
    CASE 
        WHEN TABLE_TYPE = 'BASE TABLE' THEN 'TABLE'
        ELSE TABLE_TYPE
    END AS TABLE_TYPE,
    NULL AS REMARKS
  FROM 
    INFORMATION_SCHEMA.TABLES
    
  UNION

  SELECT 
    DB_NAME() AS TABLE_QUALIFIER, 
    SCHEMA_NAME(s.schema_id) AS TABLE_OWNER, 
    s.name AS TABLE_NAME, 
    'SYNONYM' AS TABLE_TYPE, 
    NULL AS REMARKS
  FROM 
    sys.synonyms AS s
)
WHERE ...

Unfortunately, information_schema.tables does not include system tables or system views, meaning this would be a breaking change. If you try to add those on, you soon find yourself back at Option 3 rebuilding the sp_tables function's full functionality. This is a shame because the SQL itself with this approach is unsurprising.

Option 5 - Concatenating SQLServerResultSets

One option I didn't explore in detail is modifying SQLServerResultSet such that someone can concatenate two of them together. If this were possible, the UNION operation could be handled in the JDBC driver instead of at the database layer. This would cause each call to getTables to run 2 metadata queries instead of just one - one for sp_tables and again for sys.synonyms. This is just working around the UNION limitation by pushing that complexity into a less optimal place.

Summary

Having looked at what it would take to implement this, I see why this feature has been hanging around for two and a half years.

Question for the Maintainers: Do you have a strong opinion about how best to implement this?

Background

I work on a team that makes heavy use of Trino, and Trino uses this JDBC driver to communicate with MS SQL Server databases. Sometimes my team is asked to make use of synonyms in SQL Server. At the present time, Trino is unable to query those synonyms because they don't show up in the metadata queries Trino runs to get lists of tables and columns. It's possible to pass direct text strings that select out of the synonyms. The text strings execute as SQL statements in the source MS SQL DB and return results back. This is not ideal though - if we enable direct query execution, the results bypass Trino's security model for controlling table access or column masking for PII. That leaves us with two not very good choices. (1) We provide all-or-nothing access to the source system or (2) we don't make use of the synonyms that the database owners asked us to use.