Useful SQL snippets

create queries to change multiple table storage engines.sql

SELECT 
    CONCAT( 'ALTER TABLE ' , table_name , ' ENGINE = innodb;' ) 
FROM 
    information_schema.TABLES
WHERE 1
    AND table_schema in ( '[MYDBNAME]' )

get DB space usage and other handy info.sql

SELECT 
    table_schema AS 'Database Name', 
    ROUND( (SUM( data_length + index_length ) / 1024 / 1024) , 2 ) AS 'Database Size in MB', 
    MAX(update_time) AS 'Last Updated (Any Table)',
	GROUP_CONCAT(DISTINCT table_collation) AS 'All Collations Found'
FROM 
	information_schema.TABLES 
WHERE 1
	AND table_schema NOT LIKE '%_schema'
	AND table_schema NOT IN( 'mysql' , 'modsec' , 'cphulkd' , 'eximstats' , 'horde' , 'roundcube' , 'leechprotect' )
GROUP BY 
	table_schema ; 

quick hits.sql

-- fix issues with GROUP_CONCAT (add this line before any query)  --

SET SESSION group_concat_max_len = 1000000;

-- analyze column types and statistics in a table:                --

SELECT [COLUMNS] FROM [TABLENAME] PROCEDURE ANALYSE();

update all EE field formatting columns to varchar.sql

SELECT 
    CONCAT( 'ALTER TABLE exp_channel_data MODIFY COLUMN field_ft_' , field_id , ' VARCHAR(128);' )
FROM 
    exp_channel_fields;

Useful SQL snippets

Various handy bits and bobs relating to SQL (and the mySQL RDBMS in particular).

# Dump all databases from CLI:

$ mysqldump -h [server] -u root --password=password --default-character-set=utf8 --set-charset --all-databases > all_dbs.sql

# Restore all databases from CLI:

$ mysql -u root --password=password --default-character-set=utf8 --set-charset < all_dbs.sql

# Run optimize (or analyze), check, and repair on all tables in all databases, from the CLI:

$ mysqlcheck -u root --password=password --auto-repair --check --optimize --all-databases