--COPY TABLE FROM ONE DB TO ANOTHER
CREATE TABLE GA_LICENSE_MT AS SELECT * FROM GOAUDITS.GA_LICENSE_MT
--SCHEMA DETAILS
SELECT 'mysql' dbms,t.TABLE_SCHEMA,t.TABLE_NAME,c.COLUMN_NAME,c.ORDINAL_POSITION,c.DATA_TYPE,c.CHARACTER_MAXIMUM_LENGTH,n.CONSTRAINT_TYPE,k.REFERENCED_TABLE_SCHEMA,k.REFERENCED_TABLE_NAME,k.REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON t.TABLE_SCHEMA=c.TABLE_SCHEMA AND t.TABLE_NAME=c.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.TABLE_SCHEMA=k.TABLE_SCHEMA AND c.TABLE_NAME=k.TABLE_NAME AND c.COLUMN_NAME=k.COLUMN_NAME LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS n ON k.CONSTRAINT_SCHEMA=n.CONSTRAINT_SCHEMA AND k.CONSTRAINT_NAME=n.CONSTRAINT_NAME AND k.TABLE_SCHEMA=n.TABLE_SCHEMA AND k.TABLE_NAME=n.TABLE_NAME
WHERE t.TABLE_TYPE='BASE TABLE' AND t.TABLE_SCHEMA IN('AUDITS')
---DB SIZE
SHOW COLUMNS FROM `GA_ACCOUNTS_MT` LIKE 'user_id%';
SELECT TABLE_TYPE,table_schema AS DBName,TABLE_NAME AS TABLE_NAME,
table_rows AS QuantofRows,
ROUND((data_length + index_length) /1024, 2 ) AS "DB Size in KB" ,
ROUND((data_length + index_length) / 1024 / 1024, 1) AS "DB Size in MB"
FROM information_schema.TABLES
WHERE table_schema = 'GOAUDITS'
AND TABLE_TYPE='BASE TABLE' AND TABLE_NAME LIKE 'GA_AUD%'
