CREATE FUNCTION dbo.fnCascadingDelete
(
	@Schema NVARCHAR(128) = NULL,
	@Table NVARCHAR(128) = NULL
)
RETURNS	@Return TABLE
	(
		RowID INT PRIMARY KEY CLUSTERED,
		IsSelfJoin TINYINT NOT NULL,
		HasPk TINYINT NOT NULL,
		[SQL] NVARCHAR(4000) NOT NULL
	)
AS
BEGIN
	DECLARE	@Constraints TABLE
		(
			RowID INT NOT NULL,
			Indent SMALLINT NOT NULL,
			[Catalog] NVARCHAR(128) NOT NULL,
			[Schema] NVARCHAR(128) NOT NULL,
			[Table] NVARCHAR(128) NOT NULL,
			[Column] NVARCHAR(128),
			pkCatalog NVARCHAR(128),
			pkSchema NVARCHAR(128),
			pkTable NVARCHAR(128),
			pkColumn NVARCHAR(128),
			pkType NVARCHAR(128),
			pkSize INT,
			IsSelfJoin TINYINT NOT NULL,
			HasPk TINYINT NOT NULL
		)

	INSERT	@Constraints
		(
			RowID,
			Indent,
			[Catalog],
			[Schema],
			[Table],
			[Column],
			pkCatalog,
			pkSchema,
			pkTable,
			pkColumn,
			pkType,
			pkSize,
			IsSelfJoin,
			HasPk
		)
	SELECT	RowID,
		Indent,
		[Catalog],
		[Schema],
		[Table],
		[Column],
		pkCatalog,
		pkSchema,
		pkTable,
		pkColumn,
		pkType,
		pkSize,
		SelfJoin,
		CASE
			WHEN [Column] IS NULL THEN 0
			ELSE 1
		END
	FROM	dbo.fnTableTree(@Schema, @Table)

	IF @@ROWCOUNT = 0
		RETURN

	DECLARE	@SQL TABLE
		(
			ID INT IDENTITY(1, 1),
			RowID INT PRIMARY KEY CLUSTERED,
			IsSelfJoin TINYINT NOT NULL,
			HasPk TINYINT NOT NULL,
			[SQL] NVARCHAR(4000) NOT NULL
		)

	DECLARE	@Indent SMALLINT,
		@RowID INT,
		@ID INT,
		@TSQL NVARCHAR(4000),
		@RowSQL NVARCHAR(4000),
		@EndSQL NVARCHAR(4000),
		@pkColumn NVARCHAR(128),
		@IsSelfJoin TINYINT,
		@HasPk TINYINT

	DECLARE	@Unwind TABLE
		(
			RowID INT NOT NULL,
			StepID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
			[SQL] NVARCHAR(4000)
		)

	WHILE NOT EXISTS (SELECT * FROM @SQL WHERE RowID = 1)
		BEGIN
			SELECT TOP 1	@RowID = c.RowID,
					@ID = c.RowID,
					@Indent = c.Indent,
					@TSQL = N'',
					@EndSQL = N'',
					@IsSelfJoin = c.IsSelfjoin,
					@HasPk = c.HasPk
			FROM		@Constraints AS c
			LEFT JOIN	@SQL AS s ON s.RowID = c.RowID
			WHERE		s.RowID IS NULL
			ORDER BY	c.Indent DESC,
					c.RowID DESC

			WHILE @ID > 0
				BEGIN
					IF @Indent = 0
						SELECT	@RowSQL = N'DELETE t' + CAST(@RowID AS NVARCHAR(12)),
							@RowSQL = @RowSQL + N' FROM ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]) + N' AS t' + CAST(@ID AS NVARCHAR(12)),
							@EndSQL = N' WHERE t' + CAST(@ID AS NVARCHAR(12)) + '.' + QUOTENAME(COALESCE(c.[Column], '%0')) + N' = ''%1''',
							@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
						FROM	@Constraints AS c
						WHERE	c.RowID = @ID
					ELSE
						SELECT	@RowSQL = N' INNER JOIN ' + QUOTENAME(c.[Catalog]) + N'.' + QUOTENAME(c.[Schema]) + N'.' + QUOTENAME(c.[Table]),
							@RowSQL = @RowSQL + N' AS t' + CAST(@ID AS NVARCHAR(12)) + N' ON t' + CAST(@ID AS NVARCHAR(12)) + N'.' + QUOTENAME(c.[Column]),
							@pkColumn = QUOTENAME(c.pkColumn),
							@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
						FROM	@Constraints AS c
						WHERE	c.RowID = @ID

					SELECT TOP 1	@ID = c.RowID,
							@Indent = c.Indent,
							@RowSQL = @RowSQL + N' = t' + CAST(c.RowID AS NVARCHAR(12)) + N'.' + @pkColumn,
							@IsSelfJoin = @IsSelfJoin | c.IsSelfJoin
					FROM		@Constraints AS c
					WHERE		c.RowID < @ID
							AND c.Indent < @Indent
					ORDER BY	c.Indent DESC,
							c.RowID DESC

					IF @@ROWCOUNT = 0
						SET	@ID = 0

					SET	@TSQL = @RowSQL + @TSQL
				END

			INSERT	@SQL
				(
					RowID,
					IsSelfJoin,
					HasPk,
					[SQL]
				)
			VALUES	(
					@RowID,
					@IsSelfJoin,
					@HasPk,
					@TSQL + @EndSQL
				)

			IF @IsSelfJoin = 1
				BEGIN
					DECLARE	@Yak NVARCHAR(160),
						@Catalog NVARCHAR(128),
						@Column NVARCHAR(128)

					SELECT	@Yak  = pkType + COALESCE('(' + CAST(pkSize AS NVARCHAR(12)) + ')', ''),
						@Catalog = [Catalog],
						@Schema = [Schema],
						@Table = [Table],
						@Column = [Column],
						@Catalog = [Catalog],
						@Table = [Table],
						@pkColumn = pkColumn
					FROM	@Constraints
					WHERE	RowID = @RowID

