Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1887
  • Last Modified:

Powershell / SMO -- scripting out SQL database ddl -- non dbo schema problem

Trying to traverse all objects in the SQL Server database, scripting the object creation to .sql file, to be stored within a source code repository.  

We are using Powershell calling SMO to create the DDL files.   Basically, it works well for all dbo schema objects.  Any other user defined schemas are not being processed correctly.  

The Powershell script is attached.  At line 21 you can see where we tried to handle the schema with this:    $Object=$Schema + "." + $Object

 
EXAMPLES:

This works:      
./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "dbo" "TableA"

This works, but the object is qualified with ‘dbo’, instead of ‘OtherSchema’.  ‘TableA’ exists within both schemas:
./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "OtherSchema" "TableA"  

This fails, presumably because ‘TableB’ is only in ‘OtherSchema’:
./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "OtherSchema" "TableB"
 
We have tried unsuccessfully to pass in the schema name.  When attempting to do so, this is the failure:
 
PS C:\mssql\tools> ./schemaextract.ps1 "c:\temp" "ServerName" "DatabaseName" "Table" "dbo" "TableA"
Directory: C:\temp\DatabaseName
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        12/21/2012   2:46 PM            Tables
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At C:\mssql\tools\SchemaExtract.ps1:97 char:20
+        $scrp.Script <<<< ($db.Tables["$Object"])
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest



We did this several years back with SMO -- no problems at all.  But, we had only the 'dbo' schema at that time.  Is anyone able to advise?
SchemaExtract.txt
0
dbaSQL
Asked:
dbaSQL
  • 7
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what if you do replace this line:
$scrp.Script($db.Tables["$Object"])
by this line
$scrp.Script($db.Tables["$Schema.$Object"])
0
 
dbaSQLAuthor Commented:
Thank you, angeliii.  Will try it asap, and let you know.
0
 
dbaSQLAuthor Commented:
this is the error we get, angeliii:

PS C:\mssql\tools> ./schemaextract.ps1 "c:\temp" "SERVERNAME" "CTS" "Table" "
OtherSchema" "TableB"
Multiple ambiguous overloads found for "Script" and the argument count: "1".
At C:\mssql\tools\SchemaExtract.ps1:96 char:14
+     $scrp.Script <<<< ($db.Tables["$Schema.$Object"])
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodCountCouldNotFindBest


in this case, Table2 is there only in OtherSchema, not dbo.
If the table exists under 2 schemas, dbo and otherwise, the script works without error, but is only finding the dbo object.
If the object only exist in a schema other than dbo, the script errors out.
0
NEW Internet Security Report Now Available!

WatchGuard’s Threat Lab is a group of dedicated threat researchers committed to helping you stay ahead of the bad guys by providing in-depth analysis of the top security threats to your network.  Check out this quarters report on the threats that shook the industry in Q4 2017.

 
dbaSQLAuthor Commented:
any ideas?
0
 
dbaSQLAuthor Commented:
angeliii, or any other Expert suggestions on the powershell script?
0
 
dbaSQLAuthor Commented:
Really becoming fairly pressing.  Does anyone have any suggestions?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is what worked for me:
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null 
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') "$SQL_SERVER"
$db=$s.Databases["$Database"]

$tbls = $db.Tables | where { $_.Name -eq "Object" }
$tbl = $tbls | where { $_.Schema -eq "$Schema" }

$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($s)

$scrp.Options.FileName = "c:\temp\test.sql"
$scrp.Options.ToFileOnly = $True

$scrp.Script($tbl)

Open in new window


you can use the "where" code easily to limit the objects array to be scripted to what you need ... this may help to simplify the work.

apart from that, the only thing that made the code "fail" is when the output folder was "write protected" ...

hope this helps
0
 
dbaSQLAuthor Commented:
thank you very much, angeliii.  i will get back to you with status.
0
 
dbaSQLAuthor Commented:
very helpful, angeliii.  the dbo and other schema objects are handled properly.  thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Making Bulk Changes to Active Directory

Watch this video to see how easy it is to make mass changes to Active Directory from an external text file without using complicated scripts.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now