Scripting Database Table Create SQL using Powershell
PowerShell is quite high on my list of languages that I dip in and out of without really knowing nearly well enough. One of things that I have found myself doing quite regularly is trying to script SQL for creating tables. This is quite straight forward using Sql Server PowerShell
Next we create the Sql Server path to use. Using a a few variables makes it more explicit:
And finally, we can call the Get-ChildItem command passing our Sql Server PowerShell path. This will load all the tables into the tableset variable.
We can now loop round the collection of tables and perform actions on them:
This script prints out a script to the specified file: C:\scripts\ps\department.sql using the sql server management objects.
First thing to note is that the script empties the ouput directory of all sql files, so be careful where you run it. Other than that, its building on the previous example by printing out each table to its own sql file. This example uses regexs to do some tidying up of the Sql generated. Although it may seem a little crazy, its actually putting the Sql into the format required for my old works database.
A simple Example
First up we need to register the appropriate snapins and set the execution policy:Next we create the Sql Server path to use. Using a a few variables makes it more explicit:
And finally, we can call the Get-ChildItem command passing our Sql Server PowerShell path. This will load all the tables into the tableset variable.
We can now loop round the collection of tables and perform actions on them:
Printing a tables Create Sql
Now that we have the basic idea, a more full example:This script prints out a script to the specified file: C:\scripts\ps\department.sql using the sql server management objects.
Printing all tables Create Sql
Well, the previous example is almost useful, but in reality we would want to print out all tables in a database and save them probably in their own file...First thing to note is that the script empties the ouput directory of all sql files, so be careful where you run it. Other than that, its building on the previous example by printing out each table to its own sql file. This example uses regexs to do some tidying up of the Sql generated. Although it may seem a little crazy, its actually putting the Sql into the format required for my old works database.