SET	@RowSQL = 'DECLARE	@Lvl INT
SET	@Lvl = 0
DECLARE	@Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey ' + @Yak + ')
INSERT @Stage (Lvl, RowKey) '
+ REPLACE(@TSQL + @EndSQL, 'DELETE t' + CAST(@RowID AS NVARCHAR(12)) + '', 'SELECT 0, t' + CAST(@RowID AS NVARCHAR(12)) + '.' + QUOTENAME(@Column) + '')
+ ' WHILE @@ROWCOUNT > 0
	BEGIN
		SET	@Lvl = @Lvl + 1

		INSERT		@Stage  (Lvl, RowKey)
		SELECT		@Lvl,
				t.' + QUOTENAME(@pkColumn) + '
		FROM		' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' AS t
		INNER JOIN	@Stage AS s ON s.RowKey = t.' + QUOTENAME(@Column) + '
					AND s.Lvl = @Lvl - 1
		LEFT JOIN	@Stage AS cr ON cr.RowKey = t.' + QUOTENAME(@pkColumn) + '
		WHERE		cr.RowKey IS NULL
	END
SELECT ''DELETE FROM ' + QUOTENAME(@Catalog) + '.' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Table) + ' WHERE ' + QUOTENAME(@pkColumn) + ' = '' + QUOTENAME(RowKey, '''''''')
FROM @Stage
WHERE RowID > 0
ORDER BY RowID DESC'

				INSERT	@Unwind
					(
						RowID,
						[SQL]
					)
				VALUES	(
						@RowID,
						@RowSQL
					)
				END
		END

	INSERT		@Return
			(
				RowID,
				IsSelfJoin,
				HasPk,
				[SQL]
			)
	SELECT		s.ID,
			s.IsSelfJoin,
			s.HasPk,
			CASE
				WHEN u.RowID IS NULL THEN s.[SQL]
				ELSE u.[SQL]
			END
	FROM		@SQL AS s
	LEFT JOIN	@Unwind AS u ON u.RowID = s.RowID
	ORDER BY	s.ID,
			u.StepID

	RETURN
