Automated Script-generation with Powershell and SMO

In the first of a series of articles on automating the process of building, modifying and copying SQL Server databases, Phil Factor demonstrates how one can generate TSQL scripts for databases, selected database objects, or table contents from PowerShell and SMO.

DevOps, Continuous Delivery & Database Lifecycle Management
Automated Deployment

If you’re a DBA or a database developer, you’ve probably found that point’n’click script-generation via SSMS becomes an increasingly tedious chore, and one that always increases the chance of error. Now, if you’re happy with the tedium of using SSMS to create your database scripts regularly, then use it, because it works, and it is probably the best way of getting one-off ad-hoc scripts for special purposes.

In my case, however, I want automated scripting, because it gives me the opportunity of getting the result exactly as I want it. I’m then more confident of having an up-to-date database build-script to hand, and I’m more likely to use this technique to perform tedious routine jobs such as integration, source control and creating test databases.

A live database is created from a number of SQL scripts, both DDL and DML. Script-generation is the act of reverse-engineering that live database, or a database component such as a schema or set of routines, into one or more scripts that are capable of creating an exact replica.

You’ll probably want a complete build script, as it is difficult to create a build script for a working database from individual object scripts alone, and impossible unless you are aware of the server settings and have scripted the database settings too. It is therefore wise to regularly save a complete script for building the entire database, and maybe a separate script for the database settings as well. You’ll probably also want to use script-generation to get the source of individual routines, tables and views for source control, for editing and for your own records.

There are a number of choices to be made when creating your database scripts, depending on the problem you’re trying to solve. It’s like buying a frou-frou cup of coffee. You are bombarded with decisions. Are you updating the schema of an existing database, or are you creating it from scratch? Do you only wish to update the routines? Do you want to temporarily disable the constraints in order to import data in the ‘wrong’ order? Do you want to leave out the DRI until you’ve imported all the data?

You need this flexibility required in a script-generation solution because you have a fair amount of complexity to deal with. A database application, at any point in time, consists of one or more databases on one or more servers, and may have more than one version or fork being developed. They may be using different data. As well as the code for all the routines, views and tables, there will be database settings and server settings. Finally, there will be data, even if just the basic enumerations and static data without which nothing works. As well as the code in the shared ‘public’ database, you may also have stealth things you are trying out, and sandbox stuff that needs to be preserved. You will also need to script your endpoint configuration and tasks that will go on the SQL Server agent. If you’re doing serious website work, you’ll have queues managed by service broker too.

If the production-DBAs have scripts for all of this, for all current versions, along with migration and rollback scripts, then they are smiling.

As well as squirreling away the code in order to preserve the work of the team, keep track of progress, maintain test-cells, do builds and rollbacks from builds, and to relate bugs to code alterations, you need codes to understand the database. You can understand a minnow of a database such as Adventureworks through Point n’ click in SSMS, but for an industrial-strength behemoth, then it is far quicker to eyeball down the build scripts. I know of very few ways to generate database scripts, and a lot of these do it wrong, because the scripts are formatted as a machine-to-machine communication. Table build scripts, for example, can be written for legibility or merely in order to get a correct build. In order to quickly learn a database, you need the legible version.

No, I had to write my own version and it has paid dividends. Functions, for example, are easier to understand with a structured comment block listing the parameters and comments in extended properties, and even where they are referenced and what they reference. Tables are far better with the simpler constraints written in-line, and comments on both table and column pulled from the extended properties.

Automated scripting of database DDL

Let’s start with the obvious technique. We’ll use PowerShell and Server Management Objects (SMO). No, don’t panic. SMO is a monster, but that’s because it is written for many purposes, the worst of which is providing an interface between SSMS and the servers. In most cases, there is already a high-level interface for the likes of you and me. I’ll admit that SMO isn’t easy. It is always a bad sign when you’ve got a problem with a script, and you reach for Google, only to find nothing more than a string of Chinese characters on half a page, and a similar StackOverflow question left unanswered since 2008, save for advice to use SSIS instead.

