| Partha 2005-08-02, 8:24 pm |
| Here is the script which generates drop and add fkey constraint
This is based on another posting
<<
Erland posted this a short time ago, you should be able to modify it to
meet
your needs:
http://tinyurl.com/6o9af
[color=darkred]
Thanks Erland and every one.
.....................
SET NOCOUNT ON
DECLARE @dummy tinyint,
@fktbl sysname,
@fkcol sysname,
@refcol sysname,
@reftbl sysname,
@keyno smallint,
@constr sysname,
@prev_constr sysname,
@drop varchar(4000),
@add1 varchar(4000),
@add2 varchar(4000),
@tmpname sysname,
@no smallint,
@err int
DECLARE sql_fkey_cur INSENSITIVE CURSOR FOR
SELECT k = 1, constr = object_name(constid)
, fktbl =
object_name(fkeyid),
fkcol = col_name(fkeyid, fkey), refcol = col_name(rkeyid,
rkey), reftbl = object_name(rkeyid),
keyno
FROM sysforeignkeys
OPEN sql_fkey_cur
FETCH NEXT FROM sql_fkey_cur INTO @dummy, @constr, @fktbl, @fkcol,
@refcol,@reftbl, @keyno
WHILE @@fetch_status = 0
Begin
-- SELECT @no = @no + 1
-- And set up command to drop current constraint.
SELECT @drop = 'ALTER TABLE ' + @fktbl +
' DROP CONSTRAINT ' + @constr
Print @drop
SELECT @add1 = 'ALTER TABLE ' + cast(@fktbl as varchar)
+ ' ADD CONSTRAINT ' + @constr +
' FOREIGN KEY (' + cast(@fkcol as varchar) + ')
REFERENCES ' + @reftbl + ' (' +
cast(@refcol as varchar) + ')' + ' on delete
cascade'
print @add1
Print '-------------'
FETCH NEXT FROM sql_fkey_cur INTO @dummy, @constr, @fktbl, @fkcol,
@refcol,@reftbl, @keyno
End
CLOSE sql_fkey_cur
DEALLOCATE sql_fkey_cur
|