END

CREATE FUNCTION dbo.fnTableTree
(
	@Schema NVARCHAR(128) = NULL,
	@Table NVARCHAR(128) = NULL
)
RETURNS	@Tree TABLE
	(
		RowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
		Indent SMALLINT NOT NULL,
		[Catalog] NVARCHAR(128) NOT NULL,
		[Schema] NVARCHAR(128) NOT NULL,
		[Table] NVARCHAR(128) NOT NULL,
		[Column] NVARCHAR(128),
		[Type] NVARCHAR(128),
		[Size] INT,
		pkCatalog NVARCHAR(128),
		pkSchema NVARCHAR(128),
		pkTable NVARCHAR(128),
		pkColumn NVARCHAR(128),
		pkType NVARCHAR(128),
		pkSize INT,
		SelfJoin TINYINT NOT NULL
	)
AS
BEGIN
	DECLARE	@Temp TABLE
		(
			TempID INT IDENTITY(0, 1) PRIMARY KEY NONCLUSTERED,
			TempKey VARBINARY(8000),
			Indent SMALLINT,
			ConstraintID INT
		)

	DECLARE	@Constraints TABLE
		(
			ConstraintID INT IDENTITY(0, 1) PRIMARY KEY CLUSTERED,
			SelfJoin TINYINT NOT NULL DEFAULT 0,
			pkCatalog NVARCHAR(128) NOT NULL DEFAULT N'',
			pkSchema NVARCHAR(128) NOT NULL DEFAULT N'',
			pkTable NVARCHAR(128) NOT NULL DEFAULT N'',
			pkColumn NVARCHAR(128),
			pkType NVARCHAR(128),
			pkSize INT,
			fkCatalog NVARCHAR(128) NOT NULL,
			fkSchema NVARCHAR(128) NOT NULL,
			fkTable NVARCHAR(128) NOT NULL,
			fkColumn NVARCHAR(128),
			fkType NVARCHAR(128),
			fkSize INT
		)

	INSERT		@Constraints
			(
				fkCatalog,
				fkSchema,
				fkTable,
				fkColumn
			)
	SELECT		t.TABLE_CATALOG,
			t.TABLE_SCHEMA,
			t.TABLE_NAME,
			ccu.COLUMN_NAME
	FROM		INFORMATION_SCHEMA.TABLES AS t
	LEFT JOIN	INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tc ON tc.TABLE_CATALOG = t.TABLE_CATALOG
				AND tc.TABLE_SCHEMA = t.TABLE_SCHEMA
				AND tc.TABLE_NAME = t.TABLE_NAME
				AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
	LEFT JOIN	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS ccu ON ccu.CONSTRAINT_CATALOG = tc.CONSTRAINT_CATALOG
				AND ccu.CONSTRAINT_SCHEMA = tc.CONSTRAINT_SCHEMA
				AND ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
	WHERE		t.TABLE_TYPE = 'BASE TABLE'
			AND OBJECTPROPERTY(OBJECT_ID(t.TABLE_CATALOG + N'.' + t.TABLE_SCHEMA + N'.' + t.TABLE_NAME), 'IsMSShipped') = 0
			AND (@Schema IS NULL OR @Schema = t.TABLE_SCHEMA)
			AND (@Table IS NULL OR @Table = t.TABLE_NAME)
	ORDER BY	t.TABLE_CATALOG,
			t.TABLE_SCHEMA,
			t.TABLE_NAME

	INSERT		@Temp
			(
				Indent,
				ConstraintID
			)
	SELECT		0,
			ConstraintID
	FROM		@Constraints
	ORDER BY	ConstraintID

	INSERT		@Constraints
			(
				SelfJoin,
				pkCatalog,
				pkSchema,
				pkTable,
				pkColumn,
				pkType,
				pkSize,
				fkCatalog,
				fkSchema,
				fkTable,
				fkColumn,
				fkType,
				fkSize
			)
	SELECT		CASE
				WHEN pk.TABLE_CATALOG = fk.TABLE_CATALOG AND pk.TABLE_SCHEMA = fk.TABLE_SCHEMA AND pk.TABLE_NAME = fk.TABLE_NAME THEN 1
				ELSE 0
			END,
			pk.TABLE_CATALOG,
			pk.TABLE_SCHEMA,
			pk.TABLE_NAME,
			pk.COLUMN_NAME,
			pt.DATA_TYPE,
			pt.CHARACTER_MAXIMUM_LENGTH,
			fk.TABLE_CATALOG,
			fk.TABLE_SCHEMA,
			fk.TABLE_NAME,
			fk.COLUMN_NAME,
			ft.DATA_TYPE,
			ft.CHARACTER_MAXIMUM_LENGTH
	FROM		INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
	INNER JOIN	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS pk ON pk.CONSTRAINT_CATALOG = rc.UNIQUE_CONSTRAINT_CATALOG
				AND pk.CONSTRAINT_SCHEMA = rc.UNIQUE_CONSTRAINT_SCHEMA
				AND pk.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
	INNER JOIN	INFORMATION_SCHEMA.COLUMNS AS pt ON pt.TABLE_CATALOG = pk.TABLE_CATALOG
				AND pt.TABLE_SCHEMA = pk.TABLE_SCHEMA
				AND pt.TABLE_NAME = pk.TABLE_NAME
				AND pt.COLUMN_NAME = pk.COLUMN_NAME
	INNER JOIN	INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS fk ON fk.CONSTRAINT_CATALOG = rc.CONSTRAINT_CATALOG
				AND fk.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA
				AND fk.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
	INNER JOIN	INFORMATION_SCHEMA.COLUMNS AS ft ON ft.TABLE_CATALOG = fk.TABLE_CATALOG
				AND ft.TABLE_SCHEMA = fk.TABLE_SCHEMA
				AND ft.TABLE_NAME = fk.TABLE_NAME
				AND ft.COLUMN_NAME = fk.COLUMN_NAME
	ORDER BY	pk.TABLE_CATALOG,
			pk.TABLE_SCHEMA,
			pk.TABLE_NAME,
			pk.COLUMN_NAME,
			fk.TABLE_CATALOG,
			fk.TABLE_SCHEMA,
			fk.TABLE_NAME,
			fk.COLUMN_NAME

	UPDATE	@Temp
	SET	TempKey = CAST(TempID AS VARBINARY(4))

	DECLARE	@Indent SMALLINT

	SET	@Indent = 0

	WHILE @Indent < 2000 AND @@ROWCOUNT > 0 OR @Indent = 0
		BEGIN
			SET	@Indent = @Indent + 1

			INSERT		@Temp
					(
						TempKey,
						Indent,
						ConstraintID
					)
			SELECT		t.TempKey,
					@Indent,
					fk.ConstraintID
			FROM		@Temp AS t
			INNER JOIN	@Constraints AS pk ON pk.ConstraintID = t.ConstraintID
			INNER JOIN	@Constraints AS fk ON fk.pkCatalog = pk.fkCatalog
						AND fk.pkSchema = pk.fkSchema
						AND fk.pkTable = pk.fkTable
			WHERE		t.Indent = @Indent - 1
					AND NOT	(
							pk.pkCatalog = pk.fkCatalog
							AND pk.pkSchema = pk.fkSchema
							AND pk.pkTable = pk.fkTable
						)
			ORDER BY	fk.ConstraintID

			UPDATE	@Temp
			SET	TempKey = TempKey + CAST(TempID AS VARBINARY(4))
			WHERE	Indent = @Indent
		END

	INSERT		@Tree
			(
				Indent,
				SelfJoin,
				[Catalog],
				[Schema],
				[Table],
				[Column],
				[Type],
				[Size],
				pkCatalog,
				pkSchema,
				pkTable,
				pkColumn,
				pkType,
				pkSize
			)
	SELECT		t.Indent,
			c.SelfJoin,
			c.fkCatalog,
			c.fkSchema,
			c.fkTable,
			c.fkColumn,
			c.fkType,
			c.fkSize,
			NULLIF(c.pkCatalog, N''),
			NULLIF(c.pkSchema, N''),
			NULLIF(c.pkTable, N''),
			c.pkColumn,
			c.pkType,
			c.pkSize
	FROM		@Temp AS t
	INNER JOIN	@Constraints AS c ON c.ConstraintID = t.ConstraintID
	ORDER BY	t.TempKey,
			t.Indent

	RETURN
