0

I have a table with several constraints without the on cascade delete property.

So if I try to delete and object by id I obtain the following error:

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

The problem is that if i run the delete I obtain the error about one constraint at time, is there a way to obtain all the table on which the constraint is present with a command?

In this way I can produce a query that delete the objects constrained before the parent node.

Kerby82
  • 4,934
  • 14
  • 48
  • 74
  • You can disable constraints: http://stackoverflow.com/a/161410/3270427 – McNets Mar 20 '17 at 20:51
  • I don't want to disable constraints, they are useful I just want a way to produce the right query deleting all the objects that have a constraint – Kerby82 Mar 20 '17 at 20:55
  • @McNets disabling constraints is a very bad idea. It will stop the database from keeping data integrity. – Zohar Peled Mar 20 '17 at 20:55
  • I know, but you want to delete a row with constraints, I suppose you know your table schema and which fields have a constraint. – McNets Mar 20 '17 at 20:57
  • The problem is that i don't know how to export a list of tables and columns with constraints on my table. The only way I know is to go to every table click on constraints and foreing keys and take note of it. I was hoping that a DBMS as a command that list all the tables and column that are involved in the constraints. – Kerby82 Mar 20 '17 at 21:00

1 Answers1

1

If I understand the question, you want to know how to find the foreign keys that references your table - Well, this should get you started:

DECLARE @YourTableName sysname = '<Your table name goes here>'

SELECT  OBJECT_NAME(f.parent_object_id) As ParentTable, 
        OBJECT_NAME(f.referenced_object_id) As ReferencedTable, 
        c1.name As ParentColumnName, 
        c2.name As ReferencedColumnName
FROM sys.foreign_key_columns f
INNER JOIN sys.all_columns c1
ON f.parent_object_id = c1.object_id and f.parent_column_id = c1.column_id 
INNER JOIN sys.all_columns c2
ON f.referenced_object_id = c2.object_id and f.referenced_column_id = c2.column_id 
WHERE OBJECT_NAME(f.referenced_object_id) = @YourTableName
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • that was exactly what I was looking for, so I can delete all the object referenced and at the end delete the object, thank you! – Kerby82 Mar 20 '17 at 22:11