Here’s a preview on what I’ve been doing for work. I create about 12 new functions to assist me in automating a task to copy table(s) from one database to another. Then, this process will also rebuild the data, indexes, and constraints from the scripts extracted during this process.
All these functions are using embedded T-SQL scripts to assist in the scripting of the SQL Table objects. Finally, I will be ending having a module to load all my functions when is needed.
Just recently, I updated two on the main functions I posted in the Microsoft Script Repository:
1. “Function Write-SQLCreateTable” – Updated – 05/25/2011: Added three new switch parameters so we can create individual scripts for:DropTableOnly, CreateTableOnly, and TurnOffidentity.
2. “Function Write-SQLInsertSelect” – Updated – 05/25/2011: Add ability to detect if there’s an Identilty Column so it can add the “Set Identity_Insert …” line. And, Add set-location go back to your “Home Drive” when the process completed.
These two updated functions require you have the SQLPS module or in won’t work. That’s why I include to do both Import and Remove of SQLPS module. Also, these functions will not script out Indexes, and Constraints.
In my new SQLDevTools Module, all functions will not import/remove the SQLPS module. It will included functions to script out Indexes, Check Constraints, Foreign Keys and even Triggers. Functions name are change slightly, and a test functions script is included to assist in building rebuilding a table in another database. More functions will be added to this module. I will make it available to the community through CodePlex when is ready.
Here’s a preview snapshot of my new functions:
Special Thanks to the following bloggers for their contributions:
1. Martin Bell (UK SQL MVP) – August 2009 posts: http://sqlblogcasts.com/blogs/martinbell/archive/tags/Scripting/default.aspx
2. Aaron Nelson (SQLVariant) – November 2010 post: http://sqlvariant.com/wordpress/index.php/2010/11/sql-source-control-powershell-and-tfs-ftw/