This sort of thing tends to happen when you’re using SMO, which is such a shamebecause it is so powerful. In fact, almost any job that you can do via SSMS you can do through SMO. With PowerShell and SMO, you can work magic, but with that strange, lonesome feeling that not many people have ever walked down the same path. It’s the awful documentation, combined with the intricate, arcane, multi-layered interface that makes SMO hard work for the uninitiated, but it shouldn’t be like this.

Microsoft has always shown its ambivalence in letting users loose on SMO, by neglecting to provide anything remotely resembling adequate documentation. All we get is minimal, and probably machine-generated, documentation of the SMO classes, methods and so on. Even the examples have errors. Microsoft has to keep SMO up to date because it is used for SSMS, but there seems to be a great deal of passive resistance to supporting users who need to use it for scripting. For this reason, I’ll be keeping these examples as simple as I can.

Getting stuck in quickly

There is usually a quick way to do things. Here is the PowerShell to script out the MyDatabase database from MyServer into the local directory E:\MyScriptsDirectory’ (it will do others, of course, by changing the assignments to the three variables at the head of the script). Note that in these PowerShell scripts I’ve opted to avoid the terse style, mainly because the terse style is less intelligible for those of us who just want to use PowerShell without getting too absorbed.

I reckon this is the simplest PowerShell script to get an executable build script, and it isn’t too painful. Die-hard SMO-heads will notice that I have to write to a file via SMO in order to get the batch-terminator GO into the script. To do this, I’ve created a ‘ScriptOptions’ object, which isn’t entirely necessary yet, but will be once we increase the complexity of the task. If you run the script, it will successfully build a database, but there will be a lot missing, because we’ve been using the default options for generating the script. You’ll have no DRI. The tables, in other words, won’t have their constraints and indexes, or any dependent objects at all. It will miss out all the extended properties as well.

There is some work to be done. Not all the defaults for the script options are sensible. A quick bit of PowerShell to query the ScriptOptions object will tell us what the defaults are. I’ll print them all out because this is a useful reference when you’re struggling with a script task. This is the equivalent of the options for the frou-frou cup of coffee, and you’ve just been served ‘black without sugar’. Our scripting options are below, along with the defaults.

Options: ‘Do you want cinnamon with that coffee, sir?’

These are the scripting options with their default settings. I haven’t explained them, because they are either obvious or  undocumented

Encoding System.Text.UnicodeEncoding
DriWithNoCheck False
IncludeFullTextCatalogRootPath False
BatchSize 1
ScriptDrops False
TargetServerVersion Version110
TargetDatabaseEngineType Standalone
AnsiFile False
AppendToFile False
ToFileOnly False
SchemaQualify True
IncludeHeaders False
IncludeIfNotExists False
WithDependencies False
DriPrimaryKey False
DriForeignKeys False
DriUniqueKeys False
DriClustered False
DriNonClustered False
DriChecks False
DriDefaults False
Triggers False
Statistics False
ClusteredIndexes False
NonClusteredIndexes False
NoAssemblies False
PrimaryObject True
Default True
XmlIndexes False
FullTextCatalogs False
FullTextIndexes False
FullTextStopLists False
Indexes False
DriIndexes False
DriAllKeys False
DriAllConstraints False
DriAll False
Bindings False
NoFileGroup False
NoFileStream False
NoFileStreamColumn False
NoCollation False
ContinueScriptingOnError False
IncludeDatabaseRoleMemberships False
Permissions False
AllowSystemObjects True
NoIdentities False
ConvertUserDefinedDataTypesToBaseType False
TimestampToBinary False
AnsiPadding False
ExtendedProperties False
DdlHeaderOnly False
DdlBodyOnly False
NoViewColumns False
SchemaQualifyForeignKeysReferences False
AgentAlertJob False
AgentJobId True
AgentNotify False
LoginSid False
NoCommandTerminator False
NoIndexPartitioningSchemes False
NoTablePartitioningSchemes False
IncludeDatabaseContext False
NoXmlNamespaces False
DriIncludeSystemNames False
OptimizerData False
NoExecuteAs False
EnforceScriptingOptions False
NoMailProfileAccounts False
NoMailProfilePrincipals False
NoVardecimal True
ChangeTracking False
ScriptDataCompression True
ScriptSchema True
ScriptData False
ScriptBatchTerminator False
ScriptOwner False

