Generate Create Script for a table - include indexes, keys, statistics

  • I spent yesterday trying various approaches to this found on google. Couldn't get any to work.

    What I'm looking for is a powershell script that will work for a single table, generating the create script for the table, PK, indexes, foreign keys and ( ideally ) statistics.

  • Find a SQL Server instance that isn't doing anything - a test instance on your desktop/laptop for example. In SSMS, go to Tools -> Options -> SQL Server Object Explorer -> Scripting and choose what you do and don't want to script. Start a Profiler trace or an extended events session, then right-click on an object of the type you want to script and script it out. Stop the trace or extended events session, and examine the commands that were executed in the background. You can now incorporate those commands into your PowerShell script.

    John

  • Thanks John. I'll have to try that again. I did exactly that in profiler yesterday and the queries it revealed pulled back various info on the table, indexes, etc. but didn't reveal any "create" scripts in the query results. Either I did it wrong, or SSMS scripting handles that "consolidation" part in some internal code.

    SSMS scripting does fairly well but never seems to do anything with statistics. I found a separate query for that, but was hoping to go the powershell route.

  • This any good to you? Looks like scripting statistics is a valid option.

    https://www.simple-talk.com/sql/database-administration/automated-script-generation-with-powershell-and-smo/

  • looks very useful, thanks.

  • Would a T-SQL option help?

    http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • For now I'd rather stick with powershell. I took The Simple Talk ( Phil Factor ) powershell script and adapted it. Then added an IF statement near the end so I can just get the one table I'm after. Create Statistics statements are sometimes generated, sometimes not.

    I don't expect or need those if it's an index-related statistic since the Create Index statements are showing up. But other stat scripts aren't being created reliably even though I have that set to True in the options.

    $ServerName='MyDevSqlServer' # the server it is on

    $Database='performance' # the name of the database you want to script as objects

    $DirectoryToSaveTo='C:\powershell' # the directory where you want to store them

    # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries

    $v = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')

    if ((($v.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') {

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

    }

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

    set-psdebug -strict # catch a few extra bugs

    $ErrorActionPreference = "stop"

    $My='Microsoft.SqlServer.Management.Smo'

    $srv = new-object ("$My.Server") $ServerName # attach to the server

    if ($srv.ServerType-eq $null) # if it managed to find a server

    {

    Write-Error "Sorry, but I couldn't find Server '$ServerName' "

    return

    }

    $scripter = new-object ("$My.Scripter") $srv # create the scripter

    $scripter.Options.ToFileOnly = $true

    $scripter.Options.ExtendedProperties= $true # yes, we want these

    $scripter.Options.DRIAll= $true # and all the constraints

    $scripter.Options.Indexes= $true # Yup, these would be nice

    $scripter.Options.Triggers= $true # This should be included

    $scripter.Options.Statistics=$true

    # first we get the bitmap of all the object types we want

    $objectsToDo =[long] [Microsoft.SqlServer.Management.Smo.DatabaseObjectTypes]::Table

    # and we store them in a datatable

    $d = new-object System.Data.Datatable

    # get just the tables

    $d=$srv.databases[$Database].EnumObjects($objectsToDo)

    # and write out each scriptable object as a file in the directory you specify

    $d| FOREACH-OBJECT { # for every object we have in the datatable.

    $SavePath="$($DirectoryToSaveTo)\$($_.DatabaseObjectTypes)\$($_.Schema)"

    # create the directory if necessary (SMO doesn't).

    if (!( Test-Path -path $SavePath )) # create it if not existing

    {Try { New-Item $SavePath -type directory | out-null }

    Catch [system.exception]{

    Write-Error "error while creating '$SavePath' $_"

    return

    }

    }

    # tell the scripter object where to write it

    $scripter.Options.Filename = "$SavePath\$($_.name -replace '[\\\/\:\.]','-').sql";

    # Create a single element URN array

    $UrnCollection = new-object ("$My.urnCollection")

    $URNCollection.add($_.urn)

    # and write out the object to the specified file

    #Write-Host $_.urn

    if ( $_.urn -match ‘Persistent_Object_Reference’) {

    $scripter.script($URNCollection)

    }

    }

    "All is written out, wondrous human"

  • Yes, something is inconsistent about this powershell scripting with SMO. With these options I get create statistic statements on about 12 tables where there are numerous statistics on many other tables that powershell left out. The create index statements should re-create index-related statistics, but the vast majority of column-based stats are left out: e.g. _WA_Sys_00000002_69FBBC1F

    $scripter = new-object ("$My.Scripter") $srv # create the scripter

    #$scripter.DiscoverDependencies(

    $scripter.Options.ToFileOnly = $true

    #$scripter.Options.WithDependencies =$true

    $scripter.Options.Statistics=$true

    $scripter.Options.ExtendedProperties= $true # yes, we want these

    $scripter.Options.DRIAll= $true # and all the constraints

    $scripter.Options.Indexes= $true # Yup, these would be nice

    $scripter.Options.Triggers= $true # This should be included

    $scripter.Options.DriForeignKeys=$true

  • I'm seeing similar behaviour scripting through SSMS. There the stats option has 3 values:

    Do not script statistics - no stats

    Script Statistics - stats for indexes only

    Script statistics and histograms - index & column stats & histograms.

    Can't see a separate option for histograms nor a way to specify histograms to the Statistics options in SMO scripter.

    Edit: spelling.

  • Going to try and test it, but postulating that column stats are a property of the columns themselves rather than the table?

    Edit: nope, there's no further Statistics property under columns. I've also just noticed the Statistics only option doesn't even script all index stats!

  • It's a bloody shame that the "upgrades" to the forum software have rendered such links totally useless. 🙁

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 10 (of 10 total)

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