Asp.net项目沙箱环境：求可自定义克隆表/视图的存储过程

Hey there! I've built a tailored stored procedure that addresses exactly your sandbox cloning needs—you can specify which tables/views to copy, choose between schema-only or full data clones, and it handles both tables and views seamlessly. This is perfect for letting users test flows safely without touching your production database.

Here's the full implementation, along with breakdowns and usage examples:

CREATE OR ALTER PROCEDURE dbo.CloneDatabaseObjects
    @SourceDB NVARCHAR(128),
    @TargetDB NVARCHAR(128),
    @ObjectNames NVARCHAR(MAX), -- Comma-separated list of tables/views (e.g., 'Customers,Orders,v_ProductSummary')
    @CloneData BIT = 0, -- 1 = Clone data, 0 = Schema-only
    @CloneViews BIT = 0 -- 1 = Clone views, 0 = Skip views
AS
BEGIN
    SET NOCOUNT ON;

    -- Validate target database exists
    IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = @TargetDB)
    BEGIN
        RAISERROR('Target database %s does not exist. Please create it first.', 16, 1, @TargetDB);
        RETURN;
    END

    -- Split comma-separated object names into a temp table
    DECLARE @Objects TABLE (ObjectName NVARCHAR(128));
    INSERT INTO @Objects
    SELECT TRIM(value) FROM STRING_SPLIT(@ObjectNames, ',') WHERE TRIM(value) <> '';

    -- Iterate over each object to clone
    DECLARE @CurrentObj NVARCHAR(128);
    DECLARE obj_cursor CURSOR FOR SELECT ObjectName FROM @Objects;
    OPEN obj_cursor;
    FETCH NEXT FROM obj_cursor INTO @CurrentObj;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Check if object is a table
        IF EXISTS (SELECT 1 FROM @SourceDB.sys.tables WHERE name = @CurrentObj)
        BEGIN
            -- Generate CREATE TABLE script for target database
            DECLARE @CreateTableSQL NVARCHAR(MAX);
            SET @CreateTableSQL = 'SELECT ''CREATE TABLE '' + QUOTENAME(''' + @TargetDB + ''') + ''.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(t.name) + ''('' + 
                STRING_AGG(QUOTENAME(c.name) + '' '' + 
                    CASE WHEN c.system_type_id = c.user_type_id THEN t.name ELSE ut.name END + 
                    CASE WHEN c.max_length <> -1 AND c.system_type_id IN (167, 175, 231, 239) THEN ''('' + CASE WHEN c.max_length > 4000 THEN ''MAX'' ELSE CAST(c.max_length AS NVARCHAR) END + '')'' 
                         WHEN c.system_type_id IN (106, 108) THEN ''('' + CAST(c.precision AS NVARCHAR) + '', '' + CAST(c.scale AS NVARCHAR) + '')'' 
                         ELSE '''' END + 
                    CASE WHEN c.is_nullable = 0 THEN '' NOT NULL'' ELSE '''' END + 
                    CASE WHEN dc.definition IS NOT NULL THEN '' DEFAULT '' + dc.definition ELSE '''' END + 
                    CASE WHEN ic.is_primary_key = 1 THEN '' PRIMARY KEY'' ELSE '''' END, '', '') + 
                '')'' 
                FROM ' + QUOTENAME(@SourceDB) + '.sys.tables t
                JOIN ' + QUOTENAME(@SourceDB) + '.sys.columns c ON t.object_id = c.object_id
                JOIN ' + QUOTENAME(@SourceDB) + '.sys.types t ON c.system_type_id = t.system_type_id
                LEFT JOIN ' + QUOTENAME(@SourceDB) + '.sys.types ut ON c.user_type_id = ut.user_type_id
                LEFT JOIN ' + QUOTENAME(@SourceDB) + '.sys.default_constraints dc ON c.default_object_id = dc.object_id
                LEFT JOIN ' + QUOTENAME(@SourceDB) + '.sys.index_columns ic ON c.object_id = ic.object_id AND c.column_id = ic.column_id
                LEFT JOIN ' + QUOTENAME(@SourceDB) + '.sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                WHERE t.name = ''' + @CurrentObj + '''
                GROUP BY t.name, s.name;';

            -- Execute CREATE TABLE
            DECLARE @TableCreation NVARCHAR(MAX);
            EXEC sp_executesql @CreateTableSQL, N'@TableCreation NVARCHAR(MAX) OUTPUT', @TableCreation = @TableCreation OUTPUT;
            EXEC sp_executesql @TableCreation;

            -- Clone data if requested
            IF @CloneData = 1
            BEGIN
                DECLARE @InsertDataSQL NVARCHAR(MAX);
                SET @InsertDataSQL = 'INSERT INTO ' + QUOTENAME(@TargetDB) + '..' + QUOTENAME(@CurrentObj) + '
                    SELECT * FROM ' + QUOTENAME(@SourceDB) + '..' + QUOTENAME(@CurrentObj) + ';';
                EXEC sp_executesql @InsertDataSQL;

                -- Reset identity seed (optional, uncomment if needed)
                -- DECLARE @ResetIdentity NVARCHAR(MAX);
                -- SET @ResetIdentity = 'DBCC CHECKIDENT(''' + QUOTENAME(@TargetDB) + '..' + QUOTENAME(@CurrentObj) + ''', RESEED);';
                -- EXEC sp_executesql @ResetIdentity;
            END
        END
        -- Check if object is a view and cloning is enabled
        ELSE IF @CloneViews = 1 AND EXISTS (SELECT 1 FROM @SourceDB.sys.views WHERE name = @CurrentObj)
        BEGIN
            -- Generate CREATE VIEW script
            DECLARE @CreateViewSQL NVARCHAR(MAX);
            SET @CreateViewSQL = 'SELECT ''CREATE VIEW '' + QUOTENAME(''' + @TargetDB + ''') + ''.'' + QUOTENAME(s.name) + ''.'' + QUOTENAME(v.name) + '' AS '' + m.definition
                FROM ' + QUOTENAME(@SourceDB) + '.sys.views v
                JOIN ' + QUOTENAME(@SourceDB) + '.sys.schemas s ON v.schema_id = s.schema_id
                JOIN ' + QUOTENAME(@SourceDB) + '.sys.sql_modules m ON v.object_id = m.object_id
                WHERE v.name = ''' + @CurrentObj + ''';';

            -- Execute CREATE VIEW
            DECLARE @ViewCreation NVARCHAR(MAX);
            EXEC sp_executesql @CreateViewSQL, N'@ViewCreation NVARCHAR(MAX) OUTPUT', @ViewCreation = @ViewCreation OUTPUT;
            EXEC sp_executesql @ViewCreation;
        END
        ELSE
        BEGIN
            PRINT 'Warning: Object ' + @CurrentObj + ' not found in source database or view cloning is disabled. Skipping.';
        END

        FETCH NEXT FROM obj_cursor INTO @CurrentObj;
    END

    CLOSE obj_cursor;
    DEALLOCATE obj_cursor;

    PRINT 'Cloning process completed successfully.';
END
GO

Key Features Explained #

• Flexible Object Selection: Pass a comma-separated list of tables and/or views to clone only what you need (no full database backup/restore overhead).
• Schema-Only or Full Data: Use @CloneData = 0 to copy just table structures, or @CloneData = 1 to include all rows.
• View Support: Toggle view cloning on/off with @CloneViews = 1—it copies the exact view definition to the target sandbox.
• Safety First: Uses QUOTENAME to prevent SQL injection, and validates the target database exists before proceeding.

Usage Examples #

1. Clone Tables (Schema-Only)

-- Clone Customers and Orders tables (no data) from ProductionDB to SandboxDB
EXEC dbo.CloneDatabaseObjects
    @SourceDB = 'ProductionDB',
    @TargetDB = 'SandboxDB',
    @ObjectNames = 'Customers,Orders',
    @CloneData = 0,
    @CloneViews = 0;

2. Clone Tables with Data + Views

-- Clone Products (with data) and v_ProductCategories view from ProductionDB to SandboxDB
EXEC dbo.CloneDatabaseObjects
    @SourceDB = 'ProductionDB',
    @TargetDB = 'SandboxDB',
    @ObjectNames = 'Products,v_ProductCategories',
    @CloneData = 1,
    @CloneViews = 1;

Important Notes #

• Target Database Setup: Make sure the target sandbox database exists before running the procedure. If you want auto-creation, you can add a check to create it (just be mindful of file paths).
• Permissions: The user executing this procedure needs:
  • SELECT access on source database tables/views
  • CREATE TABLE/CREATE VIEW/INSERT access on the target database
• Dependencies: If you're cloning tables with foreign keys, clone parent tables first to avoid constraint errors.
• Identity Columns: The data clone preserves identity values. If you want to reset the identity seed after cloning, uncomment the DBCC CHECKIDENT section in the procedure.
• Indexes/Triggers: This base version clones table structure (columns, constraints, defaults) and view definitions. If you need indexes or triggers, you can extend the procedure by adding logic to generate those scripts from sys.indexes and sys.triggers.

内容的提问来源于stack exchange，提问作者Muhammad Musavi