Scripting out several databases on a server

A while ago, I wrote an article Automated Script-generation with Powershell and SMO about using SMO to script out a SQL Server database. It has remained surprisingly but agreeably popular. SMO is still there, but now part of the sqlserver module that is included with SSMS and downloadable. Someone recently asked me whether it was possible to alter one of the scripts to allow several databases to be scripted out at one go. The answer of course, ‘Do bears deposit nitrogenous waste-products in the wood?’. I thought I ought to upgrade a script to current standards, and add support for those who have to use a SQL Server Login. I also initialize the scripting preferences in a different way that allows configuration to be read from disk or specified differently for each database. I haven’t implemented these configuration  options but merely made it possible by having the configuration data in a hash table, so it can be read in. 

This script allows you to specify databases in a list that can include wildcards. ‘*’ would mean all of them. DOS wildcards allow you to specify ranges and collections of characters in square brackets and use the ‘?’ character to mean ‘any one character’. So, by saying $Databases = @('*')     then you script them all but  $Databases = @('BigReportingApp','MyDbase','Pubs') would script you just those that you list

Here is the code

SSMS uses the same library for scripting databases. By using code like this, you are just automating what would be a tedious and error-prone process. If you look at the original article, you’ll see that there are ways of doing an object-level script and so on, so you can match pretty-well everything you can do within SSMS.