Deleting records from database

Hi please help me out.

I have a database with lots of table in it. I have to delete all r开发者_StackOverflow中文版ecords(except for 10 given studentID) from whole database whose studentId is not in given 10 ID's. studentID is a primary key in a table and foreign key in lots of tables. i am not sure even other tables might have same studentID, now i want to write a script to perform this task which will delete all records from parent table as well as child tables (sql server 2008).

For the Foreign Key constraints, you must perform deletions from the child tables first.

It would be something like this, since you have the StudentIDs already, no join is necessary to the parent table.

Delete child1 Where studentID not in (1,2,4,5,6,77,122,123,1222,12121,99999)
Delete child2 Where studentID not in (1,2,4,5,6,77,122,123,1222,12121,99999)
Delete child3 Where studentID not in (1,2,4,5,6,77,122,123,1222,12121,99999)
Delete students Where studentID not in (1,2,4,5,6,77,122,123,1222,12121,99999)

If you had a "grandchild" table, e.g. Student -> Account -> AccountHistory, then you would remove in reverse order, for example:

Delete AccountHitory
where AccountID not in (
    select AccountID
    From Account
    Where studentID in (1,2,4,5,6,77,122,123,1222,12121,99999))

Delete Account Where studentID not in (1,2,4,5,6,77,122,123,1222,12121,99999)

Delete Student Where studentID not in (1,2,4,5,6,77,122,123,1222,12121,99999)

Sample tables

create table student (studentid int identity primary key)
create table student_class (id int primary key, studentid int references student(studentid))
create table student_class_attendance (student_classid int references student_class(id), attended datetime)
create table invoice (id int primary key, studentid int references student(studentid), due datetime, amount money)

Query to find linked tables

;with tmp(lvl,FK,PK) As (
    SELECT 1, FK.TABLE_NAME, PK.TABLE_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    WHERE PK.TABLE_NAME = 'student'
    UNION ALL
    SELECT lvl+1, FK.TABLE_NAME, PK.TABLE_NAME
    FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    INNER JOIN tmp on tmp.FK = PK.TABLE_NAME
)
SELECT *
FROM tmp
order by lvl desc