CREATE PROCEDURE usp_DropColumn
@TableName	Varchar(50),
@ColumnName	Varchar(50)
AS

/*====================================================================================================
NAME: 	usp_Dropcolumn						CREATED BY: SD
TYPE: 	stored procedure (SQL 7)				CREATION DATE: 08/01/01

USAGE:
DECLARE @TableName	Varchar(50)
DECLARE @ColumnName	Varchar(50)

SET @TableName = 'MyTable'
SET @ColumnName = 'MyColumn'

Exec usp_DropColumn @TableName, @ColumnName

PURPOSE	: To Drop a Column from a Table irrespective of the constraints 
defined on it.

Verifications:
	Check existence of table
	Check existence of column within table
	Check if this is the only column in the table
	Check existence of Default constraint on the column
	Check existence of Multiple Foreign Key constraints on the column
	Check existence of a Primary Key constraint on the column
	Check existence of Multiple Unique Constraint/Indexs on the column

NOTE : Only Table Owner or a dbo can Alter the Table.
=====================================================================================================*/
/*___________________________________________________________________________________________________*/


SET NOCOUNT ON

/* Declaring Variables	*/
Declare @Qry1		Varchar(1000)
Declare @Qry2		Varchar(1000)
Declare @Qry3		Varchar(1000)
Declare @Qry4		Varchar(1000)
Declare @Qry5		Varchar(1000)
Declare @Qry6		Varchar(1000)

Declare @DFName		Varchar(500)
Declare @FKName		Varchar(500)
Declare @PKFKName	Varchar(500)
Declare @Tablename2	Varchar(500)
Declare @PKName		Varchar(500)
Declare @UixName	Varchar(500)


BEGIN TRAN

