Skip to content

Instantly share code, notes, and snippets.

@donpandix

donpandix/list_fk.sql

Last active May 18, 2021 14:00
Show Gist options
  • Save donpandix/bed8c870fe068d567e58e5bb73a6fc05 to your computer and use it in GitHub Desktop.
Save donpandix/bed8c870fe068d567e58e5bb73a6fc05 to your computer and use it in GitHub Desktop.
Download ZIP
[Limpiar de forma eficaz tablas en base de datos] Instrucciones para borrar el contenido de todas las tablas sin las restricciones de las claves foraneas #sql #query #delete #constraint #clean
Raw
list_fk.sql
-- Lista de claves foraneas, solo información
SELECT name AS 'Constraint',
OBJECT_NAME(parent_object_id) AS 'Table',
OBJECT_NAME(referenced_object_id) AS 'Referenced Table',
is_disabled,
is_not_trusted
FROM sys.foreign_keys;
-- Lista de instrucciones para deshabilitar las claves foraneas
SELECT 'ALTER TABLE ' +OBJECT_NAME(parent_object_id)+ ' NOCHECK CONSTRAINT ' + name +';' as deshabilita
FROM sys.foreign_keys;
-- Lisat de instrucciunes para eliminar el contenido de las tablas
SELECT 'DELETE FROM ' + name + ';' as instruccion_borra
FROM sys.all_objects
WHERE type_desc='USER_TABLE'
AND object_id > 0
AND name not like 'sys%';
-- instrucciones para habilitar nuevamente las claves foraneas
SELECT 'ALTER TABLE ' +OBJECT_NAME(parent_object_id)+ ' CHECK CONSTRAINT ' + name +';' as habilita
FROM sys.foreign_keys;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment