Post reply

SQL SMO

  • November 27, 2012 at 6:52 pm

    #266627

    I have a short SMO file to create a script to create a database.

    I don't much about SMO and found an existing file and made a few changes to it.

    It works but it seems to ignore the option Default =$True

    It makes a create table entry but does not put in the default values.

    If this is the wrong forum for this please let me know.

  • November 28, 2012 at 9:51 pm

    #1563556

    can you post the relevant portion of your script?

  • November 29, 2012 at 12:36 pm

    #1563984

    Here is a powershell script that now works. I have a very similar one for drops and stored procedures. Then I concatenate them together. If you run a script with Options.ScriptDrops = $True it will not do the create of the database.

    I could not find an option that would consistently create the default values, but looping through the columns and adding the DefaultConstraint worked. On some of my databases options.DRIAll worked and on some it didn't. I gave up and used the code below.

    Get can get rid of the $conContext lines if you are running the script on the same computer as the SQL Server.

    I hope this helps someone.

    ############################################

    $server = $args[0]

    $dbname = $args[1]

    $password = $args[2]

    $output = $args[3]

    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    $smoObjects = New-Object Microsoft.SqlServer.Management.Smo.UrnCollection

    $srv = new-Object Microsoft.SqlServer.Management.Smo.Server($server)

    $conContext = $srv.ConnectionContext

    $conContext.LoginSecure = $FALSE

    $conContext.Login = "sa"

    $conContext.Password = $password

    $db = New-Object Microsoft.SqlServer.Management.Smo.Database

    $db = $srv.Databases.Item($dbname)

    ## CREATE TABLES

    $scrp = New-Object Microsoft.SqlServer.Management.Smo.Scripter($srv)

    $scrp.Options.FileName = $output

    $scrp.Options.AppendToFile = $True

    Foreach ($tb in $db.Tables) {

    If ($tb.IsSystemObject -eq $FALSE) {

    $smoObjects.Add($tb.Urn);

    foreach($column in $tb.Columns) {

    #Iterate thru all the default constraints in the columns

    foreach($default in $column.DefaultConstraint) {

    #If there are default constraints, script out the ADD CONSTRAINT command

    if ($default -ne $null) {

    $smoObjects.Add($default.Urn);

    }

    }

    }

    Foreach ($index in $tb.Indexes) {

    $smoObjects.Add($index.Urn);

    }

    }

    }

    $sc = $scrp.Script($smoObjects)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply