Getting information from the Babelfish system catalog - Amazon Aurora
SQL Server system catalogs available in Babelfish
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)

sys.all_columns

All columns in all tables and views

sys.all_objects

All objects in all schemas

sys.all_sql_modules

The union of sys.sql_modules and sys.system_sql_modules

sys.all_views

All views in all schemas

sys.columns

All columns in user-defined tables and views

sys.configurations

Babelfish support limited to a single read-only configuration.

sys.data_spaces

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

sys.database_files

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

sys.database_mirroring

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

sys.database_principals

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

sys.database_role_members

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

sys.databases

All databases in all schemas

sys.dm_exec_connections

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

sys.dm_exec_sessions

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

sys.dm_hadr_database_replica_states

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

sys.dm_os_host_info

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

sys.endpoints

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

sys.indexes

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

sys.languages

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

sys.schemas

All schemas

sys.server_principals

All logins and roles

sys.sql_modules

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

sys.sysconfigures

Babelfish support limited to a single read-only configuration.

sys.syscurconfigs

Babelfish support limited to a single read-only configuration.

sys.sysprocesses

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

sys.system_sql_modules

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

sys.table_types

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

sys.tables

All tables in a schema

sys.xml_schema_collections

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.