SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE  procedure [dbo].[p_sys_drop_column]
	@table_name nvarchar(128),
	@column_name nvarchar(128)

as 

if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table_name and COLUMN_NAME = @column_name)
begin


	-- first delete all foreign key constraints that are using the column

	DECLARE @SQL VARCHAR(MAX)=''
	SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(ccu.TABLE_SCHEMA) + '.' + QUOTENAME(ccu.TABLE_NAME) + ' DROP CONSTRAINT [' + RTRIM(ccu.constraint_name) +'];' + CHAR(13)

		/*
		SELECT 
			ccu.table_name AS SourceTable
			,ccu.constraint_name AS SourceConstraint
			,ccu.column_name AS SourceColumn
			,kcu.table_name AS TargetTable
			,kcu.column_name AS TargetColumn, */
	FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
		INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
			ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME 
		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu 
			ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME  
	where ccu.TABLE_NAME = @table_name and ccu.COLUMN_NAME = @column_name

	if len(@SQL) > 0
	begin
		print @SQL
		exec(@SQL)
	end 
	--then delete column
	set @SQL = ''

	SELECT @SQL = @SQL + 'ALTER TABLE ' + QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME) + ' DROP COLUMN ' + QUOTENAME(@column_name) +  ';' + CHAR(13)
	from INFORMATION_SCHEMA.TABLES t
	where TABLE_NAME = @table_name

	print @SQL
	exec(@SQL)

	
end 

/*
exec p_sys_drop_column 'pr_products_branches', 'prdb_monthly_payment_code'
*/
GO
