Convert Foreign Key to Foreign Key with Delete Cascade

We sometimes get very large databases that we want to cut down to use for
testing.
The information is all related to a central accounts table.
The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.
After this I will restore the constraints back to their original state.
I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.
For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.
My idea was to go through the database saying:
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade
delete various accounts and related data
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)
Can anyone assist?
Thanks
Jason, that looks like the best approach to me as well.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Convert Foreign Key to Foreign Key with Delete Cascade

We sometimes get very large databases that we want to cut down to use for
testing.
The information is all related to a central accounts table.
The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.
After this I will restore the constraints back to their original state.
I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.
For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.
My idea was to go through the database saying:
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade
delete various accounts and related data
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)
Can anyone assist?
ThanksJason, that looks like the best approach to me as well.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Convert Foreign Key to Foreign Key with Delete Cascade

We sometimes get very large databases that we want to cut down to use for
testing.
The information is all related to a central accounts table.
The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.
After this I will restore the constraints back to their original state.
I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.
For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.
My idea was to go through the database saying:
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade
delete various accounts and related data
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)
Can anyone assist?
ThanksJason, that looks like the best approach to me as well.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Convert Foreign Key to Foreign Key with Delete Cascade

We sometimes get very large databases that we want to cut down to use for
testing.
The information is all related to a central accounts table.
The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.
After this I will restore the constraints back to their original state.
I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.
For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.
My idea was to go through the database saying:
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade
delete various accounts and related data
alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)
Can anyone assist?
Thanks
Hi
Erland posted this a short time ago, you should be able to modify it to meet
your needs:
http://tinyurl.com/6o9af
John
"Jason Madison" <jason.madison@.btinternet.com> wrote in message
news:e9zk36GZEHA.2576@.TK2MSFTNGP10.phx.gbl...
> We sometimes get very large databases that we want to cut down to use for
> testing.
> The information is all related to a central accounts table.
> The way I thought of doing this is to grab all the foreign constraints and
> turn them into cascade delete constraints, then delete as many accounts as
I
> want.
> After this I will restore the constraints back to their original state.
> I am having a problem doing this as I cannot find a way to programatically
> get the add constraint foreign key sql.
> For example, I can use sysforeignkeys to list all the foreign keys or
> ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
> SQL to modify.
> My idea was to go through the database saying:
> alter table (tablename) drop constraint (foreign key)
> alter table (tablename) add constraint (foreign key) on delete cascade
> delete various accounts and related data
> alter table (tablename) drop constraint (foreign key)
> alter table (tablename) add constraint (foreign key)
> Can anyone assist?
> Thanks
>
>