• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 296
  • Last Modified:

Cascading Delete for every relationship in a database

I have a database that I need to clean about half of the records out of because a client is selling off half of his company.   The whole thing is a huge mess of tables with various dependencies all over the place.  I need to know if anyone knows of a script that can be run or an option that can be set to automatically allow cascading delete for every relationship in the entire database.  Thanks!  I'd make this worth more points, but I'm new and that's all I've got.
0
BigSnazz
Asked:
BigSnazz
  • 2
1 Solution
 
ispalenyCommented:
/*
create table TestRefA(X int primary key)
create table TestRefB(X int references TestRefA(X))
drop table dbo.mysysusers
drop table dbo.mysysreferences
drop table dbo.mysysobjects
drop table dbo.mysyscolumns
drop table dbo.mysysreferenceskeys
*/
select *
into dbo.mysysusers
from dbo.sysusers
select *
into dbo.mysysreferences
from dbo.sysreferences
select *
into dbo.mysysobjects
from dbo.sysobjects
select *
into dbo.mysyscolumns
from dbo.syscolumns
select constid,CAST(B.name as varchar(8000)) F,CAST(C.name as varchar(8000)) R,CAST('' AS varchar(8000)) FN,CAST('' AS varchar(8000)) RN,CAST('' AS varchar(8000)) CN
 into dbo.mysysreferenceskeys
 from dbo.mysysreferences A join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey1=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey1=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey2=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey2=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey3=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey3=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey4=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey4=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey5=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey5=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey6=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey6=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey7=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey7=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey8=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey8=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey9=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey9=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey10=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey10=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey11=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey11=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey12=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey12=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey13=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey13=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey14=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey14=C.colid
update X set F=F+COALESCE(','+B.name,''),R=R+COALESCE(','+C.name,'') from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysyscolumns B ON A.fkeyid=B.id AND A.fkey15=B.colid join dbo.mysyscolumns C ON A.rkeyid=C.id AND A.rkey15=C.colid
update X set RN=Y.RN,CN=Y.CN
from dbo.mysysreferenceskeys X
join
(
select A.constid,QUOTENAME(D.name)+'.'+QUOTENAME(C.name) RN,QUOTENAME(B.name) CN
from dbo.mysysreferences A
join dbo.mysysobjects B ON A.constid=B.id
join dbo.mysysobjects C ON B.parent_obj=C.id
join dbo.mysysusers   D ON C.uid=D.uid
) Y ON X.constid=Y.constid
update X set FN=Y.FN
from dbo.mysysreferenceskeys X
join
(
select X.constid,QUOTENAME(D.name)+'.'+QUOTENAME(C.name) FN
from dbo.mysysreferenceskeys X join dbo.mysysreferences A ON X.constid=A.constid join dbo.mysysobjects C ON A.rkeyid=C.id join dbo.mysysusers   D ON C.uid=D.uid
) Y ON X.constid=Y.constid

select 'alter table '+RN+' drop '+CN
from dbo.mysysreferenceskeys
select 'alter table '+RN+' add constraint '+CN+' FOREIGN KEY '++'('+R+') REFERENCES '+FN+' ('+F+') ON DELETE CASCADE'
from dbo.mysysreferenceskeys
0
 
ispalenyCommented:
I wouldn't post it for such a low bonus, but you answered at least one question before. Good luck in further investigation.
0
 
BigSnazzAuthor Commented:
That's amazing..and it did exactly what I needed.  Thanks!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now