Switching the current database

Below are examples of how to determine the current/active database and schema and switch between different databases and schemas:

CLI

-- check your current database
SELECT current_database();
dbname


-- list all tables in the current database
SHOW TABLES;
table1
table2


-- list all databases
SHOW DATABASES;
dbname
dbname2

-- switch to database named 'dbname2'
USE dbname2;

-- verify that you've successfully switched databases
SELECT current_database();
dbname2 

-- check your current schema
SELECT current_schema();
main

-- list all schemas across all databases
SELECT * FROM duckdb_schemas();

oid	database_name	database_oid	schema_name	internal	sql
986	my_db	989	information_schema	true	NULL
974	my_db	989	main	false	NULL
972	my_db	989	my_schema	false	NULL
987	my_db	989	pg_catalog	true	NULL
1508	system	0	information_schema	true	NULL
0	system	0	main	true	NULL
1509	system	0	pg_catalog	true	NULL
1510	temp	1453	information_schema	true	NULL
1454	temp	1453	main	true	NULL
1511	temp	1453	pg_catalog	true	NULL

-- switch to schema my_schema within the same database
USE my_schema;

-- verify that you've successfully switched schemas
SELECT current_schema();
my_schema

-- switch to database my_db and schema main 
USE my_db.my_schema

-- verify that both the database and schema have been changed
SELECT current_database(), current_schema();

current_database()	current_schema()
my_db	main