Currently I am working on a project that at one time required defaulting
all relationship constraints in a sql-server database to "cascade
delete". I used a stored procedure to do so.

This morning I modified the procedure to include "cascade update". Now I
am trying to determine the origin of the older procedure.
Did I create it from scratch?
Did I borrow it from someone and forget to note the creator properly?
Did I modify it from one generally known and used?
I try to make attributions carefully both in fairness and because
knowledge of the origin and creator of a procedure can help understand
and work with it.

This is the modified procedure. Can you identify the origin of it or any
part of it?

BTW, it's really clumsy because, TTBOMK, SQL-Server has no SQL capability
for modifying constraints; modification is accomplished through dropping
and recreation.

CREATE PROCEDURE [dbo].[Default_REFERENTIAL_CONSTRAINTS_TO_Cascade]

AS
DECLARE @Constraint_Name varchar(255)
DECLARE @Table_Name varchar(255)
DECLARE @Column_Name varchar(255)
DECLARE @Referenced_Table varchar(255)
DECLARE @Referenced_Column varchar(255)
DECLARE @SQLDrop varchar(255)
DECLARE @SQLAdd varchar(255)
DECLARE @SQL1 varchar(255)
DECLARE @SQL2 varchar(255)
DECLARE @Transaction varchar(255)

SET @Transaction='Transaction1'
SET @SQLDrop = 'ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME'
SET @SQLADD = 'ALTER TABLE TABLE_NAME
ADD CONSTRAINT CONSTRAINT_NAME
FOREIGN KEY (COLUMN_NAME)
REFERENCES REFERENCED_TABLE(REFERENCED_COLUMN)
ON DELETE CASCADE
ON UPDATE CASCADE'

DECLARE NON_CASCADE_FOREIGN_KEYS CURSOR FOR

SELECT RC.CONSTRAINT_NAME,
CCU.TABLE_NAME,
CCU.COLUMN_NAME AS Column_Name,
SO_1.Name AS Referenced_Table,
C.COLUMN_NAME as Referenced_Column
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
LEFT OUTER JOIN
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CCU
ON RC.CONSTRAINT_NAME=CCU.CONSTRAINT_NAME
LEFT OUTER JOIN
sys.objects AS SO
ON RC.CONSTRAINT_NAME=SO.NAME
LEFT OUTER JOIN
sys.foreign_key_columns AS fkc
ON SO.OBJECT_ID=fkc.constraint_object_ID
LEFT OUTER JOIN
sys.objects AS SO_1
ON fkc.referenced_object_id=SO_1.OBJECT_ID
LEFT OUTER JOIN
INFORMATION_SCHEMA.COLUMNS C
ON SO_1.Name=C.Table_Name
AND
fkc.referenced_column_id=C.ORDINAL_POSITION
WHERE RC.DELETE_RULE != 'CASCADE'
OR RC.UPDATE_RULE != 'CASCADE'

OPEN NON_CASCADE_FOREIGN_KEYS

FETCH NEXT From NON_CASCADE_FOREIGN_KEYS
INTO
@Constraint_Name,
@Table_Name,
@Column_Name,
@Referenced_Table,
@Referenced_Column

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL1 = REPLACE(@SQLDrop, 'CONSTRAINT_NAME', @Constraint_Name)
SET @SQL1 = REPLACE(@SQL1, 'TABLE_NAME', @Table_Name)

SET @SQL2 = REPLACE(@SQLAdd, 'CONSTRAINT_NAME', @Constraint_Name)
SET @SQL2 = REPLACE(@SQL2, 'TABLE_NAME', @Table_Name)
SET @SQL2 = REPLACE(@SQL2, 'COLUMN_NAME', @Column_Name)
SET @SQL2 = REPLACE(@SQL2, 'REFERENCED_TABLE', @Referenced_Table)
SET @SQL2 = REPLACE(@SQL2, 'REFERENCED_COLUMN', @Referenced_Column)

BEGIN TRANSACTION @Transaction

BEGIN TRY
EXECUTE (@SQL1)
EXECUTE (@SQL2)
COMMIT TRANSACTION @Transaction
END TRY

BEGIN CATCH
SELECT @Constraint_Name + ' modification failed'
ROLLBACK TRANSACTION @Transaction
END CATCH

FETCH NEXT From NON_CASCADE_FOREIGN_KEYS
INTO
@Constraint_Name,
@Table_Name,
@Column_Name,
@Referenced_Table,
@Referenced_Column

END

CLOSE NON_CASCADE_FOREIGN_KEYS
DEALLOCATE NON_CASCADE_FOREIGN_KEYS

--
lyle fairfield

- The man who told us that Canada wouldn't go there has now told us that
Canada will be the first to come back. How reassuring!