CODEWITHSUNDEEP
mysqlsqldatabase
Jorjon
Jorjon

Reputation: 5434

Detect if database and table exist on the same query

I need to be able to detect if the database and/or the table exists on a single query, to act accordingly. I have this fugly query working:

SELECT * FROM

(SELECT COUNT(*) AS `database`
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMATA.SCHEMA_NAME="database_name") AS foo,

(SELECT COUNT(*) AS `table`
FROM INFORMATION_SCHEMA.TABLES
WHERE table_schema = "database_name"
AND table_name = "table_name") AS bar

This query returns:

database    table
1           0

But... Maybe there is a better method out there.

Upvotes: 0

Views: 159

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26353

This will also work, though I'm not sure it's any prettier :)

SELECT
  MAX(CASE
      WHEN table_schema = 'database_name' THEN 1
      ELSE 0
      END) AS `database`
, MAX(CASE
      WHEN table_schema = 'database_name' AND table_name = 'table' THEN 1
      ELSE 0
      END) AS `table`
FROM information_schema.tables;

Upvotes: 0

PinnyM
PinnyM

Reputation: 35531

Use a LEFT JOIN:

SELECT schemata.schema_name AS `database_name`, tables.table_name
FROM INFORMATION_SCHEMA.SCHEMATA schemata
LEFT JOIN INFORMATION_SCHEMA.TABLES tables 
  ON schemata.schema_name = tables.table_schema
  AND tables.table_name = "table_name"
WHERE SCHEMATA.SCHEMA_NAME="database_name"

Upvotes: 1

Related Questions