1

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.

Thanks for the help

2 Answers 2

9

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): $_"
    }
}
Sign up to request clarification or add additional context in comments.

3 Comments

I tried it out and it works but how would i specify just one table or a list of objects e.g script out proc1,table2,proc4,table6 .I am new to smo so have no idea how it works
All objects to be scripted are first gathered in the $objects list. You can filter that list before the foreach to only keep the objects you want.
Why does it only return some of the user defined functions, and not, for example any of the Table-Valued functions??
2

Here's a script to backup an individual object. Simply pass the object name to the function:

http://sev17.com/2012/04/backup-database-object/

2 Comments

Broken link, Error 500 on server
(noticed the date as 12 years ago, and changed the downvote on the answer, sorry)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.