-- Check Existence of Table.
if exists (select * from sysobjects where id = object_id(@TableName) and 
OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
	-- Check to see that the column already exist
	IF (SELECT COLUMNPROPERTY( OBJECT_ID(@TableName),@ColumnName,'AllowsNull')) 
IS NOT NULL
	Begin
		--Check to see if the column count in the table is more than 1.
		IF (	SELECT Count(*) FROM syscolumns sc INNER JOIN sysobjects so
			ON sc.id = so.id
			WHERE so.name = @TableName) <= 1
		BEGIN
			Raiserror('ERROR : %s is the only column in the table %s. It cannot be 
dropped',16,1, @ColumnName, @TableName)
			Rollback Tran
			Return(@@Error)
		END

		--Check to see if the column has a Default constraint defined on it.
		IF Exists (	SELECT syscolumns.* FROM syscolumns INNER JOIN sysobjects
				ON syscolumns.id = sysobjects.id
				INNER JOIN sysobjects so
				ON syscolumns.cdefault = so.id
				WHERE sysobjects.name = @TableName
				AND syscolumns.name = @ColumnName
				AND syscolumns.cdefault <> 0
			  )
		BEGIN
			SELECT @DFName = so.name
			FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
			INNER JOIN sysobjects so ON syscolumns.cdefault = so.id
			WHERE sysobjects.name = @TableName
			AND syscolumns.name = @ColumnName
			AND syscolumns.cdefault <> 0

			SET @Qry1 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @DFName
			Print 'Dropping Default constraint ' + @DFName
			Exec(@Qry1)

			IF @@Error <> 0
			BEGIN
				Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1, 
@DFName, @TableName)
				Rollback Tran
				Return(@@Error)
			END
			ELSE
				Print 'Default ' + @DFName + ' Dropped from ' + @TableName + ' Table.'
		END

		--Check to see if the column has a FK constraint defined on it.
		IF Exists (	SELECT so.name FROM syscolumns INNER JOIN sysobjects
				ON syscolumns.id = sysobjects.id
				INNER JOIN sysreferences
				ON syscolumns.id = sysreferences.fkeyid
				INNER JOIN sysobjects so
				ON sysreferences.constid = so.id
				WHERE sysobjects.name = @TableName
				AND syscolumns.name = @ColumnName
			  )
		BEGIN
			DECLARE FK_Cursor CURSOR  FOR
			SELECT so.name FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = 
sysobjects.id
			INNER JOIN sysreferences ON syscolumns.id = sysreferences.fkeyid
			INNER JOIN sysobjects so ON sysreferences.constid = so.id
			WHERE sysobjects.name = @TableName AND syscolumns.name = @ColumnName

			OPEN FK_Cursor
			FETCH NEXT FROM FK_Cursor
			INTO @FKName

			WHILE @@FETCH_STATUS = 0
			BEGIN
				SET @Qry2 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @FKName
				Print 'Dropping FK constraint ' + @FKName
				Exec(@Qry2)

				IF @@Error <> 0
				BEGIN
					Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1, 
@FKName, @TableName)
					Rollback Tran
					Return(@@Error)
				END
				ELSE
					Print 'FK ' + @FKname + ' Dropped from ' + @TableName + ' Table.'

				FETCH NEXT FROM FK_Cursor INTO @FKName
			END	/* WHILE @@FETCH_STATUS = 0	*/

			CLOSE FK_cursor
			DEALLOCATE FK_cursor
		END


		--Check to see if the column has a PK constraint defined on it.
		--This even takes care of Unique Constraints defined on the column.
		IF Exists (	SELECT so2.name
				FROM sysobjects so1
				INNER JOIN sysobjects so2
				ON so1.id = so2.Parent_Obj
				INNER JOIN sysindexes
				ON so2.name = sysindexes.name
				INNER JOIN sysindexkeys
				ON sysindexes.id = sysindexkeys.id
				AND sysindexes.indid = sysindexkeys.indid
				INNER JOIN syscolumns
				ON so1.id = syscolumns.id
				AND sysindexkeys.colid = syscolumns.colid
				WHERE so1.name = @TableName
				AND so2.xtype = 'PK'
				AND syscolumns.name = @ColumnName
			  )
		BEGIN
			-- Check to see if the PK is being Referenced by a FK
			IF Exists (	SELECT so.name
					FROM sysreferences
					INNER JOIN syscolumns
					ON sysreferences.rkeyid = syscolumns.id
					INNER join sysobjects
					ON sysreferences.rkeyid = sysobjects.id
					INNER JOIN sysobjects so
					ON sysreferences.constid = so.id
					WHERE syscolumns.name = @ColumnName
					AND sysobjects.name = @TableName
				  )
			BEGIN
				DECLARE PKFK_Cursor CURSOR  FOR
				SELECT so.name, so2.name
				FROM sysreferences INNER JOIN syscolumns
				ON sysreferences.rkeyid = syscolumns.id
				INNER join sysobjects ON sysreferences.rkeyid = sysobjects.id
				INNER JOIN sysobjects so ON sysreferences.constid = so.id
				INNER JOIN sysobjects so2
				ON sysreferences.fkeyid = so2.id
				WHERE syscolumns.name = @ColumnName AND sysobjects.name = @TableName

				OPEN PKFK_Cursor
				FETCH NEXT FROM PKFK_Cursor
				INTO @PKFKName, @TableName2

				WHILE @@FETCH_STATUS = 0
				BEGIN
					SET @Qry3 = 'ALTER TABLE ' + @TableName2 + ' DROP Constraint ' + 
@PKFKName
					Print 'Dropping FK constraint ' + @PKFKName
					Exec(@Qry3)

					IF @@Error <> 0
					BEGIN
						Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1, 
@PKFKName, @TableName2)
						Rollback Tran
						Return(@@Error)
					END
					ELSE
						Print 'FK ' + @PKFKname + ' Dropped from ' + @TableName2 + ' Table.'
					FETCH NEXT FROM PKFK_Cursor INTO @PKFKName, @TableName2
				END	/* WHILE @@FETCH_STATUS = 0	*/

				CLOSE PKFK_cursor
				DEALLOCATE PKFK_cursor
			END

			-- Drop the PK now
			SELECT @PKName = so2.name
			FROM sysobjects so1
			INNER JOIN sysobjects so2
			ON so1.id = so2.Parent_Obj
			INNER JOIN sysindexes
			ON so2.name = sysindexes.name
			INNER JOIN sysindexkeys
			ON sysindexes.id = sysindexkeys.id
			AND sysindexes.indid = sysindexkeys.indid
			INNER JOIN syscolumns
			ON so1.id = syscolumns.id
			AND sysindexkeys.colid = syscolumns.colid
			WHERE so1.name = @TableName
			AND so2.xtype = 'PK'
			AND syscolumns.name = @ColumnName


			SET @Qry4 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @PKName
			Print 'Dropping PK constraint ' + @PKName
			Exec(@Qry4)

			IF @@Error <> 0
			BEGIN
				Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1, 
