Bases de datos

SQL Server

Eliminación y creación de llaves foráneas

Última actualización: 14-11-2017 18:46

La unión de estos dos SELECTs da como resultado el listado de comandos de eliminación y creación de todas las llaves foráneas que existan en la base de datos a la que se está conectado.

IMPORTANTE:
Este comando no es útil al 100% si existen llaves foráneas a tablas cuya llave primaria esté compuesta de más de una columna.

 
declare @fks TABLE ( DROP_FK varchar(max), ADD_FK varchar(max));
insert into @fks
select
		'if exists (select * from sys.foreign_keys where name = ''' + b.name + ''') '
		+ 'alter table [' + e.name + '].[' + c.name + '] drop constraint [' + b.name + '];' as DROP_FK,
		'if not exists (select * from sys.foreign_keys where name = ''' + b.name + ''') '
		+ 'alter table [' + e.name + '].[' + c.name + '] add constraint [' + b.name + ']'
				+ ' foreign key ([' + h.name + ']) references [' + d.name + '].[' + a.name + '] ([' + g.name + '])'
				+ case when delete_referential_action_desc = 'CASCADE' then ' ON DELETE CASCADE' else '' end
				+ case when update_referential_action_desc = 'CASCADE' then ' ON UPDATE CASCADE' else '' end
				+ ';'  as ADD_FK
from sys.objects a
	inner join sys.foreign_keys b on b.referenced_object_id = a.object_id
	inner join sys.objects c on c.object_id = b.parent_object_id
	inner join sys.schemas d on d.schema_id = a.schema_id
	inner join sys.schemas e on e.schema_id = c.schema_id
	inner join sys.foreign_key_columns f on f.constraint_object_id = b.object_id
	inner join sys.columns g on g.object_id = a.object_id and g.column_id = f.referenced_column_id
	inner join sys.columns h on h.object_id = c.object_id and h.column_id = f.parent_column_id;
 
 
select XSQL from (select 1 as XO, DROP_FK as XSQL from @fks union select 2, ADD_FK from @fks ) b order by b.XO;
 
go