La unión de estos dos SELECTs da como resultado el listado de comandos de creación de tablas, llaves primarias y foráneas de la base de datos en que se ejecute.
SET SESSION group_concat_max_len = 100000000; select XSQL from ( -- COMANDOS SQL PARA CREACIÓN DE TABLAS select 10 as ROW_BLOCK, concat('create table ', '[', z.TABLE_NAME, ']', ' (', GROUP_CONCAT(z.MSSQL_COLUMN_SCRIPT order by ORDINAL_POSITION separator ', '), case when y.PK_COLS is null then '' else concat(' primary key (', y.PK_COLS, ')') end, ');' ) as XSQL from ( select a.TABLE_NAME, b.ORDINAL_POSITION, concat('[', b.COLUMN_NAME, '] ', case when b.DATA_TYPE in ('varchar', 'char') then concat(b.DATA_TYPE, '(', b.CHARACTER_MAXIMUM_LENGTH, ')') when b.DATA_TYPE in ('varbinary', 'tinyblob', 'blob', 'mediumblob', 'longblob') then concat('varbinary(', b.CHARACTER_MAXIMUM_LENGTH, ')') when b.DATA_TYPE = 'int' then 'int' when b.DATA_TYPE = 'timestamp' then 'datetime' when b.DATA_TYPE = 'year' then 'smallint' when b.DATA_TYPE = 'float' then concat('float(', b.NUMERIC_PRECISION + b.NUMERIC_SCALE, ')') when b.DATA_TYPE in ('double', 'real') then 'float(53)' when b.DATA_TYPE in ('decimal') then concat('decimal(', b.NUMERIC_PRECISION, ', ', b.NUMERIC_SCALE, ')') when b.DATA_TYPE in ('smallint', 'tinyint', 'date', 'bigint', 'datetime' , 'time') then b.DATA_TYPE when b.DATA_TYPE in ('text', 'tinytext', 'mediumtext', 'longtext') then 'nvarchar(max)' else '[UNKNOW DATA TYPE]' end, case when b.EXTRA = 'auto_increment' then ' identity(1, 1)' else '' end, case b.IS_NULLABLE when 'YES' then '' else ' not' end, ' null', case when b.COLUMN_DEFAULT is null then '' else concat(' default ''', replace(replace(b.COLUMN_DEFAULT, 'CURRENT_TIMESTAMP', 'getdate()'), '0000-00-00', '1753-01-01' ), '''') end ) as MSSQL_COLUMN_SCRIPT from information_schema.tables a inner join information_schema.columns b on a.table_schema = database() and b.table_schema = database() and b.table_name = a.table_name where a.TABLE_TYPE = 'BASE TABLE' order by a.TABLE_NAME, b.ORDINAL_POSITION ) z left outer join ( select a.TABLE_NAME, GROUP_CONCAT( concat('[', b.COLUMN_NAME, ']') order by ifnull(b.ORDINAL_POSITION, 1) separator ', ' ) as PK_COLS from information_schema.TABLE_CONSTRAINTS a inner join information_schema.KEY_COLUMN_USAGE b on a.table_schema = database() and b.table_schema = database() and b.table_name = a.table_name and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME where a.table_schema = database() and a.CONSTRAINT_TYPE = 'PRIMARY KEY' group by a.TABLE_NAME ) y on y.TABLE_NAME = z.TABLE_NAME group by z.TABLE_NAME union -- COMANDOS SQL PARA CREACIÓN DE LLAVES FORÁNEAS select 20 as ROW_BLOCK, concat('alter table [', a.TABLE_NAME, ']' ' add constraint ', a.CONSTRAINT_NAME, ' foreign key (', group_concat( concat('[', b.COLUMN_NAME, ']') order by ifnull(ORDINAL_POSITION, 0) separator ', ' ), ')', ' references [', b.REFERENCED_TABLE_NAME, '] (', group_concat( concat('[', b.REFERENCED_COLUMN_NAME, ']') order by ifnull(ORDINAL_POSITION, 0) separator ', ' ), ')' , ';') as FK_SQL from information_schema.TABLE_CONSTRAINTS a inner join information_schema.KEY_COLUMN_USAGE b on a.table_schema = database() and b.table_schema = database() and b.table_name = a.table_name and b.CONSTRAINT_NAME = a.CONSTRAINT_NAME where CONSTRAINT_TYPE = 'FOREIGN KEY' group by a.TABLE_NAME, a.CONSTRAINT_NAME union -- COMANDOS SQL PARA CREACIÓN DE ÍNDICES select 70, concat('create ', case a.NON_UNIQUE when 0 then ' unique' else '' end, ' index [', a.INDEX_NAME, '] on [', a.TABLE_NAME, '] (', GROUP_CONCAT(concat('[', a.COLUMN_NAME, ']') order by a.SEQ_IN_INDEX separator ', '), ');') as XSQL from information_schema.STATISTICS a left outer join information_schema.TABLE_CONSTRAINTS b on b.table_schema = database() and b.TABLE_NAME = a.TABLE_NAME and b.CONSTRAINT_NAME = a.INDEX_NAME and b.CONSTRAINT_TYPE in ('PRIMARY KEY', 'FOREIGN KEY') where a.table_schema = database() and b.CONSTRAINT_NAME is null group by a.TABLE_NAME, a.INDEX_NAME, a.NON_UNIQUE ) ft order by ROW_BLOCK;