Useful queries

Useful Queries¶

Here is a list of useful MySQL queries, for various things.

Comparing databases¶

Comparing core_config_data table values from within 2 databases.

SELECT prod.*, dev.value
FROM `database1`.core_config_data as prod 
LEFT JOIN `database2`.core_config_data as dev ON 
(
    prod.scope = dev.scope
    AND prod.scope_id = dev.scope_id
    AND prod.path = dev.path
)

WHERE prod.value != dev.value

The one below is exluding store URL's

SELECT prod.*, dev.value
FROM `database1`.core_config_data as prod 
LEFT JOIN `database2`.core_config_data as dev ON 
(
    prod.scope = dev.scope
    AND prod.scope_id = dev.scope_id
    AND prod.path = dev.path
)

WHERE prod.value != dev.value 
AND prod.path NOT LIKE 'web%'

Show table headers¶

SELECT COLUMN_NAME FROM information_schema.columns WHERE table_name = 'table_name';

Show tables names that contain string¶

SHOW TABLES WHERE Tables_in_insertTableName LIKE '%string%';

Show tables which contain a certain column name¶

SELECT      COLUMN_NAME AS 'ColumnName'
            ,TABLE_NAME AS  'TableName'
FROM        INFORMATION_SCHEMA.COLUMNS
WHERE       COLUMN_NAME LIKE '%insert_column_name%'
ORDER BY    TableName ,ColumnName;