Create a sandbox for a SQL database using views and triggers

create_sandbox.sql

CREATE PROCEDURE [dbo].[create_sandbox_for_schema]
	@schema_name nvarchar(max),
	@sandbox_name nvarchar(max)
AS
BEGIN
	DECLARE sandbox_statements CURSOR FOR
	WITH primary_key_columns AS
	(
		SELECT key_constraints.parent_object_id,
			columns.name,
			columns.column_id
		FROM sys.key_constraints
		INNER JOIN sys.index_columns
		ON key_constraints.parent_object_id = index_columns.object_id AND key_constraints.unique_index_id = index_columns.index_id
		INNER JOIN sys.columns
		ON index_columns.object_id = columns.object_id AND index_columns.column_id = columns.column_id
		WHERE key_constraints.[type] = 'PK'
	),
	column_definitions AS
	(
		SELECT columns.column_id,
			columns.object_id,
			CASE WHEN columns.is_computed = 1
				THEN REPLACE(REPLACE(',
					[%ColumnName%] AS %Definition%',
					'%ColumnName%', columns.name),
					'%Definition%', computed_columns.[definition] COLLATE Latin1_General_100_CI_AS_KS_WS_SC)
				ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(',
					[%ColumnName%] %Type%%TypePrecision% %Nullable%%Identity%',
					'%ColumnName%', columns.name),
					'%Type%', UPPER(types.name)),
					'%TypePrecision%',
						CASE WHEN types.name IN ('varchar', 'char', 'varbinary')
							THEN REPLACE('({0})', '{0}', CASE WHEN columns.max_length = -1 THEN 'MAX' ELSE CAST(columns.max_length AS NVARCHAR(5)) END)
							WHEN types.name IN ('nvarchar', 'nchar')
							THEN REPLACE('({0})', '{0}', CASE WHEN columns.max_length = -1 THEN 'MAX' ELSE CAST(columns.max_length / 2 AS NVARCHAR(5)) END)
							WHEN types.name IN ('datetime2', 'time2', 'datetimeoffset') 
							THEN REPLACE('({0})', '{0}', CAST(columns.scale AS VARCHAR(5)))
							WHEN types.name = 'decimal' 
							THEN REPLACE(REPLACE('({0},{1})', '{0}', CAST(columns.[precision] AS NVARCHAR(5))), '{1}', CAST(columns.scale AS NVARCHAR(5)))
							ELSE ''
						END),
					'%Nullable%', CASE WHEN columns.is_nullable = 1 THEN 'NULL' ELSE 'NOT NULL' END),
					'%Identity%',
						CASE WHEN columns.is_identity = 1
						THEN REPLACE(REPLACE(
							' IDENTITY(%Seed%,%Increment%)',
							'%Seed%', CAST(IDENT_CURRENT(tables.name) AS NVARCHAR(MAX))),
							'%Increment%', CAST(IDENT_INCR(tables.name) AS NVARCHAR(MAX)))
						ELSE ''
						END)
					END AS column_definition
		FROM sys.columns
		INNER JOIN 
		(
			SELECT tables.object_id,
				REPLACE(REPLACE('[%Schema%].[%Table%]', '%Schema%', @schema_name), '%Table%', tables.name) AS name
			FROM sys.tables
		) AS tables ON columns.object_id = tables.object_id
		INNER JOIN sys.types ON columns.user_type_id = types.user_type_id
		LEFT JOIN sys.computed_columns ON columns.object_id = computed_columns.object_id AND columns.column_id = computed_columns.column_id
		LEFT JOIN sys.default_constraints ON columns.default_object_id != 0 AND columns.object_id = default_constraints.parent_object_id AND columns.column_id = default_constraints.parent_object_id
		LEFT JOIN sys.identity_columns
		ON columns.is_identity = 1
			 AND columns.object_id = identity_columns.object_id
			 AND columns.column_id = identity_columns.column_id
	),
	sandbox AS
	(
		SELECT 
			REPLACE(REPLACE(REPLACE('
				CREATE TABLE [%SandboxName%].[%Table%_inserted_row]
				(
					%InsertedRowColumnDefinitions%
				)',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%InsertedRowColumnDefinitions%', columns.inserted_row_column_definitions) AS create_inserted_rows_table_sql,
			REPLACE(REPLACE(REPLACE('
				CREATE TABLE [%SandboxName%].[%Table%_updated_row]
				(
					%UpdatedRowColumnDefinitions%
				)',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%UpdatedRowColumnDefinitions%', columns.updated_row_column_definitions) AS create_updated_rows_table_sql,
			REPLACE(REPLACE(REPLACE('
				CREATE TABLE [%SandboxName%].[%Table%_deleted_row]
				(
					%DeletedRowColumnDefinitions%
				)',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%DeletedRowColumnDefinitions%', columns.deleted_row_column_definitions) AS create_deleted_rows_table_sql,
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
				CREATE VIEW [%SandboxName%].[%Table%]
				AS
				SELECT %AllRows%
				FROM [%Schema%].[%Table%]
				LEFT JOIN [%SandboxName%].[%Table%_updated_row] AS updated_row ON %UpdatedTableJoin%
				LEFT JOIN [%SandboxName%].[%Table%_deleted_row] AS deleted_row ON %DeletedTableJoin%
				WHERE %DeletedRowKeyIsNotNull%
				UNION ALL
				SELECT %AllColumnNames%
				FROM [%SandboxName%].[%Table%_inserted_row]',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%AllRows%', columns.all_rows),
				'%Schema%', @schema_name),
				'%UpdatedTableJoin%', columns.updated_table_join),
				'%DeletedTableJoin%', columns.deleted_table_join),
				'%DeletedRowKeyIsNotNull%', columns.deleted_row_key_is_not_null),
				'%AllColumnNames%', columns.all_column_names) AS create_view_sql,
			REPLACE(REPLACE(REPLACE('
				CREATE UNIQUE CLUSTERED INDEX [IX_%Table%]
					ON [%SandboxName%].[%Table%] (%KeyColumnNames%))',
				'%Table%', tables.name),
				'%SandboxName%', @sandbox_name),
				'%KeyColumnNames%', columns.key_column_names) AS create_primary_key_constraint,
			REPLACE(REPLACE(REPLACE('
				CREATE TRIGGER [%Table%InsertTrigger] ON [%SandboxName%].[%Table%]
				INSTEAD OF INSERT
				AS
				BEGIN
					INSERT INTO [%SandboxName%].[%Table%_inserted_row] (%ColumnNames%)
					SELECT %ColumnNames%
					FROM INSERTED
				END',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%ColumnNames%', columns.column_names) AS create_insert_trigger_sql,
			REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
				CREATE TRIGGER [%Table%UpdateTrigger] ON [%SandboxName%].[%Table%]
				INSTEAD OF UPDATE
				AS
				BEGIN
					MERGE INTO [%SandboxName%].[%Table%_updated_row] AS updated_row
					USING INSERTED
					ON %InsertedTableJoin%
					WHEN MATCHED THEN UPDATE SET %UpdateColumns%
					WHEN NOT MATCHED THEN INSERT (%ColumnNames%) VALUES (%ColumnNames%)
				END',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%InsertedTableJoin%', columns.inserted_table_join),
				'%UpdateColumns%', columns.update_columns),
				'%ColumnNames%', columns.column_names) AS create_update_trigger_sql,
			REPLACE(REPLACE(REPLACE('
				CREATE TRIGGER [%Table%DeleteTrigger] ON [%SandboxName%].[%Table%]
				INSTEAD OF DELETE
				AS
				BEGIN
					INSERT INTO [%SandboxName%].[%Table%_deleted_row] (%KeyColumnNames%)
					SELECT %KeyColumnNames%
					FROM DELETED
				END',
				'%SandboxName%', @sandbox_name),
				'%Table%', tables.name),
				'%KeyColumnNames%', columns.key_column_names) AS create_delete_trigger_sql,
			(
				SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE('
					CREATE %Unique%NONCLUSTERED INDEX [%Index%] ON [%SandboxName%].[%Table%] (%ColumnNames%)%IncludeColumnNames%',
					'%Unique%', CASE WHEN indexes.is_unique = 1 THEN 'UNIQUE ' ELSE '' END),
					'%Index%', indexes.name),
					'%SandboxName', @sandbox_name),
					'%Table%', tables.name),
					'%ColumnNames%',
						STUFF
						(
							(
								SELECT REPLACE(REPLACE(
									', [%ColumnName%] %SortOrder%',
									'%ColumnName%', columns.name),
									'%SortOrder', CASE WHEN index_columns.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END)
								FROM sys.columns
								INNER JOIN sys.index_columns
								ON columns.object_id = index_columns.object_id
									AND columns.column_id = index_columns.column_id
									AND index_columns.is_included_column = 0
									AND index_columns.index_id = indexes.index_id
								FOR XML PATH(''), TYPE
							).value('.', 'NVARCHAR(MAX)'),
							1, 2, ''
						)
					),
					'%IncludeColumnNames%', 
						ISNULL(REPLACE(
							'INCLUDE (%ColumnNames%)',
							'%ColumnNames%',
								STUFF
								(
									(
										SELECT REPLACE(', [%ColumnName%]', '%ColumnName%', columns.name)
										FROM sys.columns
										INNER JOIN sys.index_columns
										ON columns.object_id = index_columns.object_id
											AND columns.column_id = index_columns.column_id
											AND index_columns.is_included_column = 1
											AND index_columns.index_id = indexes.index_id
										FOR XML PATH(''), TYPE
									).value('.', 'NVARCHAR(MAX)'),
									1, 2, ''
								)
						), '')
					)
				FROM sys.indexes
				WHERE indexes.object_id = tables.object_id AND indexes.is_primary_key = 0 AND indexes.[type] = 2
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)') AS create_indexes_sql
		FROM sys.tables
		INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
		INNER JOIN
		(
			SELECT tables.object_id,
				STUFF
				(
					(
						SELECT column_definition
						FROM column_definitions
						WHERE object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS inserted_row_column_definitions,
				STUFF
				(
					(
						SELECT column_definition
						FROM column_definitions
						WHERE object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS updated_row_column_definitions,
				STUFF
				(
					(
						SELECT column_definition
						FROM column_definitions
						INNER JOIN primary_key_columns
						ON column_definitions.column_id = primary_key_columns.column_id
							AND column_definitions.object_id = primary_key_columns.parent_object_id
						WHERE column_definitions.object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS deleted_row_column_definitions,
				STUFF
				(
					(
						SELECT REPLACE(REPLACE(REPLACE(', 
							CASE
								WHEN updated_row.[%UpdatedRowKeyColumnName%] IS NOT NULL
								THEN updated_row.[%ColumnName%]
								ELSE [%Table%].[%ColumnName%]
							END AS %ColumnName%',
							'%Table%', tables.name),
							'%ColumnName%', columns.name),
							'%UpdatedRowKeyColumnName%', (
								SELECT TOP 1 primary_key_columns.name
								FROM primary_key_columns
								WHERE parent_object_id = tables.object_id
								ORDER BY column_id
							))
						FROM sys.columns
						WHERE columns.object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS all_rows,
				STUFF
				(
					(
						SELECT REPLACE(REPLACE(
							' AND [%Table%].[%ColumnName%] = updated_row.[%ColumnName%]',
							'%Table%', tables.name),
							'%ColumnName%', primary_key_columns.name)
						FROM primary_key_columns
						WHERE parent_object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 5, ''
				) AS updated_table_join,
				STUFF
				(
					(
						SELECT REPLACE(REPLACE(
							' AND [%Table%].[%ColumnName%] = deleted_row.[%ColumnName%]',
							'%Table%', tables.name),
							'%ColumnName%', primary_key_columns.name)
						FROM primary_key_columns
						WHERE parent_object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 5, ''
				) AS deleted_table_join,
				(
					SELECT TOP 1 REPLACE(
						'deleted_row.[%ColumnName%] IS NOT NULL',
						'%ColumnName%', primary_key_columns.name)
					FROM primary_key_columns
					WHERE parent_object_id = tables.object_id
					ORDER BY column_id
				) AS deleted_row_key_is_not_null,
				STUFF
				(
					(
						SELECT REPLACE(REPLACE(
							' AND updated_row.[%ColumnName%] = INSERTED.[%ColumnName%]',
							'%Table%', tables.name),
							'%ColumnName%', primary_key_columns.name)
						FROM primary_key_columns
						WHERE parent_object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 5, ''
				) AS inserted_table_join,
				STUFF
				(
					(
						SELECT REPLACE(
							', [%ColumnName%] = [%ColumnName%]',
							'%ColumnName%', columns.name)
						FROM sys.columns
						WHERE columns.object_id = tables.object_id AND columns.is_identity = 0 AND columns.is_computed = 0
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS update_columns,
				STUFF
				(
					(
						SELECT REPLACE(', [%ColumnName%]', '%ColumnName%', name)
						FROM primary_key_columns
						WHERE parent_object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS key_column_names,
				STUFF
				(
					(
						SELECT REPLACE(', [%ColumnName%]', '%ColumnName%', columns.name)
						FROM sys.columns
						WHERE columns.object_id = tables.object_id
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS all_column_names,
				STUFF
				(
					(
						SELECT REPLACE(', [%ColumnName%]', '%ColumnName%', columns.name)
						FROM sys.columns
						WHERE columns.object_id = tables.object_id AND columns.is_identity = 0 AND columns.is_computed = 0
						FOR XML PATH(''), TYPE
					).value('.', 'NVARCHAR(MAX)'),
					1, 2, ''
				) AS column_names
			FROM sys.tables 
			INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
			WHERE schemas.name = @schema_name		
		) AS columns ON tables.object_id = columns.object_id
		WHERE schemas.name = @schema_name
	)
	SELECT REPLACE('CREATE SCHEMA [%SandboxName%]', '%SandboxName%', @sandbox_name)
	UNION ALL
	SELECT REPLACE(
		'CREATE USER [%SandboxName%] WITH PASSWORD = ''passw0rd'', DEFAULT_SCHEMA = [%SandboxName%]',
		'%SandboxName%', @sandbox_name)
	UNION ALL
	SELECT create_inserted_rows_table_sql FROM sandbox WHERE create_inserted_rows_table_sql IS NOT NULL
	UNION ALL
	SELECT create_updated_rows_table_sql FROM sandbox WHERE create_updated_rows_table_sql IS NOT NULL
	UNION ALL
	SELECT create_deleted_rows_table_sql FROM sandbox WHERE create_deleted_rows_table_sql IS NOT NULL
	UNION ALL
	SELECT create_view_sql FROM sandbox WHERE create_view_sql IS NOT NULL
	UNION ALL
	SELECT create_primary_key_constraint FROM sandbox WHERE create_primary_key_constraint IS NOT NULL
	UNION ALL
	SELECT create_insert_trigger_sql FROM sandbox WHERE create_insert_trigger_sql IS NOT NULL
	UNION ALL
	SELECT create_update_trigger_sql FROM sandbox WHERE create_update_trigger_sql IS NOT NULL
	UNION ALL
	SELECT create_delete_trigger_sql FROM sandbox WHERE create_delete_trigger_sql IS NOT NULL
	UNION ALL
	SELECT create_indexes_sql FROM sandbox WHERE create_indexes_sql IS NOT NULL
	UNION ALL
	SELECT
		REPLACE([definition],
			REPLACE('CREATE FUNCTION [%Schema%].', '%Schema', @schema_name),
			REPLACE('CREATE FUNCTION [%Sandbox%].', '%Sandbox%', @sandbox_name))
	FROM sys.objects
	INNER JOIN sys.sql_modules ON objects.object_id = sql_modules.object_id
	INNER JOIN sys.schemas ON objects.schema_id = schemas.schema_id
	WHERE objects.[type] IN ('FN', 'IF', 'TF') AND schemas.name = @schema_name
	UNION ALL
	SELECT
		REPLACE([definition],
			REPLACE('CREATE PROCEDURE [%Schema%].', '%Schema', @schema_name),
			REPLACE('CREATE PROCEDURE [%Sandbox%].', '%Sandbox%', @sandbox_name))
	FROM sys.objects
	INNER JOIN sys.sql_modules ON objects.object_id = sql_modules.object_id
	INNER JOIN sys.schemas ON objects.schema_id = schemas.schema_id
	WHERE objects.[type] = 'P' AND schemas.name = @schema_name
	UNION ALL
	SELECT
		REPLACE([definition],
			REPLACE('CREATE VIEW [%Schema%].', '%Schema', @schema_name),
			REPLACE('CREATE VIEW [%Sandbox%].', '%Sandbox%', @sandbox_name))
	FROM sys.objects
	INNER JOIN sys.sql_modules ON objects.object_id = sql_modules.object_id
	INNER JOIN sys.schemas ON objects.schema_id = schemas.schema_id
	WHERE objects.[type] = 'V' AND schemas.name = @schema_name

	DECLARE @sandbox_statement NVARCHAR(MAX)

	OPEN sandbox_statements   
	FETCH NEXT FROM sandbox_statements INTO @sandbox_statement

	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '@sandbox_statement = ' + @sandbox_statement
		EXECUTE @sandbox_statement

		FETCH NEXT FROM tables_to_sandbox INTO @sandbox_statement
	END

	CLOSE sandbox_statements   
	DEALLOCATE sandbox_statements
END