current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

I am able to connect to a remote SQL Server with the instance name using PowerShell. I am using an XML file (something like a configuration file) which includes all my database details. PowerShell uses this for connection establishment.

Now I want to get all "schemas" associated with a particular database that is already defined in that XML file. How can I achieve that using Powershell scripting?

Any help would be really appreciated.

share|improve this question
    
Do you want to pull this data from the flat XML file, or the database? –  Thomas Stringer Jun 24 '13 at 10:18
    
@ThomasStringer: No..I have my databasename in an XML file. But I need to pull the schemas corresponding to that database from SQL Server. Note: I am trying this remotely. –  Vysakh Jun 24 '13 at 10:41
1  
Can you provide the format of your XML file you are using? –  Shawn Melton Jun 24 '13 at 12:52
    
What's the scope of this question? Do you need help with XML in any way, or is that just background? When you say "I want to get all "schemas"", do you just need a list of the schema names from a database? Or, do you want to access objects within each schema in the database? If so, what do you want to do with the database schema objects? –  peterk411 Nov 26 '13 at 22:28

2 Answers 2

up vote 0 down vote

You can use this script to extract your databases as .dacpac files, which can be used in Visual Studio to translate to a full database schema.

#extract all your database schemas as dacpacs
$server = 'PICARD'

$dbs = Invoke-Sqlcmd -ServerInstance $server -Database tempdb -Query 'SELECT name FROM sys.databases WHERE database_id >4'

foreach($db in $dbs.name){
$cmd = "& 'C:\Program Files (x86)\Microsoft SQL Server\120\DAC\bin\sqlpackage.exe' /action:Extract /targetfile:'C:\dacpacs\$db.dacpac' /SourceServerName:$server /SourceDatabaseName:$db"

Invoke-Expression $cmd
}

We use this in my environment to extract our schemas on a daily basis. Head over to my blog for the full post on this technique.

share|improve this answer
up vote 0 down vote

Assuming you have a valid instance of the SMO Server object defined as $ServerObject. The following code will allow you pull the names of all schemas in the database UserDatabase01:

$Database = $ServerObject.Databases | ? { $_.Name -eq "UserDatabase01" }
$Database.Schemas | Select Name

For more information on schema properties available through SMO, see the documentation for the Schema class. Unfortunately, the version of SMO for SQL 2005 is missing a lot of properties for this class. Newer versions are backwards compatible, you may want to look into installing a newer version:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.