Getting information from the Babelfish system catalog - Amazon Aurora
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Getting information from the Babelfish system catalog

You can obtain information about the database objects that are stored in your Babelfish cluster by querying many of the same system views as used in SQL Server. Each new release of Babelfish adds support for more system views. For a list of available views currently available, see the SQL Server system catalog views table.

These system views provide information from the system catalog (sys.schemas). In the case of Babelfish, these views contain both SQL Server and PostgreSQL system schemas. To query Babelfish for system catalog information, you can use the TDS port or the PostgreSQL port, as shown in the following examples.

  • Query the T-SQL port using sqlcmd or another SQL Server client.

    1> SELECT * FROM sys.schemas 2> GO

    This query returns SQL Server and Aurora PostgreSQL system schemas, as shown in the following.

    name --------------------------------------------------------- demographic_dbo public sys master_dbo tempdb_dbo ...
  • Query the PostgreSQL port using psql or pgAdmin. This example uses the psql list schemas metacommand (\dn):

    babelfish_db=> \dn

    The query returns the same result set as that returned by sqlcmd on the T-SQL port.

    List of schemas Name ------------------------------ demographic_dbo public sys master_dbo tempdb_dbo ...

SQL Server system catalogs available in Babelfish

In the following table you can find the SQL Server views currently implemented in Babelfish. For more information about the system catalogs in SQL Server, see System Catalog Views (Transact-SQL) in Microsoft documentation.

View name Description or Babelfish limitation (if any)


All columns in all tables and views


All objects in all schemas


The union of sys.sql_modules and sys.system_sql_modules


All views in all schemas


All columns in user-defined tables and views


Babelfish support limited to a single read-only configuration.


Contains a row for each data space. This can be a filegroup, partition scheme, or FILESTREAM data filegroup.


A per-database view that contains one row for each file of a database as stored in the database itself.


For information, see sys.database_mirroring in Microsoft Transact-SQL documentation.


For information, see sys.database_principals in Microsoft Transact-SQL documentation.


For information, see sys.database_role_members in Microsoft Transact-SQL documentation.


All databases in all schemas


For information, see sys.dm_exec_connections in Microsoft Transact-SQL documentation.


For information, see sys.dm_exec_sessions in Microsoft Transact-SQL documentation.


For information, see sys.dm_hadr_database_replica_states in Microsoft Transact-SQL documentation.


For information, see sys.dm_os_host_info in Microsoft Transact-SQL documentation.


For information, see sys.endpoints in Microsoft Transact-SQL documentation.


For information, see sys.indexes in Microsoft Transact-SQL documentation.


For information, see sys.languages in Microsoft Transact-SQL documentation.


All schemas


All logins and roles


For information, see sys.sql_modules in Microsoft Transact-SQL documentation.


Babelfish support limited to a single read-only configuration.


Babelfish support limited to a single read-only configuration.


For information, see sys.sysprocesses in Microsoft Transact-SQL documentation.


For information, see sys.system_sql_modules in Microsoft Transact-SQL documentation.


For information, see sys.table_types in Microsoft Transact-SQL documentation.


All tables in a schema


For information, see sys.xml_schema_collections in Microsoft Transact-SQL documentation.

PostgreSQL implements system catalogs that are similar to the SQL Server object catalog views. For a complete list of system catalogs, see System Catalogs in the PostgreSQL documentation.

DDL exports supported by Babelfish

From Babelfish 2.4.0 and 3.1.0 versions, Babelfish supports DDL exports using various tools. For example, you can use this functionality from SQL Server Management Studio (SSMS) to generate the data definition scripts for various objects in a Babelfish for Aurora PostgreSQL database. You can then use the generated DDL commands in this script to create the same objects in another Babelfish for Aurora PostgreSQL or SQL Server database.

Babelfish supports DDL exports for the following objects in the specified versions.

List of objects 2.4.0 3.1.0
User tables YesYes
Primary keys YesYes
Foreign keys YesYes
Unique constraints YesYes
Indexes YesYes
Check constraints YesYes
Views YesYes
Stored procedures YesYes
User-defined functions YesYes
Table-valued functions YesYes
Triggers YesYes
User Defined Datatypes NoNo
User Defined Table Types NoNo
Users NoNo
Logins NoNo
Sequences NoNo
Roles NoNo

Limitations with the exported DDLs

  • Use escape hatches before recreating the objects with the exported DDLs – Babelfish doesn't support all the commands in the exported DDL script. Use escape hatches to avoid errors caused when recreating the objects from the DDL commands in Babelfish. For more information on escape hatches, see Managing Babelfish error handling with escape hatches

  • Objects containing CHECK constraints with explicit COLLATE clauses – The scripts with these objects generated from a SQL Server database have different but equivalent collations as in the Babelfish database. For example, a few collations, such as sql_latin1_general_cp1_cs_as, sql_latin1_general_cp1251_cs_as, and latin1_general_cs_as are generated as latin1_general_cs_as, which is the closest Windows collation.