4

I need to delete some SQL Azure database entries, and I'm not sure if the cascade on delete is specified or not. If I by accident delete something important, I'm in a world of hurt. So, is there a command to check for cascade deletion?

Gleno
  • 16,621
  • 12
  • 64
  • 85
  • Are you saying you have a table with a FK to the table from which you want to delete something? If you DO have cascade delete, you know what happens. If you DON'T, the delete will fail. Isn't that the right thing? – n8wrl Sep 01 '11 at 19:51
  • I might have a table with an FK to the ... I don't know. If I risk it, and find out the hard way - see world of hurt. =) In that case I have to move the FK constraints first. – Gleno Sep 01 '11 at 19:53
  • @Gleno, do not remope Fks just so you can delete. They are there for a reason, removing them and deleting the parents without deleting the children is a guanantee of data integrity problems. If you do not want the child records deleted with the parent records, then you CANNOT delete the parent record, ever. Mark it as inactive instead. – HLGEM Sep 02 '11 at 13:13
  • HLGEM, ye, I get what you are saying, but it's not like that. I have some accidental dupes, some records point to those dupes, and now I have to re-point them to originals and delete the dupes. Aaaand, thanks to this question this is already done and works fine-thank-u-very-much. :) – Gleno Sep 03 '11 at 00:33

1 Answers1

3

Use sp_fkeys to find it. http://msdn.microsoft.com/en-us/library/ms175090.aspx Look at the resultset's "DELETE_RULE" column. 

0=CASCADE
1=NO ACTION

An example:

USE MyDB;
GO
EXEC sp_fkeys @pktable_name = N'MyTable',@pktable_owner = N'MyUserName';
Don Rhummy
  • 24,730
  • 42
  • 175
  • 330
  • 1
    This worked, but without the *N'MyUserName'* bit. Thanks! Results are bleak, cascade on delete is on. Also, there's no **USE** on Azure, afaik – Gleno Sep 01 '11 at 20:01
  • @Gleno I don't know what your permissions on the DB are, but you can remove cascade delete (not recommended unless you REALLY know the DB schema and that it won't screw it up) – Don Rhummy Sep 01 '11 at 20:24
  • Oh, I belive it will screw it up. I'm going to clean it up manually. After finding out that there are only 2 dependencies, using your snippet, I can now proceed cautiously. :) – Gleno Sep 01 '11 at 20:34