Blog ala Vidar

Foreign Keys

SET NOCOUNT ON

DECLARE
  @FKName AS NVARCHAR(128),
  @FKObjectID AS INTEGER,
  @TableName AS NVARCHAR(128),
  @ReferencedTableName AS NVARCHAR(128),
  @delete_referential_action_desc AS NVARCHAR(128),
  @update_referential_action_desc AS NVARCHAR(128),
  @Tries AS TINYINT,
  @FKColumns AS NVARCHAR(MAX),
  @ReferenceColumns AS NVARCHAR(MAX),
  @SQL AS NVARCHAR(MAX)


/*
 * Remember to change stbl_Database_% to the namespace you are working on
 */
SELECT 
  name AS FKName, object_id AS FKObjectID,
  OBJECT_NAME(parent_object_id) AS TableName, 
  OBJECT_NAME(referenced_object_id) AS ReferencedTableName,
  delete_referential_action_desc, update_referential_action_desc,
  CAST(0 AS BIT) AS Dropped, CAST(0 AS BIT) AS Tries
	INTO #FKs
	FROM sys.foreign_keys
	WHERE schema_id = 1
	  AND OBJECT_NAME(parent_object_id) LIKE 'stbl_Database_%'

SELECT
  constraint_object_id AS FKObjectID, 
  constraint_column_id AS FKColumnID, 
  (SELECT name 
		FROM sys.syscolumns 
		WHERE id = parent_object_id 
		  AND colid = FKC.parent_column_id) AS ParentColumnName,
  (SELECT name 
		FROM sys.syscolumns 
		WHERE id = referenced_object_id 
		  AND colid = FKC.referenced_column_id) AS ReferencedColumnName
	INTO #FKCs
	FROM sys.foreign_key_columns AS FKC
	WHERE EXISTS (SELECT *
					FROM #FKs
					WHERE FKObjectID = FKC.constraint_object_id)
	ORDER BY FKColumnID


WHILE EXISTS (SELECT * FROM #FKs WHERE Dropped = 0 AND Tries < 10)
BEGIN
	SELECT TOP 1
	  @FKName = FKName,
	  @TableName = TableName,
	  @Tries = Tries
		FROM #FKs
		WHERE Dropped = 0
		ORDER BY Tries

	SET @SQL = 'SET NOCOUNT ON ' + CHAR(13) + CHAR(10)
	SET @SQL = @SQL + 'ALTER TABLE [dbo].[' + @TableName + '] DROP CONSTRAINT [' + @FKName + ']'

	BEGIN TRY
		EXEC sp_executesql @SQL

		UPDATE #FKs
			SET Dropped = 1
			WHERE TableName = @TableName
			  AND FKName = @FKName
	END TRY
	BEGIN CATCH
		IF @Tries = 9
		BEGIN
			RAISERROR('Unable to drop FK: %s', 18, 1, @FKName)
		END

		UPDATE #FKs
			SET Tries = Tries + 1
			WHERE TableName = @TableName
			  AND FKName = @FKName
	END CATCH
END


/*
 *
 * Do the import of data
 *
 */


UPDATE #FKs
	SET Tries = 0

WHILE EXISTS (SELECT * FROM #FKs WHERE Tries < 10)
BEGIN
	SELECT TOP 1
	  @FKObjectID = FKObjectID,
	  @FKName = FKName,
	  @TableName = TableName,
	  @ReferencedTableName = ReferencedTableName,
	  @delete_referential_action_desc = delete_referential_action_desc,
	  @update_referential_action_desc = update_referential_action_desc,
	  @Tries = Tries
		FROM #FKs
		ORDER BY Tries

	SET @FKColumns = ''
	SET @ReferenceColumns = ''

	SELECT
	  @FKColumns = @FKColumns + '[' + ParentColumnName + '], ',
	  @ReferenceColumns = @ReferenceColumns + '[' + ReferencedColumnName + '], '
		FROM #FKCs AS FKCs
		WHERE FKObjectID = @FKObjectID
		ORDER BY FKColumnID

	SET @FKColumns = LEFT(@FKColumns, LEN(@FKColumns) - 1)
	SET @ReferenceColumns = LEFT(@ReferenceColumns, LEN(@ReferenceColumns) - 1)

	SET @SQL = 'SET NOCOUNT ON ' + CHAR(13) + CHAR(10)
	SET @SQL = @SQL + 'ALTER TABLE [dbo].[' + @TableName + ']  '
	SET @SQL = @SQL + ' WITH CHECK ADD  CONSTRAINT [' + @FKName + '] FOREIGN KEY(' + @FKColumns + ') '
	SET @SQL = @SQL + ' REFERENCES [dbo].[' + @ReferencedTableName + '] (' + @ReferenceColumns + ') '

	IF @delete_referential_action_desc = 'CASCADE'
	BEGIN
		SET @SQL = @SQL + ' ON DELETE CASCADE '
	END
	ELSE IF @delete_referential_action_desc = 'SET_NULL'
	BEGIN
		SET @SQL = @SQL + ' ON DELETE SET NULL '
	END
	ELSE IF @delete_referential_action_desc = 'SET_DEFAULT'
	BEGIN
		SET @SQL = @SQL + ' ON DELETE SET DEFAULT '
	END

	IF @update_referential_action_desc = 'CASCADE'
	BEGIN
		SET @SQL = @SQL + ' ON UPDATE CASCADE '
	END
	ELSE IF @update_referential_action_desc = 'SET_NULL'
	BEGIN
		SET @SQL = @SQL + ' ON UPDATE SET NULL '
	END
	ELSE IF @update_referential_action_desc = 'SET_DEFAULT'
	BEGIN
		SET @SQL = @SQL + ' ON UPDATE SET DEFAULT '
	END

	BEGIN TRY
		EXEC (@SQL)

		SET @SQL = 'SET NOCOUNT ON ' + CHAR(13) + CHAR(10)
		SET @SQL = @SQL + 'ALTER TABLE [dbo].[' + @TableName + '] CHECK CONSTRAINT [' + @FKName + ']'

		EXEC (@SQL)

		DELETE
			FROM #FKs
			WHERE FKObjectID = @FKObjectID
	END TRY
	BEGIN CATCH
		IF @Tries = 9
		BEGIN
			RAISERROR('Unable to drop FK: %s', 18, 1, @FKName)
		END

		UPDATE #FKs
			SET Tries = Tries + 1
			WHERE FKObjectID = @FKObjectID
	END CATCH
END

DROP TABLE #FKCs
DROP TABLE #FKs