Script To drop the dependets on a column

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



		
CREATE PROCEDURE [dbo].[DropDependents]
(
	@NameSpace NVARCHAR(100),
	@ObjectName NVARCHAR(100),
	@AttributeName NVARCHAR(100)
)
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
DECLARE @ConstraintCount INT
DECLARE @ConstraintLoop INT
DECLARE @TranCount INT
DECLARE @ConstraintName NVARCHAR(100)
DECLARE @TABLE_SCHEMA NVARCHAR(100)
DECLARE @FK_Table NVARCHAR(100)

DECLARE @DropConstraintQuery NVARCHAR(4000)
DECLARE @SelectIndexQuery NVARCHAR(2000)
DECLARE @DropIndexQuery NVARCHAR(2000)
DECLARE @IndexCount INT
DECLARE @IndexLoop INT
DECLARE @IndexName NVARCHAR(1000)
-- Error Handling Variables
DECLARE @ErrorMessage NVARCHAR(4000)
DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorLine INT
DECLARE @ErrorProcedure NVARCHAR(200)

CREATE TABLE #Constraints
(
	ID INT IDENTITY(1,1),
	ConstraintName SYSNAME,
	TABLE_SCHEMA NVARCHAR(256),
	FK_Table SYSNAME,
	FK_Column SYSNAME,
	PK_Table SYSNAME,
	PK_Column SYSNAME
)

CREATE TABLE #HelpIndexes
(
	IndexName NVARCHAR(1000),
	IndexDesc NVARCHAR(2000),
	IndexKeys NVARCHAR(2000),
)

CREATE TABLE #Indexes
(
	ID INT IDENTITY(1,1),
	IndexName NVARCHAR(1000),
	IndexDesc NVARCHAR(2000),
	IndexKeys NVARCHAR(2000),
)

	SET @TranCount = @@TranCount

IF @TranCount  = 0 
BEGIN TRAN

	TRUNCATE TABLE #Constraints	

	INSERT INTO #Constraints(ConstraintName,TABLE_SCHEMA,FK_Table,FK_Column,PK_Table,PK_Column)	
			SELECT 
					OBJECT_NAME(CONSTRAINT_OBJECT_ID) AS ConstraintName,
					CTU.TABLE_SCHEMA,
					OBJECT_NAME(PARENT_OBJECT_ID) AS FK_Table,
					COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID) AS FK_Column,
					OBJECT_NAME(REFERENCED_OBJECT_ID) AS PK_Table,
					COL_NAME(REFERENCED_OBJECT_ID,REFERENCED_COLUMN_ID) AS PK_Column 
				FROM SYS.FOREIGN_KEY_COLUMNS FKC 
				INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU 
					ON CTU.CONSTRAINT_NAME = OBJECT_NAME(CONSTRAINT_OBJECT_ID) 
--					WHERE OBJECT_NAME(REFERENCED_OBJECT_ID)  = @ObjectName
--							AND COL_NAME(REFERENCED_OBJECT_ID,REFERENCED_COLUMN_ID) =  @AttributeName 
					WHERE OBJECT_NAME(PARENT_OBJECT_ID)    = @ObjectName
							AND COL_NAME(PARENT_OBJECT_ID,PARENT_COLUMN_ID) =  @AttributeName 


			SELECT @ConstraintCount = @@ROWCOUNT, @ConstraintLoop = 1

			SELECT @DropConstraintQuery = '',@ConstraintName = '',@TABLE_SCHEMA = '',@FK_Table = ''
			WHILE @ConstraintLoop  < = @ConstraintCount 
				BEGIN
					SELECT @ConstraintName = ConstraintName,@TABLE_SCHEMA = TABLE_SCHEMA,
						   @FK_Table = FK_Table 
					 FROM #Constraints WHERE ID = @ConstraintLoop  
					SET @DropConstraintQuery = 'ALTER TABLE ' + @TABLE_SCHEMA + '.' + @FK_Table +
											   ' DROP CONSTRAINT ' + @ConstraintName 

					EXEC(@DropConstraintQuery) 	

					SET @ConstraintLoop  = @ConstraintLoop   + 1
				END

-- Drop Indexes Which Are Related to the Given Attriibute
		SELECT @SelectIndexQuery = ''
		INSERT INTO #HelpIndexes
				EXEC ('Sp_Helpindex ''' + @NameSpace + '.' + @ObjectName + '''')

		SET @SelectIndexQuery = 'SELECT * FROM #HelpIndexes WHERE IndexKeys LIKE ''%' + @AttributeName + '%'''

		INSERT INTO #Indexes (IndexName ,IndexDesc ,IndexKeys)
				EXEC(@SelectIndexQuery)

		SELECT @IndexCount = @@RowCount , @IndexLoop = 1
		
		WHILE @IndexLoop < = @IndexCount 
		BEGIN
			SELECT @DropIndexQuery = ''
			SELECT @IndexName = IndexName FROM #Indexes WHERE ID = @IndexLoop 
			SET @DropIndexQuery = 'DROP INDEX ' + @NameSpace + '.' + @ObjectName + '.' + @IndexName
			--PRINT @DropIndexQuery 
			EXEC(@DropIndexQuery)

			SET @IndexLoop  =  @IndexLoop  + 1
		END

END TRY

BEGIN CATCH

SELECT 
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorLine = ERROR_LINE(),
		@ErrorMessage = ERROR_MESSAGE()  + ' - [Widds].[DropDependents]',
        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

	    RAISERROR ( @ErrorMessage, @ErrorSeverity,1,@ErrorNumber,@ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine);

		IF @@TranCount > @TranCount
				ROLLBACK TRAN
		RETURN -1

END CATCH

IF @@TranCount > @TranCount
	COMMIT TRAN

RETURN 0

END