ZF-6665: Metadata for tables from a different catalog in MSSQL
MSSQL servers can handle queries against multiple databases. Also, MSSQL works with schemas, so the tables could be organized in the following manner:
-Database Foo -----Schema Bar ---------Table Baz -Database Sample -----Schema Zend ---------Table Framework
Using any database, presuming you have the appropriate permissions, you can do the following query:
SELECT baz.column FROM foo.bar.baz INNER JOIN sample.zend.framework ON baz.some_key = framework.some_key
Note that it is not possible to do the following:
sp_columns 'foo.bar.baz' sp_columns 'sample.zend.framework'
In order to obtain a list of columns from a table, you have to be on that table original database, like this:
USE foo; sp_columns 'baz', 'bar'; USE sample; sp_columns 'framework', 'zend';
I'm currently using workarounds in my applications to circumvent this limitation:
$db->exec('USE foo;'); //Changes current database $barBazTable->fetchAll(); //Can normally fetch metadata under the right context $db->exec('USE sample;'); $zendFrameworkTable->fetchAll();
My current approach doesn't work with tables with the same name in different schemas (sales.people, employees.people).
My Zend_Db_Table objects also had they "$_schema" properties with the whole schema identifier, including database name ('foo.bar', 'sample.zend', etc..). These works fine.
I think the "USE" statements should be called while describing the table (sp_columns, sp_pkeys, etc...) inside Zend_Db_Adapter_Pdo_Mssql, and then restored to the original database (specified on connection).