Unused foreign servers

Back to Catalog
Goal Find foreign servers that do not have any associated foreign tables.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability High (Few or no false-positive results)
License MIT License
Fixing Suggestion Drop the foreign server or start to use it.
Data Source INFORMATION_SCHEMA only
SQL Query 
SELECT foreign_data_wrapper_name, foreign_server_name, foreign_server_type
FROM INFORMATION_SCHEMA.foreign_servers AS fs
WHERE NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.foreign_tables AS ft
WHERE fs.foreign_server_name=ft.foreign_server_name)
ORDER BY foreign_data_wrapper_name, foreign_server_name;

SQL statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
SELECT format('DROP SERVER %1$I;', foreign_server_name) AS statements
FROM INFORMATION_SCHEMA.foreign_servers AS fs
WHERE NOT EXISTS (SELECT 1
FROM INFORMATION_SCHEMA.foreign_tables AS ft
WHERE fs.foreign_server_name=ft.foreign_server_name)
ORDER BY foreign_data_wrapper_name, foreign_server_name;
Drop the foreign server.
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
Distributed databaseQueries of this category provide information about the foreign table mechanism.
Unused implementation elementsQueries of this catergory provide information about the database objects that are not used.