Bases de datos

MySQL

Generación de script de tablas, llaves primarias y foráneas e índices

Última actualización: 23-10-2022 16:15

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,
						')',
 
                        case 
							when z.ENGINE is null then ''
                            else concat(' engine = ', z.ENGINE)
                        end,
                        case 
							when z.CHARACTER_SET_NAME is null then ''
                            else concat( ' DEFAULT CHARSET=', z.CHARACTER_SET_NAME)
						end ,                       
                        ';'
				) as XSQL					
		from (
				select a.TABLE_NAME, b.ORDINAL_POSITION, a.ENGINE,	c.CHARACTER_SET_NAME,				
						concat( b.COLUMN_NAME, 
								' ', b.COLUMN_TYPE,							
								case when b.EXTRA = 'auto_increment' then concat(' ', b.EXTRA) else '' end,
							case b.IS_NULLABLE when 'YES' then '' else ' not' end,
							' null',
 
 
                            case 
								when b.COLUMN_DEFAULT is null then ''
                                when trim(upper(b.COLUMN_DEFAULT)) = 'NULL' then ''
								else 
									concat( ' default ', 
									case upper(b.COLUMN_DEFAULT)
										when 'CURRENT_TIMESTAMP' then 'CURRENT_TIMESTAMP'
                                        when 'CURRENT_TIMESTAMP()' then 'CURRENT_TIMESTAMP()'
                                        else concat( "'",  replace(b.COLUMN_DEFAULT, "'", "") , "'")
 
									end
                                    )
							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
					left outer join INFORMATION_SCHEMA.CHARACTER_SETS c on c.default_collate_name = a.TABLE_COLLATION
				where a.TABLE_TYPE = 'BASE TABLE'
				order by a.TABLE_NAME, b.ORDINAL_POSITION			
			) z
			left outer join (
					select a.TABLE_NAME,  
							GROUP_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(
							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(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;