This is a scarily complicated set of options. One can’t help wondering why some of these options would be required. However, we can soon put our script right without too much bother. Your own requirements may be different, but I was aiming for an exact copy of AdventureWorks in my testing!

Making an exact copy of AdventureWorks

If you test a database built with this script against the original, it gives a pretty good account of itself. All that’s missing are some extended properties on indexes, but there is no switch that one can flip to tickle those out of SMO, so I suspect that someone has made a mistake.

We did this intermediate version because it is simple and demonstrates a clean technique which you can take and expand on. It’s great for archiving a complete build script that you can use in source control alongside the individual object scripts. However, I’m going to include a more complete version that will give you a database build script and an object-deletion script as well as the object-build script, all concatenated into one script. You’ll begin to understand why I like to create a ‘ScriptingOptions’ object to store the options, since it is more efficient for this sort of job.

Getting database settings and object drops into a database-script

This isn’t entirely what we want for other purposes, of course. What about when you want to create a database without indexes, constraints or triggers, import the data in BCP fast-mode and then add the indexes, constraints and triggers? Yes, you can squirrel away far more test-runs this way, and load them rapidly, but in order to do it, you need a build script without them first, and a second build script with them only. With the first ones, you can have a ‘knock-down’ kill script that deletes everything from the database before you start, but you definitely don’t want it for the second script. You’ll soon be eyeing up all those scripting options, though, believe me. I’ll be covering a lot about this in future articles.

Automated scripting of objects

A second task is to save each object to a separate file. You’ll need to do this to get your local (unshared) database into source control if you’re not using SQL Source Control. The simplest way of doing this, if you are lucky enough to have SQL Compare, is

You can do it in PowerShell, and you will, again, have more knobs you can twiddle to get the individual scripts exactly how you like them.

Here is a simplified script that shows you one of the several methods of doing this. Like SQL Compare, it saves each object type into its own subdirectory.

This time, we get SMO’s EnumObjects method for the current database object, then get the scripter object, via the Script method, to script out each object individually and save it to a separate file. Each filename is generated from the name of the object, and the directory name is generated from its object type. You can, of course, be very selective about what you script out and you’ll see that scripting out a single object type, such as a table, is very simple.

In this following script, we’ll save just the tables, scripting different schemas into different directories and adding the DRI, indexes, extended properties and triggers to each table. It would be equally simple to script whatever types of objects you want just by ‘or’ing the DatabaseObjecTypes to taste. ( in the previous script, I specified ‘all but…’)

Automated scripting of static data

The last task we’ll set ourselves is to script out static data. This will really just include all those small tables without which your database simply won’t work. Now, there is no way that anyone but you will know which tables this includes, but don’t ‘go ape’ with this script, since it is scripting INSERT statements and that sort of thing doesn’t scale effectively for big tables. No, sir: you’ll want native-mode, fast-mode BCP for that.

This time, I’ve used a slightly different approach, in that I’ve actually constructed the URNs from the (maybe qualified)  names of the table; this means the schema too if you specify it, and also the database if you want that too. You just specify what tables you want to script and we just go and do it. With SMO there are always several ways of getting to your destination.

Now we’re making progress, and I’m hoping that, in these simplified scripts, I’ve given you some useful clues as to how to generate particular types of build scripts. I’ll let you turn them into robust command-line tools with parameters and all the error handling, logging and other bits and pieces you’ll want. In the next article, I’ll explain how you can extend this functionality by actually executing SQL batches via SMO. This will allow you to automate migration scripts and do integration testing and all manner of other things all from the one script.

DevOps, Continuous Delivery & Database Lifecycle Management
Go to the Simple Talk library to find more articles, or visit for more information on the benefits of extending DevOps practices to SQL Server databases.

For more articles like this, sign up to the fortnightly Simple-Talk newsletter.


