Cascade Delete turned on?

Question

Is there a way to check if the table(s) have Cascade Delete turned on? I'm looking at the script of the table (from SQL Server) and I don't see any indication of Cascade Delete.

Answer 1

You can use INFORMATION_SCHEMA for standard approach, ex.

select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
where DELETE_RULE ='CASCADE'

Answer 2

Please use sys.foreign_keys for foreign key relations.

The column - delete_referential_action helps you know if there is a delete on cascade.

http://technet.microsoft.com/en-us/library/ms189807.aspx

Below View help with similar works:

sys.default_constraints for default constraints on columns
sys.check_constraints for check constraints on columns
sys.key_constraints for key constraints (e.g. primary keys)
sys.foreign_keys for foreign key relations

Source: SQL Server 2008- Get table constraints

Answer 3

I found how to do this:

I scripted the FK on the table to a new query window:

ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [FK_myTable_myTableHeaders] FOREIGN KEY([ID])
REFERENCES [dbo].[myTableHeaders] ([_ID])
ON DELETE CASCADE
GO

This how I was able to confirm it.