In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Powershell / SMO -- scripting out SQL database ddl -- non dbo schema problem
Posted on 2012-12-28
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\SchemaExtra
+ $scrp.Script <<<< ($db.Tables["$Object"])
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes
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
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\SchemaExtra
+ $scrp.Script <<<< ($db.Tables["$Object"])
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
2
9 Comments
Active today
what if you do replace this line:
$scrp.Script($db.Tables["$
by this line
$scrp.Script($db.Tables["$
$scrp.Script($db.Tables["$
by this line
$scrp.Script($db.Tables["$
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\SchemaExtra
+ $scrp.Script <<<< ($db.Tables["$Schema.$Obje
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes
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.
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\SchemaExtra
+ $scrp.Script <<<< ($db.Tables["$Schema.$Obje
+ CategoryInfo : NotSpecified: (:) [], MethodException
+ FullyQualifiedErrorId : MethodCountCouldNotFindBes
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.
Active today
this is what worked for me:
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
[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)
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
Featured Post
Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed.
Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 2 hours left to enroll
630 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.