Skip to content

Instantly share code, notes, and snippets.

@kissarat
Last active December 18, 2015 00:19
Show Gist options
  • Save kissarat/5695792 to your computer and use it in GitHub Desktop.
Save kissarat/5695792 to your computer and use it in GitHub Desktop.
MySQL database schema metainformation
-- USE mysql;
CREATE VIEW table_names AS
SELECT table_name as `name` FROM information_schema.tables
WHERE table_schema=database() AND table_type='BASE TABLE';
CREATE VIEW view_names AS
SELECT table_name as `name` FROM information_schema.tables
WHERE table_schema=database() AND table_type='SYSTEM VIEW';
CREATE VIEW column_names AS
SELECT
c.table_name as `table`,
c.column_name as `name`
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name
WHERE c.table_schema=database() AND t.table_type='BASE TABLE';
DROP VIEW IF EXISTS view_column_names;
CREATE VIEW view_column_names AS
SELECT
c.table_name as `view`,
c.column_name as `name`
FROM information_schema.columns c
JOIN information_schema.tables t ON c.table_name = t.table_name
WHERE c.table_schema=database() AND t.table_type='SYSTEM VIEW';
-- GRANT select ON table_names TO '%'@'%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment