Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-12-28
9
Medium Priority
?
1,855 Views
Last Modified: 2013-01-15
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
Comment
Question by:dbaSQL
  • 7
  • 2
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726874
what if you do replace this line:
$scrp.Script($db.Tables["$Object"])
by this line
$scrp.Script($db.Tables["$Schema.$Object"])
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38728007
Thank you, angeliii.  Will try it asap, and let you know.
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38728113
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 Tabletop Appliances Blow Competitors Away!

WatchGuard’s new T15, T35 and T55 tabletop UTMs provide the highest-performing security inspection in their class, allowing users at small offices, home offices and distributed enterprises to experience blazing-fast Internet speeds without sacrificing enterprise-grade security.

 
LVL 17

Author Comment

by:dbaSQL
ID: 38732513
any ideas?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38737322
angeliii, or any other Expert suggestions on the powershell script?
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 38754521
Really becoming fairly pressing.  Does anyone have any suggestions?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38756273
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
 
LVL 17

Author Comment

by:dbaSQL
ID: 38756389
thank you very much, angeliii.  i will get back to you with status.
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 38779925
very helpful, angeliii.  the dbo and other schema objects are handled properly.  thank you very much.
0

Featured Post

Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
A walk-through example of how to obtain and apply new DID phone numbers to your cloud PBX enabled users that are configured in Office 365. Whether you have 1, 10 or 100+ users in your tenant, it's quite easy to get them phone-enabled and making/rece…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question