如何在ironpython中为Microsoft.SqlServer.SMO.Scripter.Script类定义参数

Question

我想使用下面的ironpython代码编写我的数据库对象的脚本：

import sys
import clr

database_name  = r'localhost\SQLEXPRESS'
dir_assemblies = r'D:\programfiles\Microsoft SQL Server\100\SDK\Assemblies'

# Import SMO Namespace
sys.path.append(dir_assemblies)
clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll')
import Microsoft.SqlServer.Management.Smo as SMO


db       = SMO.Server(database_name)
scripter = SMO.Scripter(db)

for database in db.Databases:
    for table in database.Tables:
        # TypeError: expected Array[Urn], got Table
        scripter.Script(table)    

执行此代码时，出现以下错误：

File "SMOtest2.py", line 18, in <module>
TypeError: expected Array[Urn], got Table

SMO.Scripter.doc为我提供以下信息：

Script(self: Scripter, urns: Array[Urn]) -> StringCollection
Script(self: Scripter, list: UrnCollection) -> StringCollection
Script(self: Scripter, objects: Array[SqlSmoObject]) -> StringCollection

我尝试创建一个数组[Urn ] 或 Array[SqlSmoObject] 但没有任何成功。

有谁知道如何为 SMO.Scripter.Script 类创建正确的参数？

我想用Python编写下面的VB代码。 摘自：http://msdn.microsoft.com /en-us/library/ms162160(v=SQL.90).aspx

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Scripter object and set the required scripting options.
Dim scrp As Scripter
scrp = New Scripter(srv)
scrp.Options.ScriptDrops = False
scrp.Options.WithDependencies = True
'Iterate through the tables in database and script each one. Display the script.
'Note that the StringCollection type needs the System.Collections.Specialized namespace to be included.
Dim tb As Table
Dim smoObjects(1) As Urn
For Each tb In db.Tables
    smoObjects = New Urn(0) {}
    smoObjects(0) = tb.Urn
    If tb.IsSystemObject = False Then
        Dim sc As StringCollection
        sc = scrp.Script(smoObjects)
        Dim st As String
        For Each st In sc
            Console.WriteLine(st)
        Next
    End If
Next

Answer 1

我找到了解决方案：

arg=System.Array[SMO.SqlSmoObject]([table])

完整的脚本如下所示：

import sys    
import clr    
# import .NET Array
import System.Array

database_name  = r'localhost\SQLEXPRESS'    
dir_assemblies = r'D:\programfiles\Microsoft SQL Server\100\SDK\Assemblies'   

# Import SMO Namespace    
sys.path.append(dir_assemblies)    
clr.AddReferenceToFile('Microsoft.SqlServer.Smo.dll')    
import Microsoft.SqlServer.Management.Smo as SMO    


db       = SMO.Server(database_name)    
scripter = SMO.Scripter(db)    

for database in db.Databases:    
    for table in database.Tables:    
        # create a .NET Array as an argument for the scripter
        arg=System.Array[SMO.SqlSmoObject]([table])
        script = scripter.Script(arg)
        #output script
        for line in script:
            print line

Answer 2

从未使用过 IronPython 或 SMO，但看起来它需要某种类型的集合。您是否尝试过：

scripter.Script(database.Tables)

而不是一次编写一张表的脚本？