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.

  • How can I transfer LINQ to SQL results to an external dll?
  • Full outer join with multiple columns
  • Sync with active table Microsoft SQL Server
  • How to sqoop rowversion /timestmap/binary SQL Server datatypes into hive using sqoop
  • Using SQL Server database for login control instead of default ASPNETDB.mdf database
  • SQL server using unpivot function
  • 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'
            + + '.' +
            + ' WHERE '
            + ' = '
            + @PrimaryKey
            , as [FK Name]
            , fk.constraint_column_id as [Col Order]
            , + '.' + as [FK table]
            , as [FK column]
            , + '.' + as [PK table]
            , 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.