将SQL Server 2008数据库架构克隆到新数据库 - 编程(PHP?)

我做了一些谷歌搜索和搜索SO,但我没能找到很多帮助 。 我正在设计一个使用Microsoft SQL Server 2008 Server的Web服务。 相关结构是这样的......有一个主数据库,包含所有主要账户/公司信息(公司名称,地址等)。 此外,每个帐户/公司都有数据库,其中包含该帐户的所有相关(元?)数据(用户,设置等)。</ p>

   SQL2008服务器
| --- MainDatabase
| -------帐户表
| -----------帐户记录ID = 1
| ------ -----帐户记录ID = 2
| -----------帐户记录ID = 3
| --- AccountDatabase00001
| -------用户表 帐户ID = 1
| --- AccountDatabase00001
| -------帐户的用户表ID = 2
</ code> </ pre>

当 创建了一个新帐户(比方说,ID = 3),我试图想出一种方法来将 AccountDatabase0001 </ code>的表模式和视图(而不是数据)克隆到一个名为的新数据库中 AccountDatabase00003 </代码>。 我可以使用几乎任何语言来执行复制,只要它可以以某种方式从PHP页面调用。</ p>

是否有人遇到过这样的PHP脚本或任何其他类似的脚本 那个问题的语言? 是否有命令我可以发送SQL服务器为我这样做? 我确信我可以通过手动遍历结构并编写SQL语句来创建每个对象,但我希望能有更简单的东西。</ p>
</ div>

展开原文

原文

I've done some Googling and searching on SO, but I have not been able to find much help on the matter. I am designing a web service which utilizes an Microsoft SQL Server 2008 Server. The relevant structure is something like this... There is a main database which houses all Primary Account/Company information (Company name, address, etc..). In addition, there are databases for each Account/Company which houses all of the relevant (meta?)data for that account (users, settings, etc...).

SQL2008 Server
|---MainDatabase
|-------Accounts Table
|-----------Account Record where ID = 1
|-----------Account Record where ID = 2
|-----------Account Record where ID = 3
|---AccountDatabase00001
|-------Users Table for account where ID = 1
|---AccountDatabase00001
|-------Users Table for account where ID = 2

When a new account is created (let's say, ID=3), I am trying to figure out a way to clone the table schema and views (NOT the data) of AccountDatabase0001 into a new database called AccountDatabase00003. I could use virtually any language to perform the duplication as long as it can be called from a PHP page somehow.

Has anyone come across such a PHP script, or a script in any other such language for that matter? Is there a command I can send the the SQL server to do this for me? I'm sure I could find my way through manually traversing the structure and writing SQL statements to create each object, but I'm hoping for something more simple.

2个回答

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;
duanou2526
duanou2526 首先,您需要执行第一个代码块来创建dbo.GenerateScript - 然后运行第二个PHP块。 其次。 这是一个旧的解决方案。 看看CLONEDATABASE docs.microsoft.com/en-us/sql/t-sql/database-console-commands / ...
大约一年之前 回复
duanguai2781
duanguai2781 它显示错误:“Msg 208,Level 16,State 6,Procedure GenerateScript,Line 1 [Batch Start Line 6]无效的对象名称'dbo.GenerateScript'”
大约一年之前 回复

展开原文

原文

You can do this using SMO without too much trouble. Here's one site that gives specific code for it. The code is in C#, but hopefully you can integrate it or translate it into PHP.

douyuai8994
douyuai8994 汤姆,非常感谢这篇文章。 虽然没有透明的方法将该代码转换为PHP(它使用某种我没有看到代码的Visual Studio DLL),但它确实让我的谷歌搜索朝着正确的方向前进。 这引导我到上面的解决方案。 +1为您的努力!
大约 9 年之前 回复
立即提问
相关内容推荐