-- Create a stored procedure to move deleted tables to another database.
CREATE PROCEDURE MoveDeletedTables
@SourceDatabaseName sysname,
@TargetDatabaseName sysname,
@TableName sysname
AS
BEGIN
-- Check if the source and target databases exist.
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @SourceDatabaseName)
BEGIN
RAISERROR('Source database does not exist.', 16, 1)
RETURN
END
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDatabaseName)
BEGIN
RAISERROR('Target database does not exist.', 16, 1)
RETURN
END
-- Create a temporary table to store the schema of the deleted table.
CREATE TABLE #TempTableSchema (
Column_Name sysname,
Data_Type sysname,
Is_Nullable bit
);
-- Get the schema of the deleted table.
INSERT INTO #TempTableSchema
SELECT
c.name AS Column_Name,
t.name AS Data_Type,
c.is_nullable AS Is_Nullable
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@SourceDatabaseName + '.' + @TableName);
-- Create the table in the target database.
DECLARE @CreateTableScript NVARCHAR(MAX) = 'CREATE TABLE ' + @TargetDatabaseName + '.' + @TableName + '(';
DECLARE @ColumnDefinition NVARCHAR(MAX) = '';
SELECT @ColumnDefinition = @ColumnDefinition +
QUOTENAME(Column_Name) + ' ' +
Data_Type +
CASE
WHEN Is_Nullable = 1 THEN ' NULL'
ELSE ' NOT NULL'
END + ','
FROM #TempTableSchema;
SET @CreateTableScript = @CreateTableScript + @ColumnDefinition;
-- Remove the last comma.
SET @CreateTableScript = SUBSTRING(@CreateTableScript, 1, LEN(@CreateTableScript) - 1) + ');';
-- Execute the create table script.
EXECUTE sp_executesql @CreateTableScript;
-- Move the data from the source table to the target table.
DECLARE @InsertDataScript NVARCHAR(MAX) = 'INSERT INTO ' + @TargetDatabaseName + '.' + @TableName + ' SELECT * FROM ' + @SourceDatabaseName + '.' + @TableName;
EXECUTE sp_executesql @InsertDataScript;
-- Drop the table from the source database.
DROP TABLE @SourceDatabaseName + '.' + @TableName;
-- Drop the temporary table.
DROP TABLE #TempTableSchema;
END;
-- Example usage:
EXECUTE MoveDeletedTables 'SourceDatabase', 'TargetDatabase', 'MyTable';
content_copy
Use code with caution.
SQL
Explanation:
Stored Procedure: The code defines a stored procedure named MoveDeletedTables which takes three parameters:
@SourceDatabaseName: The name of the source database where the table will be deleted from.
@TargetDatabaseName: The name of the target database where the table will be moved.
@TableName: The name of the table to be moved.
Database Existence Check: The procedure checks if both the source and target databases exist. If either doesn't exist, it raises an error and returns.
Schema Retrieval: The procedure creates a temporary table #TempTableSchema to store the schema (column names, data types, nullability) of the deleted table.
Table Creation in Target Database:
It builds a dynamic SQL script (@CreateTableScript) to create the table in the target database based on the schema retrieved from the source table.
It executes the script using sp_executesql.
Data Transfer:
It constructs another dynamic SQL script (@InsertDataScript) to insert data from the source table into the newly created table in the target database.
It executes this script to transfer the data.
Table Deletion: The procedure then drops the table from the source database.
Cleanup: The temporary table #TempTableSchema is dropped.
To Use:
Replace SourceDatabase and TargetDatabase with the actual names of your databases.
Call the stored procedure with the name of the table you want to move, like this:
EXECUTE MoveDeletedTables 'SourceDatabase', 'TargetDatabase', 'MyTable';
content_copy
Use code with caution.
SQL
Important Notes:
Trigger Alternative: While the provided code is a stored procedure, you can still implement the logic within a trigger if needed. However, it's generally preferred to use a stored procedure for complex data manipulation tasks.
Permissions: Make sure you have the necessary permissions to create tables and insert data in the target database.
Error Handling: The provided code includes basic error handling for database existence. You may want to add more comprehensive error handling to handle potential issues during schema retrieval, table creation, or data transfer.
Performance: For large tables, the data transfer operation might take a significant amount of time. Consider using table-level operations for improved performance.
Alternative Solutions: Explore other tools like SQL Server Agent jobs or third-party data migration tools for more robust and automated solutions.
