Delete data from dependent tables

Is there a query in SQL Server 2008 which will delete data fr开发者_C百科om all dependent tables as well, along with the selected table?

My apologies for not having elaborated on the question. I know that Cascade Delete would work fine, but my application connects to a 3rd party SQL Server db. I have a few Unit Tests which insert into the target table and the dependent tables. Unfortunately the constraints on the target table are not On Delete Cascade and I cannot create them or have them created. I am looking for a generic way of traversing through the dependencies and deleting data in the right order.

As there are foreign key relationships, following script could get you started mimicking a cascaded delete.

DECLARE @TableName VARCHAR(32)
DECLARE @PrimaryKey VARCHAR(32)

SET @TableName = 'MasterTable'
SET @PrimaryKey = '1'

SELECT  'DELETE FROM '
        + fks.name + '.' + fkt.name
        + ' WHERE '
        + pc.name 
        + ' = '
        + @PrimaryKey
        , fko.name as [FK Name]
        , fk.constraint_column_id as [Col Order]
        , fks.name + '.' + fkt.name as [FK table]
        , pc.name as [FK column]
        , rcs.name + '.' + rct.name as [PK table]
        , rc.name as [PK column]
FROM    sys.foreign_key_columns fk
        -- FK columns
        INNER JOIN sys.columns pc ON fk.parent_object_id = pc.object_id
                                     AND fk.parent_column_id = pc.column_id
        INNER JOIN sys.objects fkt ON pc.object_id = fkt.object_id
        INNER JOIN sys.schemas as fks ON fks.schema_id = fkt.schema_id
        -- referenced PK columns
        INNER JOIN sys.columns rc ON fk.referenced_object_id = rc.object_id
                                     AND fk.referenced_column_id = rc.column_id
        INNER JOIN sys.objects rct ON rc.object_id = rct.object_id
        INNER JOIN sys.schemas as rcs ON rcs.schema_id = rct.schema_id
        -- foreign key constraint name
        INNER JOIN sys.objects fko ON fk.constraint_object_id = fko.object_id
WHERE   rct.Name = @TableName

To do that you set up a constraint between the tables with cascading delete. You can do that in a diagram by dragging a connection between fields and editing the properties, or using a query:

 alter table SomeTable
 add constraint SomeConstraint
 foreign key (SomeField) references SomeOtherTable (SomeOtherField) 
 on delete cascade

Read about ON CASCADE DELETE from msdn, books, articles and you will find the answer.

Cascading Referential Integrity Constraints