Skip to content

Instantly share code, notes, and snippets.

@dkittell

dkittell/MySQL-WordPressActivePlugins.sql

Last active January 12, 2018 05:04
Show Gist options
  • Save dkittell/f9080101d66834910dfd to your computer and use it in GitHub Desktop.
Save dkittell/f9080101d66834910dfd to your computer and use it in GitHub Desktop.
Download ZIP
WordPress MySQL Database Information
Raw
MySQL-WordPressActivePlugins.sql
-- 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;
Raw
MySQL-WordPressGetActiveTheme.sql
-- 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';
Raw
MySQL-WordPressGetInfoWithUserRoles.sql
-- 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;
Raw
MySQL-WordPressRunProceduresMultipleDBs.sql
-- 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'
)
Raw
MySQL-WordPressUserUserRole.sql
-- 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;
Raw
MySQL-WordPressViewSiteInfo.sql
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment