{"id":6160,"date":"2013-10-18T09:03:40","date_gmt":"2013-10-18T09:03:40","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/powershell-script-all-objects-on-all-databases-to-files\/"},"modified":"2017-11-07T14:29:43","modified_gmt":"2017-11-07T14:29:43","slug":"powershell-script-all-objects-on-all-databases-to-files","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/powershell-script-all-objects-on-all-databases-to-files\/","title":{"rendered":"PowerShell &#8211; script all objects on all databases to files"},"content":{"rendered":"<pre class=\"theme:powershell-ise lang:ps decode:true \">[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')\r\n$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName\r\n\r\n$IncludeTypes = @(\"tables\", \"StoredProcedures\", \"Views\", \"UserDefinedFunctions\")\r\n$ExcludeSchemas = @(\"sys\", \"Information_Schema\")\r\n\r\n\r\n$so = new-object ('Microsoft.SqlServer.Management.Smo.ScriptingOptions')\r\n$so.IncludeIfNotExists = 0\r\n$so.SchemaQualify = 1\r\n$so.AllowSystemObjects = 0\r\n$so.ScriptDrops = 0 #Script Drop Objects\r\n\r\n$dbs = $serverInstance.Databases\r\nforeach ($db in $dbs)\r\n{\r\n    $dbname = \"$db\".replace(\"[\", \"\").replace(\"]\", \"\")\r\n    $dbpath = \"$path\" + \"$dbname\" + \"\\\"\r\n    if (!(Test-Path $dbpath))\r\n    { $null = new-item -type directory -name \"$dbname\" -path \"$path\" }\r\n    \r\n    foreach ($Type in $IncludeTypes)\r\n    {\r\n        $objpath = \"$dbpath\" + \"$Type\" + \"\\\"\r\n        if (!(Test-Path $objpath))\r\n        { $null = new-item -type directory -name \"$Type\" -path \"$dbpath\" }\r\n        foreach ($objs in $db.$Type)\r\n        {\r\n            If ($ExcludeSchemas -notcontains $objs.Schema)\r\n            {\r\n                $ObjName = \"$objs\".replace(\"[\", \"\").replace(\"]\", \"\")\r\n                $OutFile = \"$objpath\" + \"$ObjName\" + \".sql\"\r\n                $objs.Script($so) + \"GO\" | out-File $OutFile #-Append\r\n            }\r\n        }\r\n    }\r\n}<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>[System.Reflection.Assembly]::LoadWithPartialName(&#8216;Microsoft.SqlServer.SMO&#8217;) $serverInstance = New-Object (&#8216;Microsoft.SqlServer.Management.Smo.Server&#8217;) $ServerName $IncludeTypes = @(&#8220;tables&#8221;, &#8220;StoredProcedures&#8221;, &#8220;Views&#8221;, &#8220;UserDefinedFunctions&#8221;) $ExcludeSchemas = @(&#8220;sys&#8221;, &#8220;Information_Schema&#8221;) $so = new-object (&#8216;Microsoft.SqlServer.Management.Smo.ScriptingOptions&#8217;) $so.IncludeIfNotExists = 0 $so.SchemaQualify = 1 $so.AllowSystemObjects = 0 $so.ScriptDrops = 0 #Script Drop Objects $dbs = $serverInstance.Databases foreach ($db in $dbs) { $dbname = &#8220;$db&#8221;.replace(&#8220;[&#8220;, &#8220;&#8221;).replace(&#8220;]&#8221;, &#8220;&#8221;) $dbpath = &#8220;$path&#8221; + &#8220;$dbname&#8221; + &#8220;\\&#8221;&#8230;&hellip;<\/p>\n","protected":false},"author":143519,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[8266],"class_list":["post-6160","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6160","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/143519"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=6160"}],"version-history":[{"count":14,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6160\/revisions"}],"predecessor-version":[{"id":75845,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/6160\/revisions\/75845"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=6160"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=6160"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=6160"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=6160"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}