Can you write a query to determine whether a table is permanent or temporary?

The exact query depends on the database system. Below are examples for MySQL, PostgreSQL, and SQL Server.

MySQL – Temporary tables are stored in the information_schema with the TABLE_TYPE column set to 'BASE TABLE' but the TABLE_NAME starts with a # for session‑temporary tables. You can check the TABLE_SCHEMA and TABLE_NAME patterns:

SELECT TABLE_NAME,
       IF(TABLE_NAME LIKE '#%', 'Temporary', 'Permanent') AS table_type
FROM   information_schema.tables
WHERE  TABLE_SCHEMA = DATABASE();

PostgreSQL – Temporary tables are created in the pg_temp schema. Query pg_class and pg_namespace:

SELECT c.relname,
       CASE WHEN n.nspname = 'pg_temp_1' THEN 'Temporary' ELSE 'Permanent' END AS table_type
FROM   pg_class c
JOIN   pg_namespace n ON c.relnamespace = n.oid
WHERE  c.relkind = 'r';

SQL Server – Temporary tables have names that start with # or ##. Use sys.tables and sys.schemas:

SELECT t.name,
       CASE WHEN t.name LIKE '#%' THEN 'Temporary' ELSE 'Permanent' END AS table_type
FROM   sys.tables t
WHERE  t.is_ms_shipped = 0;

These queries list each table in the current database and label it as Permanent or Temporary based on naming conventions or system catalog metadata. Adjust the schema or database name filters as needed for your environment.