To create some customized SQL tables clone by script

Question

In an Asp.net project I need to have a sandbox environment so that user be able to test the processes without any risk to hurt the database, so I want to make a stored procedure to create a sort of database clone in which some tables come with data and some come without any data, I think it should be a stored procedure which takes the table name as an argument and another Boolean argument which determines if it's supposed to make a clone of table with data or just the table structure, It will be so better if it considers Views too.

I have read some articles and some Q&A in this website like this one that creates a database backup and then restores it by another name as a clone database, I even read about DBCC in here but none of them have offered such a customized script I tend to have.

Thanks in advance.

For now I think this might help, I can follow steps then change it based on what I desire. Of course it's not the exact answer. — Muhammad Musavi
– Muhammad Musavi, Commented Jan 13, 2018 at 9:27

Jayasurya Satheesh · Accepted Answer · 2018-01-13 10:08:54Z

1

Try This Procedure. Pass The Table Name to Variable @TableName and 'Y' to variable @WithDate if you want t move the data else 'N'. If you didn't specify any values, it will be taken as 'Y' by default

CREATE PROCEDURE dbo.sProc_MoveTable
(
    @TableName VARCHAR(255),
    @WithData CHAR(1) = 'Y'
)   
AS
BEGIN

    DECLARE @Sql VARCHAR(MAX)

    SELECT
        @sql = 'SELECT 
                    * 
                    INTO [DestDatabase].[dbo].['+LTRIM(RTRIM(@TableName))+']
                    FROM [SourceDatabase].[dbo].['+LTRIM(RTRIM(@TableName))+'] 
                        WHERE '
                        +CASE @WithData WHEN 'Y'
                            THEN ' 1=1'
                        ELSE ' 1=0 ' END

    PRINT @sql

    EXEC(@sql)

END
GO

answered Jan 13, 2018 at 10:08

Jayasurya Satheesh

8,0733 gold badges25 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

To create some customized SQL tables clone by script

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related