SnippetName:
dropcolumn

Description:
drop column, providing code to identify and usually remove dependencies if they exist

Code:
$SELECTIONSTART$
BEGIN TRY

	 ALTER TABLE [$SchemaName$].[$TableName$]
		DROP COLUMN [$ColumnName$];

END TRY
BEGIN CATCH
		--constraints that are attached to column
		SELECT CONCAT('ALTER TABLE ',QUOTENAME('$SchemaName$'),'.',QUOTENAME('$TableName$'),
		              ' DROP CONSTRAINT ',QUOTENAME(constraints.name),
		              '; --Defined as: ',constraints.definition, CHAR(13), CHAR(10))
		FROM   (--check constraints
				SELECT NULL AS column_name, name, check_constraints.object_id, check_constraints.parent_object_id, 
					  check_constraints.parent_column_id, check_constraints.definition
				FROM   sys.check_constraints
				UNION ALL
				--foreign keys, in that this column shows up in a reference to a table
				SELECT columns.name, foreign_keys.name, foreign_key_columns.constraint_object_id, 
				       foreign_key_columns.parent_object_id, foreign_key_columns.parent_column_id, 
				       CONCAT('Column in FK reference to: ',OBJECT_SCHEMA_NAME(foreign_key_columns.parent_object_id), 
				               '.',OBJECT_NAME(foreign_key_columns.parent_object_id))
				FROM   sys.foreign_key_columns
						JOIN sys.foreign_keys
							ON foreign_keys.object_id = foreign_key_columns.constraint_object_id
						JOIN sys.columns
							ON columns.object_id = foreign_key_columns.parent_object_id
							  AND columns.column_id = foreign_key_columns.parent_column_id
				UNION ALL
				--default constraints
				SELECT columns.name AS column_name, default_constraints.name, default_constraints.object_id, 
				       default_constraints.parent_object_id, default_constraints.parent_column_id, default_constraints.definition
				FROM   sys.default_constraints
						JOIN sys.columns
							ON default_constraints.parent_object_id = columns.object_id
									AND default_constraints.parent_column_id = columns.column_id) AS constraints

		WHERE OBJECT_SCHEMA_NAME(constraints.parent_object_id) = '$SchemaName$'
		  AND OBJECT_NAME(constraints.parent_object_id) = '$TableName$'
		  AND (constraints.column_name = '$ColumnName$'
		  		  --table constraints don't have a direct link.. And it saves the definition
		  		  --with the columns in brackets
			   OR constraints.definition LIKE '%`[$ColumnName$`]%' ESCAPE '`')
		
		UNION ALL

		--indexes that this column is a key or include member of
		SELECT CASE WHEN indexes.is_unique_constraint = 1 OR indexes.is_primary_key = 1 THEN
				--for PK or UNIQUE constraints, it is an alter
				CONCAT('ALTER TABLE ', OBJECT_SCHEMA_NAME(indexes.object_id),'.',
						OBJECT_NAME(indexes.object_id),
		              ' DROP CONSTRAINT ',QUOTENAME(indexes.name),
		              '--Key Cols:',STRING_AGG(QUOTENAME(CASE WHEN sys.index_columns.is_included_column = 0 THEN columns.name END), ','))
				ELSE
				--or drop 
				CONCAT( 'DROP INDEX ', QUOTENAME(indexes.name),' ON  ', OBJECT_SCHEMA_NAME(indexes.object_id),'.',
						OBJECT_NAME(indexes.object_id),'--Key Cols:',STRING_AGG(QUOTENAME(CASE WHEN sys.index_columns.is_included_column = 0 THEN columns.name END), ',') ,
						' Incl Cols: ',STRING_AGG(QUOTENAME(CASE WHEN sys.index_columns.is_included_column = 1 THEN columns.name END), ',') )
                 END
		FROM   sys.indexes
					JOIN sys.index_columns
					ON index_columns.object_id = indexes.object_id
						AND index_columns.index_id = indexes.index_id
					JOIN sys.columns
					ON columns.column_id = index_columns.column_id
						AND columns.object_id = index_columns.object_id
		WHERE  OBJECT_SCHEMA_NAME(indexes.object_id) = '$SchemaName$'
			AND  OBJECT_NAME(indexes.object_id) = '$TableName$'
		GROUP BY indexes.name, indexes.object_id, indexes.is_unique_constraint, indexes.is_primary_key
		HAVING STRING_AGG(QUOTENAME(columns.name), ',') LIKE '%`[$ColumnName$`]%' ESCAPE '`'

		UNION ALL
		
		--Schema Bound References... Warning only
		SELECT DISTINCT CONCAT('--ATTENTION: ',OBJECT_SCHEMA_NAME(sql_expression_dependencies.referencing_id),'.',
					OBJECT_NAME(sql_expression_dependencies.referencing_id),' has a schema bound reference to this column. This script does not drop.')
		FROM   sys.sql_expression_dependencies
				JOIN sys.columns
					ON sql_expression_dependencies.referenced_id = columns.object_id
						AND sql_expression_dependencies.referenced_minor_id = columns.column_id
		WHERE  sql_expression_dependencies.is_schema_bound_reference = 1
			AND  OBJECTPROPERTYEX(sql_expression_dependencies.referencing_id,'IsConstraint') = 0
			AND  OBJECT_SCHEMA_NAME(sql_expression_dependencies.referenced_id) = '$SchemaName$'
			AND  OBJECT_NAME(sql_expression_dependencies.referenced_id) = '$TableName$'
			AND columns.name = '$ColumnName$'; --only care about direct access, and non * because * is not allowed in 
												--schemabound object
		
		PRINT 'Assistance for dropping this column is output in a result set. The error message may have more details'
		PRINT 'that you have to work through. This script is not guaranteed to cover all situations and scenarios'
		PRINT 'and review the code before executing it. No guarantee is provided of the safety of this code.'
		PRINT '';
		THROW; --throw the error received. We may not have 100% of all reasons a table cannot be dropped yet
		
END CATCH;
$SELECTIONEND$