@PKName, @TableName)
				RollBack Tran
				Return(@@Error)
			END
			ELSE
				Print 'PK ' + @PKname + ' Dropped from ' + @TableName + ' Table.'
		END


		--Check to see if the column has a Unique Index defined on it.
		IF Exists (	SELECT sysindexes.name
				FROM syscolumns INNER JOIN sysobjects				ON syscolumns.id = 
sysobjects.id
				INNER JOIN sysindexes
				ON sysobjects.id = sysindexes.id
				INNER JOIN sysindexkeys
				ON sysindexkeys.id = syscolumns.id
				AND sysindexkeys.colid = syscolumns.colid
				AND sysindexkeys.indid = sysindexes.indid
				WHERE sysobjects.name = @TableName
				AND syscolumns.name = @ColumnName
			  )		BEGIN
			DECLARE IX_Cursor CURSOR  FOR
			SELECT sysindexes.name
			FROM syscolumns INNER JOIN sysobjects
			ON syscolumns.id = sysobjects.id
			INNER JOIN sysindexes
			ON sysobjects.id = sysindexes.id
			INNER JOIN sysindexkeys
			ON sysindexkeys.id = syscolumns.id
			AND sysindexkeys.colid = syscolumns.colid
			AND sysindexkeys.indid = sysindexes.indid
			WHERE sysobjects.name = @TableName
			AND syscolumns.name = @ColumnName

			OPEN IX_Cursor
			FETCH NEXT FROM IX_Cursor
			INTO @UIXName

			WHILE @@FETCH_STATUS = 0
			BEGIN
				Print 'Dropping Index ' + @UIXName + ' from ' + @TableName + ' Table'
				-- If the Index Name is there in sysobjects then it's a Unique 
Constraint
				-- Otherwise it's a Unique Index.
				IF Exists(SELECT * FROM sysobjects WHERE name = @UIXName)
					SET @Qry5 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + 
@UIXName
				ELSE
					SET @Qry5 = 'DROP INDEX ' + @TableName + '.' + @UIXName

				Exec(@Qry5)

				IF @@Error <> 0
				BEGIN
					Raiserror('ERROR : Failed to Drop Index %s from %s Table',16,1, 
@UIXName, @TableName)
					Rollback Tran
					Return(@@Error)
				END
				ELSE
					Print 'Index ' + @UIXName + ' removed from ' + @TableName + ' table'
				FETCH NEXT FROM IX_Cursor INTO @UIXName
			END

			CLOSE IX_cursor
			DEALLOCATE IX_cursor
		END


		-- Drop the Column Now. All the constraints have been removed.
		SET @Qry6 = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName
		Exec(@Qry6)

		IF @@Error <> 0
		BEGIN
			Raiserror('ERROR : Failed to Drop column %s from %s Table',16,1, 
@ColumnName, @TableName)
			Rollback Tran
			Return(@@Error)
		END
		ELSE
			Print 'Column ' + @ColumnName + ' removed from ' + @TableName + ' table'
	End
	Else
	BEGIN
		Raiserror('ERROR : Column %s does not exist in %s Table',16,1, 
@ColumnName, @TableName)
		Rollback Tran
		Return(@@Error)
	END
END
ELSE
BEGIN
	Raiserror('ERROR : Table %s does not Exist',16,1, @TableName)
	Rollback Tran
	Return(@@Error)
END


COMMIT TRAN
SET NOCOUNT OFF
GO