WordPress Database Information

Posted on September 14, 2015February 12, 2016 By David Kittell

I plan to work more on this as it is rather nasty but as a work in progress notepad if you will this is the spot for now.

-- Not a pretty approach but this will help you get a listing of all active plugins
DROP TABLE

IF EXISTS wp_active_plugins;
	CREATE TEMPORARY TABLE wp_active_plugins (plugin_file VARCHAR(150));

ALTER TABLE wp_active_plugins ADD UNIQUE INDEX ix_plugin (plugin_file);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 2), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 4), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 6), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 8), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 10), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 12), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 14), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 16), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 18), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 20), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 22), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 24), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 26), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 28), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 30), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 32), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 34), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 36), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 38), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 40), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 42), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 44), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 46), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 48), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 50), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 52), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 54), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 56), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 58), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 60), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 62), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 64), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 66), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 68), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 70), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 72), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 74), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 76), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 78), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 80), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 82), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 84), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 86), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 88), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 90), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 92), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 94), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 96), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 98), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 100), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 102), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 104), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 106), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 108), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 110), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 112), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 114), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 116), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 118), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 120), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 122), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 124), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 126), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 128), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 130), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 132), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 134), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 136), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 138), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 140), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 142), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 144), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 146), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 148), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 150), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 152), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 154), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 156), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 158), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 160), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 162), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 164), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 166), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 168), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 170), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 172), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 174), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 176), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 178), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 180), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 182), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 184), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 186), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 188), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 190), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 192), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 194), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 196), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 198), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

