Bases de datos

MySQL

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

Última actualización: 03-06-2018 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,
						');'
				) 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;