Improper lookup of schema names in SQL Server, in some cases

Hi,

I've noticed previously that HeidiSQL doesn't display schema names properly for SQL Server (I'm using 2008 R2). However, I think I've narrowed down the problem quite a bit, and the culprit seems to be the SCHEMA_NAME function.

When HeidiSQL probes the system for tables and schemas, it uses the following query: (my example database is called "Surveillance")

SELECT *, SCHEMA_NAME("schema_id") AS 'schema' 
    FROM "Surveillance"."sys"."objects" 
    WHERE "type" IN ('P', 'U', 'V', 'TR', 'FN', 'TF', 'IF');

However, the SCHEMA_NAME function is dependent on the current connection, which fails to resolve the schema_id values properly if you haven't currently selected the database you're expanding. If so, schema_id values are off - and worst case, could look up the wrong schema name from a different database.

If you modify the query to the following instead:

SELECT o.*, s.name AS 'schema' 
    FROM "Surveillance"."sys"."objects" o
    JOIN "Surveillance"."sys"."schemas" s ON s.schema_id = o.schema_id
    WHERE o."type" IN ('P', 'U', 'V', 'TR', 'FN', 'TF', 'IF');

It then uses the correct database to look up schema names, and the correct values will be returned. The sys.schemas table seems to be available in SQL Server 2008 and forward.

The proper behavior can be verified by selecting the database first in the connection tree, and then expanding the node, causing HeidiSQL to use the right database when lookup up metadata.

I've also noticed that once HeidiSQL actually loads the correct schema values, querying and editing seems to work as expected - although I made only a very shallow attempt at verifying the behavior.

Thanks,

Matt