One of the things that I’ve commonly run into when I am working on a data conversion is an issue where I want to delete everything from a table, but there are foreign keys pointing to that table, and then there are foreign keys pointing to the tables with the foreign keys, etc. This is a stored procedure I created that will allow you to pass in the qualified name of a table (“[dbo].[MyTable]”) and it will recursively find all tables with foreign keys to it, then delete the data from all those tables in reverse order so that you don’t run into those annoying foreign key references.
IF OBJECT_ID(N'[dbo].[CascadeDelete]') IS NOT NULL DROP PROCEDURE [dbo].[CascadeDelete] GO CREATE PROCEDURE [dbo].[CascadeDelete] @qualifiedTable VARCHAR(512) AS BEGIN IF OBJECT_ID(@qualifiedTable) IS NULL RETURN DECLARE @level INT = 1 CREATE TABLE #keys ([Level] INT ,[TableName] VARCHAR(512)) INSERT INTO [#keys] ([Level], [TableName]) SELECT @level ,@qualifiedTable WHILE @@ROWCOUNT < 0 AND @level < 100 BEGIN SET @level += 1 INSERT INTO [#keys] SELECT @Level ,'[' + [S].[name] + '].' + '[' + [T].[name] + ']' AS [TableName] FROM [sys].[tables] AS T JOIN [sys].[schemas] AS S ON [T].[schema_id] = [S].[schema_id] JOIN [sys].[foreign_keys] AS FK ON [T].[object_id] = [FK].[parent_object_id] AND [FK].[referenced_object_id] IN (SELECT OBJECT_ID([TableName]) FROM [#keys]) JOIN [sys].[columns] AS C ON [FK].[key_index_id] = [C].[column_id] AND [C].[object_id] = [FK].[parent_object_id] AND [C].[is_nullable] = 0 WHERE [T].[object_id] NOT IN (SELECT OBJECT_ID([TableName]) FROM [#keys]) END DECLARE @query VARCHAR(MAX) DECLARE cur CURSOR FAST_FORWARD READ_ONLY FOR SELECT 'DELETE FROM ' + TableName + ';' FROM [#keys] AS K ORDER BY [Level] DESC OPEN cur FETCH NEXT FROM cur INTO @query WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_sqlexec @query FETCH NEXT FROM cur INTO @query END CLOSE cur DEALLOCATE cur END