Tags: , , , , , ,


  • Rate
    [Total: 56    Average: 4.6/5]
  • RealSQLGuy

    Combine with source control
    Good stuff. I’ve been doing something similar for the past year, combined with our source control software, to automate version control of our database objects.

  • Phil Factor

    Re:Combine with source control
    Ah yes, we have trodden the same path, I can see. Quite a struggle eh? It is well worth checking out the site, and a great example of Source-control integration. Lots of useful info here.
    See also Grant’s approach

  • Anonymous

    nice article. dba must read

  • Seyit

    Thanks, saved me heaps of time! Tagging in delicious for future reference!

  • johns

    Objects created in master
    Great Scripts, Thanks!
    Testing the script following “Getting database settings and object drops into a database-script”
    The database is created fine, but the objects are created in master, oops.
    I added
    #Change the database context
    “USE $Database” | Out-File -Append -FilePath “$($DirectoryToSaveTo)$($DatabaseName)_Build.sql”
    “GO” | Out-File -Append -FilePath “$($DirectoryToSaveTo)$($DatabaseName)_Build.sql”
    after $scripter.Script($s.Databases[$Database]) # do it
    That looks like it fixed that issue.
    Thanks again

  • puzsol

    SMO is very useful
    I recently stumbled upon SMO and used it to build a C# util to copy a database (data and all) from the client server where we only have read access. It works quite well, though I had to modify the order of the scripts as it tries to assign users to roles before the roles are created.

    The really neat thing is that the with the Transfer object is that you can use EnumObjects to obtain the order to copy table data (I used SqlBulkCopy) with DataReferentialIntegrity in tact (already on the table structure).

    Of course I could have just used the RedGate SQL tools, but that wouldn’t have been anywhere near as much fun (or undocumented frustration).

    One other “error” with SMO is even if you specify to exclude filegroups from the transfer, it still includes the with filegroup option on the fulltext indexes – so again manual editing of the script created.

  • Phil Factor

    re: Objects created in Master
    Oops. One moment it’s there, the next moment it isn’t.  I’ve added in your fix to the script whilst I figure out where the USE <database> disappeared to. Sometimes using SMO directly is like trying to catch a rat with your bare hands.
    Thanks for that!
    Ah. I took out …
    which gives you the USE <Database>  – because it would only return it as a string rather than write it to file. I then did…
      #now we alter the name to create the copy. This isn’t safe with certain database names
      if ($CopyOfDatabase -eq $null) {$CopyOfDatabase=”New$Database”}
           (get-content “$($DirectoryToSaveTo)$($DatabaseName)_Build.sql”) | `
         Foreach-Object {$_ -replace $Database, “$CopyOfDatabase”} >”$($DirectoryToSaveTo)$($DatabaseName)_Build.sql”
    “USE [$CopyOfDatabase]”  >> “$($DirectoryToSaveTo)$($DatabaseName)_Build.sql”
    # add the database object build script
    …when I tidied it up for publication, I took out the full copying bit but I nicked out the insertion of the ‘USE’ by mistake!

  • Phil Factor

    Getting the table order.
    It sounds like a useful bit of code. The table-dependency order thing is described in a subsequent article. Although it is certainly possible to do using SMO, this is most effectively done, I reckon, with a topological sort, in TSQL. With such a table-function, then deleting the data in every table in a database becomes frighteningly easy, and you can BCP in entire data sets without any kill and fill nonsense!
    With SMO, one gets such a wonderful feeling of success when you get it to bend to your will that it is easy to forget the grinding frustration of working against a perfectly horrible documentation

  • software_now

    Few questions
    I scripted one of our dbs with 4,500 objects to a file using your script. Then I created a new database and ran the data scripts your script generated (getting confusing? 🙂 )

    If I check number of objects by using “select COUNT(*) from sys.all_objects” from PROD versus new test db – I get 300+ object mismatch

    So, here is the changes I had to make, to get it closer:

    I added:
    $CreationScriptOptions.Bindings= $true
    $CreationScriptOptions.Permissions= $true
    $CreationScriptOptions.IncludeDatabaseRoleMemberships= $true

    I am now down to 245 differences.

    Few problems I am aware of:
    1. It looks like sequence in which those scripts get execute is part of the problem
    2. Constraint names are not preserved
    3. Not all permissions get scripted, such as:

    Any idea how to get around those?

    Still a great script though!!

    • Phil Factor

      Re: Few questions
      Some index extended properties got missed in the testing I did. I checked with SQL compare. You can download it and use it to see exactly what is getting missed. I expect that there is a combination of options that will make it produce a correct script. Without knowing exactly what is getting missed, we are rather guessing
      Thanks for the test. That is quite a database.

  • software_now

    Permissions in the script?
    Do you know of a way to generate permissions? Here is one of the pieces I am missing.

    GRANT CREATE FUNCTION TO [ProductSupportLevel3]
    GRANT CREATE PROCEDURE TO [ProductSupportLevel3]
    GRANT DELETE TO [ProductSupportLevel3]
    GRANT EXECUTE TO [ProductSupportLevel3]
    GRANT INSERT TO [ProductSupportLevel3]
    GRANT SELECT TO [ProductSupportLevel3]
    GRANT UPDATE TO [ProductSupportLevel3]
    GRANT VIEW DEFINITION TO [ProductSupportLevel3]

  • Phil Factor

    Re: Permissions on tyhe script?
    Simple. (I generally need to leave all this out) Just include this..

    $CreationScriptOptions.Permissions = $true

    To get users and logins, do this…


  • barrynoble

    Great Script. I have one small issues.. Can you help me?
    The only issue I have is that for some of the databases I have a partition function that is range right. The script generates the partition function with no right or left making it default to left. I have tried multiple things to correct that but nothing has worked. Do you have any suggestions?

    • Phil Factor

      Re: Great Script. I have one small issues.. Can you help me?
      I’ve found exactly the same thing, which I’d say is a bug.  Create a database with two range functions, one RANGE LEFT, and the other RANGE RIGHT, and even SSMS will script them incorrectly leaving out the LEFT or RIGHT keyword. If SSMS does it wrongly, I don’t think that any tweaking with SMO will get it right.  To get it right, I’d do it directly.  Unfortunately, sp_HelpText won’t help you, but you can do it using the values in sys.partition_range_values and sys.partition_functions. The latter has a column called boundary_value_on_right which records whether range partitioning is 1 (Boundary value is included in the RIGHT range of the boundary) or 0 (Boundary value is included in the LEFT range of the boundary). For scripting, I quite often use SQL directly just because of certain issues like this. (I also like to have extended properties to appear as comments as well as parameters within scripts )

  • Phil Factor

    Re: Great Script. I have one small issues.. Can you help me?
    + CASE Boundary_Value_On_Right WHEN 1 THEN ‘ RIGHT ‘ ELSE ‘ LEFT ‘ END ELSE Type_desc END
    + ‘FOR VALUES (‘ COLLATE sql_Latin1_General_cp1_ci_ai
    + CASE WHEN sys.Types.Name IN
    ‘datetime’,’datetime2′,’char’, ‘nchar’,
    ‘datetimeoffset’, ‘smalldatetime’)
    THEN Stuff(
    FROM sys.Partition_Range_Values rv
    WHERE pf.Function_Id=rv.Function_Id
    AND pp.Parameter_Id=rv.Parameter_Id
    FOR XML PATH(”), TYPE).VALUE(‘.’, ‘varchar(max)’)
    ,1,2,”) +’)’
    FROM sys.Partition_Range_Values rv
    WHERE pf.Function_Id=rv.Function_Id
    AND pp.Parameter_Id=rv.Parameter_Id
    FOR XML PATH(”), TYPE).VALUE(‘.’, ‘varchar(max)’)
    ,1,2,”) +’)’
    FROM sys.Partition_Functions pf
    INNER JOIN sys.Partition_Parameters pp
    ON pp.Function_Id=pf.Function_Id
    INNER JOIN sys.Types
    ON sys.Types.System_Type_Id=pp.System_Type_Id

  • ricardodiazjimenez

    Great script. Additional challenge
    Phil, thank you these scripts are awesome. Helped me a lot.

    I have this additional challenge. Hope you can give me some tips.

    I need the scripts to create some tables in the database (not all). For this I have a table called “TableExport” in the database which containts the names of the tables that I want to “script”. How can I filter the script creation with the table names in my “TableExport”?

    Finally, I would like to create just one file for all the tables in the script, not a separate one for each table. How do you accomplish these?

    Thanks again!

  • Atif Shehzad

    Getting error message for $transfer.ScriptTransfer
    I am getting following error message on one of my servers.
    $transfer.ScriptTransfer <<<< () + CategoryInfo : NotSpecified: (:) [], ParentContainsErrorRecordE xception + FullyQualifiedErrorId : DotNetMethodException

    Output file is created with few objects coded in it but error message is generated later. Could you please advise for it.


  • ax4413

    Schema authorization missing from generated build script
    Great scripts Phil. Thanks a lot.

    I have a question though. Is there any way to script out schema authorizations? I don’t seem to be able to achieve this myself. Microsoft’s help files don’t seem to be that clear, for example the property DriAll has the following description Gets or sets the Boolean property value that specifies whether all DRI objects are included in the generated script. Clear???

    If I create a schema in test database I would hope to get the same script out using the smo scripter.

    USE [TestDatabase]

    What I actually get out is more like this. As you can see it is missing authorization and as such end up defaulting to dbo.


    I know I am a little late to the party but any help would be gratefully received.

  • ax4413

    Schema authorization missing from generated build script
    Great scripts Phil. Thanks a lot.

    I have a question though. Is there any way to script out schema authorizations? I don’t seem to be able to achieve this myself. Microsoft’s help files don’t seem to be that clear, for example the property DriAll has the following description Gets or sets the Boolean property value that specifies whether all DRI objects are included in the generated script. Clear???

    If I create a schema in test database I would hope to get the same script out using the smo scripter.

    USE [TestDatabase]

    What I actually get out is more like this. As you can see it is missing authorization and as such end up defaulting to dbo.


    I know I am a little late to the party but any help would be gratefully received.

  • Phil Factor

    Re: schema authorisation missing from generated build script
    permissions are, by default set to false in the scripter options object. Have you tried setting it to true? Is it just the schemes that don’t have their authorisations script, or is it missing for all database objects?

  • ax4413

    Re: schema authorisation missing from generated build script
    Phil, thanks for getting back to me.
    I have only specified authorization on the schema objects so I am unsure if it is missing on other objects. However I don’t think that it is.
    I have set the scripting option permissions to true and unfortunately authorization is still missed out of the script. If i iterate through all of the database objects and script them individually then I can get a complete script generated but this is a less clean way of generating teh file.


  • TFrascaroli

    Tiny modification
    Hi there Phil. First of all, such a nice script, but what mostly impressed me was the fact that you ACTUALLY KNOW what all that stuff does.

    Second, I’d like to use that script and include it in a batch where I’ll have the visual studio build process and other builds to make a 1-click-release. Am I doing it right? Is this the right tool to use? I ask this because from what I can read here and elsewhere, people mostly use SMO for fun and personal callenges, not really something serious. But on the other hand we can’t affor to pay for the wonderful RedGate or similar.

    And finaly, I need to be able to script the insertion of just 1 / a few rows in some tables (which are the "default values" of the tables pointing to theese ones). So, can I / how to go about scripting full tables for some tables and partial tables for others?

    Thank you very much!

    PS: Sorry for my English, I know it’s a little rough…

  • SqlServerlabs

    Need to Add Databases & Server list
    can we pass multiple servers list and Database List ( DB list is common for all servers)

  • SqlServerlabs

    Pass Parameter
    Need to give multiple servers list as input at $Servername, and Multiple Databases at $Database
    , good if we can pass these list as .txt file.

  • SqlServerlabs

    Pass Parameter
    Need to give multiple servers list as input at $Servername, and Multiple Databases at $Database
    , good if we can pass these list as .txt file.

  • SqlServerlabs

    Pass Parameter
    Need to give multiple servers list as input at $Servername, and Multiple Databases at $Database
    , good if we can pass these list as .txt file.

  • paul.storm

    Further on Scripting Options
    For detailed information on SMO scripting options see