SnippetName:
droptable

Description:
drop table command, enhanced to help you with foreign keys, schemabound objects,  and temporal tables

Code:
$SELECTIONSTART$
BEGIN TRY

	DROP TABLE [$SchemaName$].[$TableName$];

END TRY
BEGIN CATCH
    --foreign key constraints that reference this object
	SELECT CONCAT('ALTER TABLE ',referenced_table,
				  ' DROP CONSTRAINT ',QUOTENAME(constraints.name),
	              '; --Defined as: ',constraints.definition, CHAR(13), CHAR(10))
	FROM   (SELECT foreign_keys.name, 
					CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME(foreign_keys.parent_object_id)),'.',
							QUOTENAME(OBJECT_NAME(foreign_keys.parent_object_id))) AS referenced_table,
			        CONCAT('FK reference FROM ', QUOTENAME(OBJECT_SCHEMA_NAME(foreign_keys.parent_object_id)),'.',
							QUOTENAME(OBJECT_NAME(foreign_keys.parent_object_id))) AS definition,
				   foreign_keys.parent_object_id,
				   foreign_keys.referenced_object_id
			FROM   sys.foreign_keys) AS constraints
	WHERE OBJECT_SCHEMA_NAME(constraints.referenced_object_id) = '$SchemaName$'
	  AND OBJECT_NAME(constraints.referenced_object_id) = '$TableName$'

	UNION ALL
	
	--schemabound objects that reference the object in any way
	SELECT DISTINCT CONCAT('--WARNING: [',OBJECT_SCHEMA_NAME(sql_expression_dependencies.referencing_id),'].[',
				OBJECT_NAME(sql_expression_dependencies.referencing_id),
				'] has a schema bound reference to this object. Must be handled to drop')
	FROM   sys.sql_expression_dependencies
	WHERE  sql_expression_dependencies.is_schema_bound_reference = 1
	  AND  OBJECTPROPERTYEX(sql_expression_dependencies.referencing_id,'IsConstraint') = 0 --constraints aren't an issue for a drop table.
	  AND  OBJECT_SCHEMA_NAME(sql_expression_dependencies.referenced_id) = '$SchemaName$'
	  AND  OBJECT_NAME(sql_expression_dependencies.referenced_id) = '$TableName$';


	--commands to turn off system versioning and drop the history table.
	DECLARE @disableCommand nvarchar(1000), @dropHistoryTableCommand nvarchar(1000)
    SELECT @disableCommand = CONCAT('ALTER TABLE ',QUOTENAME(OBJECT_SCHEMA_NAME(tables.object_id)),'.', 
					  QUOTENAME(name), ' SET ( SYSTEM_VERSIONING = OFF);'),
			  
		  @dropHistoryTableCommand = 
				CONCAT('DROP TABLE ', QUOTENAME(OBJECT_SCHEMA_NAME(tables.history_table_id)),'.', 
			    QUOTENAME(OBJECT_NAME(tables.history_table_id)),';')
   
	FROM   sys.tables
	WHERE  tables.temporal_type_desc NOT IN ('NON_TEMPORAL_TABLE','HISTORY_TABLE')
		AND   name = '$TableName$'
		AND   OBJECT_SCHEMA_NAME(tables.object_id) = '$SchemaName$';

	PRINT '';
	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 '';


	IF @disableCommand is not null
	 BEGIN 
		PRINT 'Table is a temporal table, and cannot be simply dropped';
		PRINT '';
		PRINT '--code to turn off versioning for table';
		PRINT @disableCommand;
		PRINT '--code to drop history table';
		PRINT @dropHistoryTableCommand;
		PRINT '';
		PRINT '';
	 END;
	 
	THROW; --throw the error received. We may not have 100% of all reasons a table cannot be dropped yet

END CATCH;
$SELECTIONEND$