Delete data from dependent tables

Is there a query in SQL Server 2008 which will delete data from 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.

  • Need help to restore multiple databases/bak files from one folder
  • SQL Query to get aggregated result in comma seperators along with group by column in SQL Server
  • Select and merge rows in a table in SQL Stored procedure
  • How can I assign a name to the SUM column?
  • How call functions from data base in EF
  • How to check for output table of called stored procedure?
  • 3 Solutions collect form web for “Delete data from dependent tables”

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.