September 27, 2016 at 6:50 am
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.
September 27, 2016 at 7:21 am
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
September 27, 2016 at 7:51 am
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.
October 4, 2016 at 5:00 am
This any good to you? Looks like scripting statistics is a valid option.
October 4, 2016 at 11:01 am
looks very useful, thanks.
October 4, 2016 at 11:08 am
Would a T-SQL option help?
http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/
October 4, 2016 at 2:31 pm
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"
October 5, 2016 at 7:19 am
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
October 5, 2016 at 7:49 am
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.
October 5, 2016 at 7:52 am
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!
August 21, 2021 at 4:33 pm
Would a T-SQL option help? http://www.sqlservercentral.com/scripts/SQL+Server+2005/67515/
It's a bloody shame that the "upgrades" to the forum software have rendered such links totally useless. 🙁
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply