Script Database Objects with PowerShell


exportI recently did a presentation about documenting SQL Server with PowerShell.

The presentation was initially intended to show people how to get data like configuration settings, databases, database files etc, from SQL Server and export that to an Excel file.

At the end several people had questions how to export database objects using PowerShell. I must admit I didn’t have a direct answer and promised to come back with solution. Luckily Bob Klimes had a solution by using the Scripter functionality in the SMO and even send me one of his scripts.

The Scripter class

I had never used this part of the SMO before and wanted to know what it did, so as I start with reading the documentation. Cool, we have a script function and I can retrieve a type which enables me to automate some things.

The code

Because I like to use functions to do my work I created a function to export the database objects. The end result looks like this:

It needs two parameters to work:

  1. -inst: your instance
  2. -path: The path to export to

There is third parameter I advise you use and that is “-dblist”. This enables you to select a certain database or multiple databases separated by a comma. If this parameter is not set it will export all the objects for all the user databases so be aware of that!

In the end I was pleased with the result and the function could well be implemented in my existing module, PSSQLLib, and so I did.

The result

There is now an extra function in the library called Export-DatabaseObject.

When executed it looks like this:

/home/sstad/domains/ html/wp content/uploads/2016/04/160414 export databaseobject working

It will create a folder structure with the instance name, database, time stamp and at last a directory containing all the files for a specific object type like a table or view.

This made my life easier and again it’s a thing that can be done much faster using a script than using the GUI in SSMS 😉

I hope this can help you out with your daily work. Any comment is appreciated.


Leave a Reply