1

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.

Muhammad Musavi
  • 2,512
  • 2
  • 22
  • 35
  • For now I think [this](https://www.tutorialspoint.com/sql/sql-clone-tables.htm) might help, I can follow steps then change it based on what I desire. Of course it's not the exact answer. – Muhammad Musavi Jan 13 '18 at 09:27

1 Answers1

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
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39