INSERT IGNORE
INTO wp_active_plugins
VALUES (
	(
		SELECT REPLACE(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(option_value, ';', 200), ':', - 1), """", ""), ";}", "")
		FROM wp_options
		WHERE option_name = 'active_plugins'
		)
	);

SELECT *
FROM wp_active_plugins
ORDER BY plugin_file;

-- Get Active Theme
DROP VIEW IF EXISTS WP_ActiveTheme;
CREATE VIEW WP_ActiveTheme
	AS
	SELECT wpi.NAME AS 'Site',
		wpi.Environment AS 'Environment',
		wpi.URL AS 'URL',
		option_value AS 'Active Theme',
		(
			SELECT DATABASE ()
			) AS SiteDB
	FROM wp_options
	LEFT OUTER JOIN WP_Info wpi ON (
			SELECT DATABASE ()
			) = wpi.WP_Database
	WHERE option_name = 'template';

-- Get Site Information with User Roles
DROP VIEW IF EXISTS wp_siteroles;
DROP VIEW IF EXISTS WP_SiteRoles;
CREATE VIEW WP_SiteRoles
	AS
	SELECT NAME,
		wpi.Environment,
		URL,
		wpi.wp_version,
		wpi.wp_Status,
		GROUP_CONCAT(DISTINCT wpu.Username) AS Administrators,
		GROUP_CONCAT(DISTINCT wpu2.Username) AS Editors,
		GROUP_CONCAT(DISTINCT wpu1.Username) AS Subscribers,
		GROUP_CONCAT(DISTINCT wpu3.Username) AS None
	FROM WP_Info wpi
	LEFT OUTER JOIN WP_UserList wpu ON wpu.wp_Database = wpi.wp_Database
		AND wpu.Capabilities = 'Administrator'
	LEFT OUTER JOIN WP_UserList wpu1 ON wpu1.wp_Database = wpi.wp_Database
		AND wpu1.Capabilities = 'Subscriber'
	LEFT OUTER JOIN WP_UserList wpu2 ON wpu2.wp_Database = wpi.wp_Database
		AND wpu2.Capabilities = 'Editor'
	LEFT OUTER JOIN WP_UserList wpu3 ON wpu3.wp_Database = wpi.wp_Database
		AND wpu3.Capabilities = 'None'
	GROUP BY wpi.wp_Database;

-- Run multiple procedures on all databases

SELECT DISTINCT CONCAT (
        'use ',
        table_schema,
        ';select* from WP_Info;select* from WP_UserList;select* from WP_SiteRoles;'
        ) AS Script
FROM `information_schema`.`columns`
WHERE table_schema NOT IN (
        'information_schema',
        'mysql',
        'test'
        )

-- Get WordPress Users / User Role(s)

DROP VIEW IF EXISTS wp_userList;
DROP VIEW IF EXISTS WP_UserList;
CREATE VIEW WP_UserList
	AS
	SELECT wpi.NAME AS 'Site',
		wpi.Environment AS Environment,
		wpu.user_login AS 'Username',
		wpu.user_email AS Email,
		wpu.user_registered AS Created,
		(
			SELECT meta_value
			FROM wp_usermeta wpum
			WHERE user_id = wpu.id
				AND meta_key = 'first_name'
			) AS 'First_Name',
		(
			SELECT meta_value
			FROM wp_usermeta wpum
			WHERE user_id = wpu.id
				AND meta_key = 'last_name'
			) AS 'Last_Name',
		(
			SELECT CASE 
					WHEN meta_value LIKE 'a:1:{s:13:"administrator"%'
						THEN 'Administrator'
					WHEN meta_value LIKE 'a:1:{s:10:"subscriber"%'
						THEN 'Subscriber'
					WHEN meta_value LIKE 'a:1:{s:6:"editor"%'
						THEN 'Editor'
					WHEN meta_value = 'a:0:{}'
						THEN 'None'
					ELSE meta_value
					END
			FROM wp_usermeta wpum
			WHERE user_id = wpu.id
				AND meta_key = 'wp_capabilities'
			) AS 'Capabilities',
		(
			SELECT DATABASE ()
			) AS 'WP_Database'
	FROM wp_users wpu
	INNER JOIN WP_Info wpi ON (
			SELECT DATABASE ()
			) = wpi.WP_Database
	ORDER BY username;

-- Get Blog Name, URL, WordPress version, version status

DROP VIEW IF EXISTS WP_Info;
    CREATE VIEW WP_Info
    AS
    SELECT DISTINCT (
            SELECT CASE
                    WHEN option_name = 'blogname'
                        THEN option_value
                    END
            FROM wp_options
            WHERE option_name = 'blogname'
            ) AS 'Name',
        (
            SELECT CASE
                    WHEN option_name = 'siteurl'
                        AND option_value LIKE '%staging.%'
                        THEN 'Stage'
                    WHEN option_name = 'siteurl'
                        AND option_value LIKE '%/test/%'
                        THEN 'Stage'
                    WHEN option_name = 'siteurl'
                        AND option_value LIKE '%www.%'
                        THEN 'Production'
                    END
            FROM wp_options
            WHERE option_name = 'siteurl'
            ) AS 'Environment',
        (
            SELECT CASE
                    WHEN option_name = 'siteurl'
                        THEN option_value
                    END
            FROM wp_options
            WHERE option_name = 'siteurl'
            ) AS 'URL',
        (
            SELECT CASE
                    WHEN option_name = 'db_version'
                        AND option_value = '2540'
                        THEN 'Version: 1.2.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '2541'
                        THEN 'Version: 1.5 - 1.5.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '3441'
                        THEN 'Version: 2 - 2.0.11'
                    WHEN option_name = 'db_version'
                        AND option_value = '4772'
                        THEN 'Version: 2.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '4773'
                        THEN 'Version: 2.1.1 - 2.1.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '5183'
                        THEN 'Version: 2.2 - 2.2.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '6124'
                        THEN 'Version: 2.3 - 2.3.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '7558'
                        THEN 'Version: 2.5'
                    WHEN option_name = 'db_version'
                        AND option_value = '7796'
                        THEN 'Version: 2.5.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '8201'
                        THEN 'Version: 2.6'
                    WHEN option_name = 'db_version'
                        AND option_value = '8204'
                        THEN 'Version: 2.6.1 - 2.6.5'
                    WHEN option_name = 'db_version'
                        AND option_value = '9872'
                        THEN 'Version: 2.7 - 2.7.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '11548'
                        THEN 'Version: 2.8 - 2.8.6'
                    WHEN option_name = 'db_version'
                        AND option_value = '12329'
                        THEN 'Version: 2.9 - 2.9.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '15260'
                        THEN 'Version: 3'
                    WHEN option_name = 'db_version'
                        AND option_value = '15477'
                        THEN 'Version: 3.0.1 - 3.0.6'
                    WHEN option_name = 'db_version'
                        AND option_value = '17056'
                        THEN 'Version: 3.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '17516'
                        THEN 'Version: 3.1.1 - 3.1.4'
                    WHEN option_name = 'db_version'
                        AND option_value = '18226'
                        THEN 'Version: 3.2 - 3.2.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '19470'
                        THEN 'Version: 3.3 - 3.3.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '20596'
                        THEN 'Version: 3.4'
                    WHEN option_name = 'db_version'
                        AND option_value = '21115'
                        THEN 'Version: 3.4.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '21707'
                        THEN 'Version: 3.4.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '22441'
                        THEN 'Version: 3.5 - 3.5.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '22442'
                        THEN 'Version: 3.5.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '24448'
                        THEN 'Version: 3.6 - 3.6.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '25824'
                        THEN 'Version: 3.7 - 3.7.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '26148'
                        THEN 'Version: 3.7.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '26149'
                        THEN 'Version: 3.7.3 - 3.7.7'
                    WHEN option_name = 'db_version'
                        AND option_value = '26151'
                        THEN 'Version: 3.7.8 - 3.7.10'
                    WHEN option_name = 'db_version'
                        AND option_value = '26691'
                        THEN 'Version: 3.8 - 3.8.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '26692'
                        THEN 'Version: 3.8.3 - 3.8.7'
                    WHEN option_name = 'db_version'
                        AND option_value = '26694'
                        THEN 'Version: 3.8.8 - 3.8.10'
                    WHEN option_name = 'db_version'
                        AND option_value = '27916'
                        THEN 'Version: 3.9 - 3.9.5'
                    WHEN option_name = 'db_version'
                        AND option_value = '27918'
                        THEN 'Version: 3.9.6 - 3.9.8'
                    WHEN option_name = 'db_version'
                        AND option_value = '29630'
                        THEN 'Version: 4 - 4.0.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '29631'
                        THEN 'Version: 4.0.4'
                    WHEN option_name = 'db_version'
                        AND option_value = '29632'
                        THEN 'Version: 4.0.5 - 4.0.7'
                    WHEN option_name = 'db_version'
                        AND option_value = '30133'
                        THEN 'Version: 4.1 - 4.1.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '30134'
                        THEN 'Version: 4.1.4'
                    WHEN option_name = 'db_version'
                        AND option_value = '30135'
                        THEN 'Version: 4.1.5 - 4.1.7'
                    WHEN option_name = 'db_version'
                        AND option_value = '31532'
                        THEN 'Version: 4.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '31533'
                        THEN 'Version: 4.2.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '31535'
                        THEN 'Version: 4.2.2'
                    WHEN option_name = 'db_version'
                        AND option_value = '31536'
                        THEN 'Version: 4.2.3 - 4.2.4'
                    WHEN option_name = 'db_version'
                        AND option_value = '33055'
                        THEN 'Version: 4.3'
                    WHEN option_name = 'db_version'
                        AND option_value = '33056'
                        THEN 'Version: 4.3.1'
                    WHEN option_name = 'db_version'
                        AND option_value = '35700'
                        THEN 'Version: 4.4'
                    END
            FROM wp_options
            WHERE option_name = 'db_version'
            ) AS 'WP_Version',
        (
            SELECT CASE
                    WHEN option_name = 'db_version'
                        AND option_value = '35700'
                        THEN 'Up to date'
                    ELSE 'Out of date'
                    END
            FROM wp_options
            WHERE option_name = 'db_version'
            ) AS 'WP_Status',
        (
            SELECT DATABASE ()
            ) AS 'WP_Database'
    FROM wp_options;

GitHub: https://gist.github.com/dkittell/f9080101d66834910dfd

Originally Posted on September 14, 2015
Last Updated on February 12, 2016

All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.

Related

Code MySQL SQL WordPress SQL