Cacher is the code snippet organizer for pro developers

USE asterisk;

DELIMITER $$

DROP FUNCTION IF EXISTS GETALLDBCREATEINFO$$
CREATE DEFINER = 'root'@'%'
FUNCTION GETALLDBCREATEINFO(databaseName varchar(100))
  RETURNS text CHARSET latin1
  DETERMINISTIC
BEGIN
    DECLARE msql text DEFAULT '';
    DECLARE tableName text;
    DECLARE tmpsql text;
    DECLARE MAX_LENGHT int;
    DECLARE i int default 1;
    SET @row_number = 0;
    CREATE TEMPORARY TABLE IF NOT EXISTS tmpTable AS (SELECT (@row_number:=@row_number + 1) AS ROW_NUMBER,TABLE_NAME FROM information_schema.tables where table_schema=databaseName);
    SELECT  MAX(ROW_NUMBER) INTO MAX_LENGHT FROM tmpTable;
    WHILE i < MAX_LENGHT + 1 DO
        SELECT TABLE_NAME INTO tableName FROM tmpTable WHERE ROW_NUMBER = i;
        SELECT GETTABLECREATEINFO(databaseName, tableName) INTO tmpsql;
        set msql = CONCAT(msql, '\r\n', tmpsql);
        SET i = i + 1;
     END WHILE;
    DROP TEMPORARY TABLE IF EXISTS tmpTable;
    RETURN msql;
  END
$$

DELIMITER ;

USE asterisk;

DELIMITER $$

DROP FUNCTION IF EXISTS GETTABLECREATEINFO$$
CREATE DEFINER = 'root'@'%'
FUNCTION GETTABLECREATEINFO(databaseName varchar(100),tableName varchar(100))
  RETURNS text CHARSET latin1
  DETERMINISTIC
BEGIN
    DECLARE msql text;
    DECLARE MAX_LENGHT bigint;
    DECLARE i int default 1;
    DECLARE columnName varchar(100);
    DECLARE isNullable varchar(3);
    DECLARE dataType varchar(100);
    DECLARE dataLenght decimal(21,0);
    set msql = CONCAT('CREATE TABLE [',  tableName, '](');
    set msql = CONCAT(msql, 'LOGICALREF int IDENTITY(1,1) PRIMARY KEY,');
    SELECT  MAX(ORDINAL_POSITION) INTO MAX_LENGHT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=databaseName AND TABLE_NAME=tableName;
    
    WHILE i <= MAX_LENGHT DO
      
      SELECT COLUMN_NAME INTO columnName FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=databaseName AND TABLE_NAME=tableName AND ORDINAL_POSITION = i;
      SELECT IS_NULLABLE INTO isNullable FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=databaseName AND TABLE_NAME=tableName AND ORDINAL_POSITION = i;
      SELECT DATA_TYPE INTO dataType FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=databaseName AND TABLE_NAME=tableName AND ORDINAL_POSITION = i;
      SELECT CHARACTER_MAXIMUM_LENGTH INTO dataLenght FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=databaseName AND TABLE_NAME=tableName AND ORDINAL_POSITION = i;

      set msql = CONCAT(msql, '[', columnName, ']');
        
      IF dataType = 'varchar' THEN
        set dataType = CONCAT('nvarchar(', dataLenght, ')');
      ELSEIF dataType = 'blob' OR dataType = 'mediumblob' OR dataType = 'longblob' THEN
        set dataType = CONCAT('varbinary(max)'); 
      ELSEIF dataType = 'longtext' OR dataType = 'mediumtext' THEN
        set dataType = CONCAT('nvarchar(max)'); 
      ELSEIF dataType = 'enum' OR dataType = 'set' THEN
        set dataType = CONCAT('nvarchar(50)'); 
      ELSEIF dataType = 'timestamp' THEN
        set dataType = CONCAT('datetime'); 
      ELSEIF dataType = 'char' THEN
        set dataType = CONCAT('nchar(', dataLenght, ')');
      END IF;
      set msql = CONCAT(msql, ' ' ,dataType, IF(isNullable = 'NO', ' NOT NULL', ''), IF(i = MAX_LENGHT, ')', ','));
      SET i = i + 1;
    END WHILE;

    set msql = CONCAT(msql, ' INSERT INTO [dbo].[', tableName, '] SELECT * FROM OPENQUERY([MYSQLLINKEDSERVER], ''SELECT * FROM ', databaseName, '.', tableName, ''')');
    RETURN msql;
  END
$$

DELIMITER ;