END

create table Test1
(
	a int primary key,
	b int constraint fk_test1 foreign key references Test1(a)
)

create table Test2
(
	a int primary key,
	b int constraint fk_test2 foreign key references Test1(a)
)

create table Test3
(
	a int primary key,
	b int constraint fk1_test3 foreign key references Test1(a),
	c int constraint fk2_test3 foreign key references Test2(a),
)

DELETE t4 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test2] AS t3 ON t3.[b] = t1.[a] INNER JOIN [Test].[dbo].[test3] AS t4 ON t4.[c] = t3.[a] WHERE t1.[a] = '%1'

DELETE t5 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test3] AS t5 ON t5.[b] = t1.[a] WHERE t1.[a] = '%1'

DELETE t3 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test2] AS t3 ON t3.[b] = t1.[a] WHERE t1.[a] = '%1'

DELETE t2 FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test1] AS t2 ON t2.[b] = t1.[a] WHERE t1.[a] = '%1'

DELETE t1 FROM [Test].[dbo].[test1] AS t1 WHERE t1.[a] = '%1'

Delete t3
From Test3 t3 JOIN Test1 t1 ON t3.b = t1.a
join Test2 t2 ON t3.c = t2.a
Where t3.a like '%1'

Delete t2
From Test2 t2 JOIN Test1 t1 ON t2.b = t1.a
Where t1.a like '%1'

