-
-
Save badmotorfinger/1755925 to your computer and use it in GitHub Desktop.
################################################################################################################################ | |
# | |
# Script Name : SmoDb | |
# Version : 1.0 | |
# Author : Vince Panuccio | |
# Purpose : | |
# This script generates one SQL script per database object including Stored Procedures,Tables,Views, | |
# User Defined Functions and User Defined Table Types. Useful for versionining a databsae in a CVS. | |
# | |
# Usage : | |
# Set variables at the top of the script then execute. | |
# | |
# Note : | |
# Only tested on SQL Server 2008r2 | |
# | |
################################################################################################################################ | |
$server = "localhost" | |
$database = "NerdDinner" | |
$output_path = "C:\dev\nerddinner\Schema" | |
$schema = "dbo" | |
$table_path = "$output_path\Table\" | |
$storedProcs_path = "$output_path\StoredProcedure\" | |
$triggers_path = "$output_path\Triggers\" | |
$views_path = "$output_path\View\" | |
$udfs_path = "$output_path\UserDefinedFunction\" | |
$textCatalog_path = "$output_path\FullTextCatalog\" | |
$udtts_path = "$output_path\UserDefinedTableTypes\" | |
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null | |
$srv = New-Object "Microsoft.SqlServer.Management.SMO.Server" $server | |
$db = New-Object ("Microsoft.SqlServer.Management.SMO.Database") | |
$tbl = New-Object ("Microsoft.SqlServer.Management.SMO.Table") | |
$scripter = New-Object ("Microsoft.SqlServer.Management.SMO.Scripter") ($server) | |
# Get the database and table objects | |
$db = $srv.Databases[$database] | |
$tbl = $db.tables | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } | |
$storedProcs = $db.StoredProcedures | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } | |
$triggers = $db.Triggers + ($tbl | % { $_.Triggers }) | |
$views = $db.Views | Where-object { $_.schema -eq $schema } | |
$udfs = $db.UserDefinedFunctions | Where-object { $_.schema -eq $schema -and -not $_.IsSystemObject } | |
$catlog = $db.FullTextCatalogs | |
$udtts = $db.UserDefinedTableTypes | Where-object { $_.schema -eq $schema } | |
# Set scripter options to ensure only data is scripted | |
$scripter.Options.ScriptSchema = $true; | |
$scripter.Options.ScriptData = $false; | |
#Exclude GOs after every line | |
$scripter.Options.NoCommandTerminator = $false; | |
$scripter.Options.ToFileOnly = $true | |
$scripter.Options.AllowSystemObjects = $false | |
$scripter.Options.Permissions = $true | |
$scripter.Options.DriAllConstraints = $true | |
$scripter.Options.SchemaQualify = $true | |
$scripter.Options.AnsiFile = $true | |
$scripter.Options.SchemaQualifyForeignKeysReferences = $true | |
$scripter.Options.Indexes = $true | |
$scripter.Options.DriIndexes = $true | |
$scripter.Options.DriClustered = $true | |
$scripter.Options.DriNonClustered = $true | |
$scripter.Options.NonClusteredIndexes = $true | |
$scripter.Options.ClusteredIndexes = $true | |
$scripter.Options.FullTextIndexes = $true | |
$scripter.Options.EnforceScriptingOptions = $true | |
function CopyObjectsToFiles($objects, $outDir) { | |
if (-not (Test-Path $outDir)) { | |
[System.IO.Directory]::CreateDirectory($outDir) | |
} | |
foreach ($o in $objects) { | |
if ($o -ne $null) { | |
$schemaPrefix = "" | |
if ($o.Schema -ne $null -and $o.Schema -ne "") { | |
$schemaPrefix = $o.Schema + "." | |
} | |
$scripter.Options.FileName = $outDir + $schemaPrefix + $o.Name + ".sql" | |
Write-Host "Writing " $scripter.Options.FileName | |
$scripter.EnumScript($o) | |
} | |
} | |
} | |
# Output the scripts | |
CopyObjectsToFiles $tbl $table_path | |
CopyObjectsToFiles $storedProcs $storedProcs_path | |
CopyObjectsToFiles $triggers $triggers_path | |
CopyObjectsToFiles $views $views_path | |
CopyObjectsToFiles $catlog $textCatalog_path | |
CopyObjectsToFiles $udtts $udtts_path | |
CopyObjectsToFiles $udfs $udfs_path | |
Write-Host "Finished at" (Get-Date) |
Hello
I'm still struggling with this code
So i'm using this code from a remote computer connected to a DBserver
So i could connect to my server this time
The script is able to see the DB, the table, the view. i can see everything when i check the variables
He creates the folders but there is no data
I have check the rights of the folder
I'm admin of my computer
I have even put the group "everyone" on the folder
Have you test remotely on your side?
Kind Regards
Maxime
Sorry mate, I haven't used this script for years so I have no idea anymore :)
Hi Maxime,
I changed the script to my needs.
This is working remotely for me, hope it works for you too.
Kind regards,
Martijn
Fantastic! 😃
Hi Maxime,
I changed the script to my needs.
This is working remotely for me, hope it works for you too.Kind regards,
Martijn
Thank you so much mcbos !! it works great !
Thanks for your answer!
For now i'm using it on the main server.
But i think it's coming from trying to connect as my server is not on my "network"
Anyway , do you have any idea how to extract only the table i need?
For example i have a file txt where i have put the name of the table i need
Thanks in advance