Can I access data from tables that are not part of the Babelfish_DB?
I have tried
USE AnotherDatabase
SELECT * FROM sys.tables -- Returns all DB's defined in the Babelfish_DB scope?
SELECT * FROM TableInBabelFishDBScope -- works fine
SELECT * FROM MyExistingTableInAnotherDatabase --errors out, can't be found.
It appears that BabelFish_DB represents a SQL Server instance because I can see all the meta data tables in there. Can I create and/or access tables in other postgresql databases, at the moment I get an error when I try (could be human error/lack of understanding). If I am supposed to use the Babelfish_DB as the container for all babel-fish enabled DB's and commands could someone post documentation on this aspect?
In essence, only artifacts created via Babelfish are assessable to Babelfish and Databases outside of the Babelfish_DB cannot be accessed via Babelfish.
All (T-SQL) created databases reside inside the BableFish_DB. Artifacts added outside of BF are not registered with Babelfish. For example, if a database name Database1 was created via Babelfish containing a table named Table1 and later a Table named Table2 was created with, for example, PgAdmin. The following (TSQL) command over Babelfish would fail SELECT * FROM Table2 relation "table2" does not exist
In multiple-instance mode, multiple databases are abstracted away inside the BF databases via schema naming semantics -> <DatabaseName>_<SchemaName>.
In single-instance mode, there is only support for one database thus the schema is simply -> <SchemaName>.
If anyone finds inaccuracies in any of the above, please feel free to correct me.
USE TestDB1
SELECT * FROM Table1 --No schema created - Using Shema TestDb1_dbo
USE TestDB2
SELECT * FROM TestSchema.Table1 -- Schema TestSchema created against TestDB2 - using TestDB2_TestSchema
USE TestDB3
SELECT * FROM TestSchema.Table1 -- Schema TestSchema created against TestDB3 - using TestDB3_TestSchema