Delete t1
From Test1 t1
Where t1.a like '%1'

DECLARE @Lvl INT  
SET @Lvl = 0  
DECLARE @Stage TABLE (RowID INT IDENTITY(0, 1), Lvl INT, RowKey int)  
INSERT @Stage (Lvl, RowKey) 
SELECT 0, t2.[b] 
FROM [Test].[dbo].[test1] AS t1 INNER JOIN [Test].[dbo].[test1] AS t2 
ON t2.[b] = t1.[a] 
WHERE t1.[a] = '%1' 

WHILE @@ROWCOUNT > 0   
BEGIN    
	SET @Lvl = @Lvl + 1      
	INSERT  @Stage  (Lvl, RowKey)    
	SELECT  @Lvl, t.ID    
	FROM  [Test].[dbo].[Table2] AS t INNER JOIN @Stage AS s ON s.RowKey = t.ParentID       
	AND s.Lvl = @Lvl - 1    
	LEFT JOIN @Stage AS cr ON cr.RowKey = t.ID    
	WHERE  cr.RowKey IS NULL   
END    

SELECT 'DELETE FROM [Test].[dbo].[Table2] WHERE [ID] = ' + QUOTENAME(RowKey, '''') + ''  FROM @Stage  ORDER BY RowID DESC

WHILE @@ROWCOUNT > 0   
BEGIN    
SET @Lvl = @Lvl + 1      
INSERT  @Stage  (Lvl, RowKey)    
SELECT  @Lvl,      t.ID    
FROM  [Test].[dbo].[test1] AS t    INNER JOIN @Stage AS s 
ON s.RowKey = t.ParentID AND s.Lvl = @Lvl - 1    
LEFT JOIN @Stage AS cr ON cr.RowKey = t.ID    
WHERE  cr.RowKey IS NULL   
END

quote:

---

Originally posted by harsh_athalye

SELECT 'DELETE FROM [Test].[dbo].[Table2] WHERE [ID] = ' + QUOTENAME(RowKey, '''') + ''  FROM @Stage  ORDER BY RowID DESC

---