使用 SMO 从 SQL 中编写单个对象的脚本

问题描述

对于我的工作，我经常需要从 Microsoft SQL 2008 服务器编写一个包含所有键、约束和触发器(基本上是重新创建表的完整脚本)的表的脚本.我还必须为过程和触发器执行此操作.我现在要做的是打开 SSMS 右键单击​​对象并选择脚本并选择将其脚本化到文件.因此，如果我有 3 个程序要做，有 10 个表和 1 个触发器，我最终会这样做 14 次.我想要的是一个 powershell 脚本，我可以将一个对象列表提供给它，然后它会使用 SMO 将每个脚本输出到一个单独的文件中.

For my job I often have to script out a table with all its keys, constraints and Triggers (basically a full script to recreate the table) from a Microsoft SQL 2008 server.I also have to do this for procedures and triggers.What I do now is open SSMS right click the object and select script to and select to script it to a file. So if I have 3 procedures to do and 10 tables and 1 trigger I end up doing this 14 times .What I would like is a powershell script that I could feed a list of objects to and then it would go and use SMO to script each on out to an individual file.

感谢您的帮助

推荐答案

这是我在需要编写数据库脚本时使用的 PowerShell 函数.修改脚本应该很容易，只需编写您需要的对象即可.

Here is a PowerShell function I use whenever I have to script a database. It should be easy to modify just to scripts the objects you need.

function SQL-Script-Database
{
    <#
    .SYNOPSIS
    Script all database objects for the given database.

    .DESCRIPTION
    This  function scripts all database objects  (i.e.: tables,  views, stored
    procedures,  and user defined functions) for the specified database on the
    the given server\instance. It creates a subdirectory per object type under
    the path specified.

    .PARAMETER savePath
    The root path where to save object definitions.

    .PARAMETER database
    The database to script (default = $global:DatabaseName)

    .PARAMETER DatabaseServer
    The database server to be used (default: $global:DatabaseServer).

    .PARAMETER InstanceName
    The instance name to be used (default: $global:InstanceName).

    .EXAMPLE
    SQL-Script-Database c:\temp AOIDB
    #>

    param (
        [parameter(Mandatory = $true)][string] $savePath,
        [parameter(Mandatory = $false)][string] $database = $global:DatabaseName,
        [parameter(Mandatory = $false)][string] $DatabaseServer = $global:DatabaseServer,
        [parameter(Mandatory = $false)][string] $InstanceName = $global:InstanceName
    )

    try
    {
        if (!$DatabaseServer -or !$InstanceName)
            { throw "`$DatabaseServer or `$InstanceName variable is not properly initialized" }

        $ServerInstance = SQL-Get-Server-Instance $DatabaseServer $InstanceName

        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null

        $s = New-Object Microsoft.SqlServer.Management.Smo.Server($ServerInstance)
        $db = $s.databases[$database]

        $objects = $db.Tables
        $objects += $db.Views
        $objects += $db.StoredProcedures
        $objects += $db.UserDefinedFunctions

        $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

        $scripter.Options.AnsiFile = $true
        $scripter.Options.IncludeHeaders = $false
        $scripter.Options.ScriptOwner = $false
        $scripter.Options.AppendToFile = $false
        $scripter.Options.AllowSystemobjects = $false
        $scripter.Options.ScriptDrops = $false
        $scripter.Options.WithDependencies = $false
        $scripter.Options.SchemaQualify = $false
        $scripter.Options.SchemaQualifyForeignKeysReferences = $false
        $scripter.Options.ScriptBatchTerminator = $false

        $scripter.Options.Indexes = $true
        $scripter.Options.ClusteredIndexes = $true
        $scripter.Options.NonClusteredIndexes = $true
        $scripter.Options.NoCollation = $true

        $scripter.Options.DriAll = $true
        $scripter.Options.DriIncludeSystemNames = $false

        $scripter.Options.ToFileOnly = $true
        $scripter.Options.Permissions = $true

        foreach ($o in $objects | where {!($_.IsSystemObject)})
        {
            $typeFolder=$o.GetType().Name

            if (!(Test-Path -Path "$savepath\$typeFolder"))
                { New-Item -Type Directory -name "$typeFolder"-path "$savePath" | Out-Null }

            $file = $o -replace "\[|\]"
            $file = $file.Replace("dbo.", "")

            $scripter.Options.FileName = "$savePath\$typeFolder\$file.sql"
            $scripter.Script($o)
        }
    }

    catch
    {
        Util-Log-Error "`t`t$($MyInvocation.InvocationName): $_"
    }
}

这篇关于使用 SMO 从 SQL 中编写单个对象的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！