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