Generate Delete Stored Procedure

,

This procedure will generate a delete stored procedure against a provided table and column name in your database.  It will search for foriegn key constraints and create a delete statement for each one found in the database.  The procedure has three parameters:
@table_name nvarchar(128) = the name of the source table
@column_name nvarchar(128) = the name of the source column
@new_proc_name nvarchar(128) = the name of the new procedure that will be generated.

Output the results from this procedure to text (or file) and then copy the results in order to create the procedure. 

Please study the resulting delete statements to make sure that they do not violate the rules of your database.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ecm_GENERATE_DELETE_PROC]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ecm_GENERATE_DELETE_PROC]
GO







CREATE                PROCEDURE ecm_GENERATE_DELETE_PROC   
@table_name nvarchar(128),  /*TABLE NAME TO DELETE FROM*/
@column_name nvarchar(128),  /*COLUMN NAME THAT IS THE KEY*/
@new_proc_name nvarchar(128) /*THE NAME OF THE NEW PROCEDURE*/

   
AS   
   
SET NOCOUNT ON   
   
DECLARE   
	@current_table nvarchar(128),   
	@current_column nvarchar(128),  
	@del_proc1 varchar(8000),  
	@rowid int,   
	@set_warning bit,   
	@warning nvarchar(255)  
   
BEGIN  

--error checking
IF @table_name NOT IN
			(SELECT TABLE_NAME
			FROM INFORMATION_SCHEMA.Tables)
	BEGIN ---invalid table name
		PRINT 'ERROR: NO TABLE NAMED: ' + '''' + @table_name + '''' + ' IN DATABASE: ' + '''' + db_name() + ''''
		RETURN
	END ---invalid table name

IF @column_name NOT IN
			(SELECT COLUMN_NAME
			FROM INFORMATION_SCHEMA.Columns
			WHERE TABLE_NAME = @table_name)
	BEGIN ---invalid column name
		PRINT 'ERROR: NO COLUMN NAMED: ' + '''' + @column_name + '''' +  ' IN TABLE: ' + '''' + @table_name + '''' + ' IN DATABASE: ' + '''' + db_name() + ''''
		RETURN
	END ---invalid column name
  
CREATE TABLE #tmp_del_builder  
	(rowid int identity(1,1),   
	ref_table nvarchar(128),   
	ref_column nvarchar(128),   
	child_table nvarchar(128),   
	child_column nvarchar(128),   
	used bit)  
  
	INSERT INTO #tmp_del_builder  
		(ref_table, 
		ref_column, 
		child_table, 
		child_column, 
		used)  
	SELECT    
		@table_name as 'Referenced Table',    
		f.name as 'Referenced Column',   
		c.name as 'Tables That Reference',    
		e.name as 'Referencing Column',   
		0  
	FROM sysobjects a    
		JOIN sysforeignkeys b   
		on a.id = b.rkeyid    
		JOIN sysobjects c   
		on c.id = b.fkeyid   
		JOIN sysobjects d   
		on d.id = b.constid   
		JOIN syscolumns f   
		on f.id = a.id   
		AND b.rkey = f.colid   
		JOIN syscolumns e   
		on c.id = e.id    
		AND b.fkey = e.colid   
	WHERE a.name = @table_name    
	ORDER BY c.name   
  
SELECT @del_proc1 =  
				' CREATE PROCEDURE ' + @new_proc_name + char(13) + char(10) +
		  	 	' @' + @column_name + ' varchar(128) ' + char(13) + char(10) + ' AS ' + char(13) + char(10) + 
				'SET NOCOUNT ON ' + char(13) + char(10) + ' BEGIN ' + char(13) + char(10) +
				' BEGIN TRANSACTION ' + char(13)  + char(10)

--print out the create procedure block
SELECT @del_proc1

SET @del_proc1 = ''
WHILE EXISTS   
		(SELECT * 
		FROM #tmp_del_builder 
		WHERE used = 0)  
BEGIN --- loop  
	SELECT @set_warning = 0  
  
	SELECT TOP 1   
		@current_table = child_table,   
		@current_column = child_column,   
		@rowid = rowid  
	FROM   
		#tmp_del_builder  
	WHERE   
		used = 0  
  
	IF OBJECT_ID(@current_table) IN   
				(SELECT [rkeyid] FROM sysforeignkeys)  
		BEGIN---warn of other foreign key   
		SELECT @warning = ' /***WARNING: Table ' + @current_table + ' has other foreign key constraints.***/ '  
		SELECT @set_warning = 1  
		END  
  
	SELECT @del_proc1 =    
			' DELETE FROM ' + @current_table + char(13) + char(10) + 
			' WHERE ' + @current_column + ' = @' + @column_name + char(13) + char(10) +  
			' IF @@ERROR! = 0 ' + char(13) + char(10) +'  ROLLBACK ' + char(13)  + char(10)
	--if possible other constraints on deleted columns, then print warning.
	IF @set_warning = 1  
		SELECT @del_proc1 = @del_proc1 + @warning + char(13)  + char(10) + char(13) + char(10)
	ELSE  
		SELECT @del_proc1 = @del_proc1 + char(13)  + char(10)
  
		UPDATE #tmp_del_builder  
		SET used = 1   
	 	WHERE rowid = @rowid  
  
--print out the foreign key delete blocks
SELECT @del_proc1

END --- loop  
  
SELECT @del_proc1 = 
			' DELETE FROM ' + @table_name + 
			' WHERE ' + @column_name + ' = @' + @column_name + char(13) + char(10) +char(13) + char(10) + 
			'IF @@ERROR! = 0 ' + char(13) + char(10) +'ROLLBACK ' + char(13) + char(10) +'ELSE ' + char(13) + char(10) +
			'COMMIT ' + char(13) + char(10) +'END'  
--print out the last delete statement and the commit to close the procedure
SELECT @del_proc1

END 
   




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate