I found a remotely simply way to accomplish this in PHP with a little help from a custom-written stored procedure which need only exist in the database you wish to clone (for me it's always AccountDatabase_1
). You pass the Table name to the Stored Procedure, and it returns the script you need to run in order to create it (which we will do, on the second database). For views, the creation script is actually stored in the Information_Schema.Views
table, so you can just pull the view names and creation code from that to create clones very easily.
STORED PROC GenerateScript()
USE [SOURCE_DATABASE_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[GenerateScript]
(
@tableName varchar(100)
)
as
If exists (Select * from Information_Schema.COLUMNS where Table_Name= @tableName)
Begin
declare @sql varchar(8000)
declare @table varchar(100)
declare @cols table (datatype varchar(50))
insert into @cols values('bit')
insert into @cols values('binary')
insert into @cols values('bigint')
insert into @cols values('int')
insert into @cols values('float')
insert into @cols values('datetime')
insert into @cols values('text')
insert into @cols values('image')
insert into @cols values('uniqueidentifier')
insert into @cols values('smalldatetime')
insert into @cols values('tinyint')
insert into @cols values('smallint')
insert into @cols values('sql_variant')
set @sql=''
Select
@sql=@sql+
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +
case when Column_name='id' then ' IDENTITY ' else '' end +
case when Data_Type in (Select datatype from @cols) then '' else '(' end+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(numeric_precision,'') as varchar)+','+
case when data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end
when data_type in ('char','nvarchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
case when data_type ='varchar' and Character_Maximum_Length<0 then 'max' else '' end+
case when data_type ='varchar' and Character_Maximum_Length>=0 then cast(isnull(Character_Maximum_Length,'') as varchar) else '' end+
case when Data_Type in (Select datatype from @cols)then '' else ')' end+
case when Is_Nullable='No ' then ' Not null ' else ' null ' end +
case when Column_Default is not null then 'DEFAULT ' + Column_Default else '' end + ','
from
Information_Schema.COLUMNS where Table_Name=@tableName
select
@table= 'Create table ' + table_Name
from
Information_Schema.COLUMNS
where
table_Name=@tableName
select @sql=@table + substring(@sql,1,len(@sql)-1) +' )'
select @sql as DDL
End
Else
Select 'The table '+@tableName + ' does not exist'
PHP
function cloneAccountDatabase($new_id){
$srcDatabaseName = "AccountDatabase_1"; //The Database we are cloning
$sourceConn = openDB($srcDatabaseName);
$destDatabaseName = "AccountDatabase_".(int)$new_id;
$destConn = openDB($destDatabaseName);
//ENSURE DATABASE EXISTS, OR CREATE IT
if ($destConn==null){
odbc_exec($sourceConn, "CREATE database " . $destDatabaseName);
$destConn = openDB($destDatabaseName);
}
//BUILD ARRAY OF TABLE NAMES
$tables = array();
$q = odbc_exec($sourceConn, "SELECT name FROM sys.Tables");
while (odbc_fetch_row($q))
$tables[]=odbc_result($q,"name");
//CREATE TABLES
foreach ($tables as $tableName){
$q=odbc_exec($sourceConn, "exec GenerateScript '$tableName';");
odbc_fetch_row($q);
$sql = odbc_result($q, 'ddl');
$q=odbc_exec($destConn, $sql);
}
//BUILD ARRAY OF VIEW NAMES AND CREATE
$q = odbc_exec($sourceConn, "SELECT * FROM Information_Schema.Views");
while (odbc_fetch_row($q)){
$view=odbc_result($q,"table_name");
$sql = odbc_result($q, "view_definition");
odbc_exec($destConn, $sql);
}
return(true);
}
UPDATE
The CLONEDATABASE
function is available in newer versions of MSSQL.
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-clonedatabase-transact-sql?view=sql-server-2017
//Clone AccountDatabase_1 to a database called AccountDatabase_2
DBCC CLONEDATABASE (AccountDatabase_1, AccountDatabase_2) WITH VERIFY_CLONEDB, NO_STATISTICS;
ALTER DATABASE AccountDatabase_2 SET READ_WRITE WITH NO_WAIT;